Multiple relationships between tables?

[anryb]

Hello all,

Brand new to Power Bi and having trouble figuring something out. 

I have a product table that contains Product IDs. 

I also have an order table with orders, some of which contain multiple product ids. Right now they are in one cell, but I can split them so that each "line item" is in its own column.

How do make this relationship work between the tables when I have 1 product id in product table that needs to be linked to multiple product ids in another? 

Thanks!

[v-kongfanf-msft]

Hi @anryb ,

Did @DataInsights  reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best Regards,
Adamk Kong

[anryb]

@DataInsights 

The line item fields are in json and look like the following. This example order has 2 product ids. 

Its quite long when copied here so I made a pastebin link:

"line_items": [ { "id": 14419813204035, - Pastebin.com

[DataInsights]

@anryb,

Thanks for the example. You could try Transform --> Parse --> JSON for that column, and then split into rows.

[DataInsights · Accepted solution]

@anryb,

In Power Query, go to the Order table and split the column with multiple Product IDs into rows (one row per Product ID). Then create the relationship between the tables using Product ID.

[anryb]

Hi @DataInsights ,

Thanks so much for the response. 

My "line item" column that contains the product ids also has other info. Is there way to extract text between delimiters by every instance of that delimiter into a column so that I can do what you suggested? When i extract value by text between delimiter it only grabs the first instance. 

[DataInsights]

@anryb,

You may need to do some additional string manipulation prior to splitting Product ID into rows. The objective would be to remove everything except the Product IDs, and then split into rows. Would you be able to paste an example?