Data Model with comma separated list

[Anonymous]

Hey all, I'm currently struggling to create a nice data model out of a single table that contains about 6 columns that contain comma separated lists. A small example:

Recipe ID	Ingredients	Country
1	eggs,milk,flour	Netherlands, United Kingdom
2	eggs,aubergine,parmezan	Italy, Spain, France
3	parmezan,basil,tomato	Italy, Croatia, Austria

So I want to create a nice data model out of something like this (actual table has 20+ columns, with 6+ containing comma separated lists).

So I felt my central table (is it technically a Fct table?) should contain the recipe ID. The problem is with the dimensions. Let's say I want to be able to filter for all recipes containing eggs, and all recipes with country:italy. This means I should have dimensions for County and Ingredients. Those dimensions need to be related to the fctRecipe, but... how? My solution so far is to create a relation between the comma separated column and the comma separated column in the "countries" and "ingredients" dimension, like so:

Where the "countries" column contains values like "italy,croatia,austria" and the country column contains "italy", "croatia" etc. Is this the best way of doing it, or am I overlooking an option?

My first instinct would be to just split the comma separated lists into rows, but that doesnt work very well when you have multiple comma separated lists in your table. You would end up with a cathesian product of all combinations of countries and ingredients in this case 😞 

Thanks for thinking along!
Jaap

[v-yiruan-msft]

Hi @Anonymous ,

Whether the problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help other members in the community find the solution easily if they face the similar problem with you. Thank you.
Best Regards
Rena
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[VijayP]

@Anonymous 

Split the Column to Rows with Comma Delimeter and Replace Values " (Double quotes) with "" (null) then your problem would be sorted out!

Please share your Kudoes

[amitchandak]

@Anonymous , Not very clear. but you can split the the column using https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

[Anonymous]

Hmm sorry, guess I didnt explain it well enough. I know how to split columns as rows and as columns, what I want to know is how to fit the column, that I have split into rows, into a data model.

Jaap

[v-yiruan-msft]

Hi @Anonymous ,

What kind of model to create and how to create it is more appropriate depends on what function you ultimately want to achieve. Please provide your desired result and the calculation logic if it is involved.

In addition, I created a sample pbix file(see attachment) for you. Please check whether that is what you want.

Best Regards

Rena