Extracted data (from list) with separator; representation in diagrams

[FaH]

Hi,

I have data coming in via API which has a List field included (let's say we have 5 options A - E), which can be transformed (values extracted) and I can choose the separator for the concatenation. Obviously, I end up with different combinations and what I'd like to do is get a diagram, which shows me the occurence of A, B, C, D and E, independently. Counting the appearance three cases of B,C as three cases for B and three cases for C instead of creating a new subgroup for this (as it is currently looking):

Sorry for the stupid explanation but I I guess my lack of the proper terminology is the main reason I was unable to find something in the PowerBI documentation.

[FaH]

Thanks for your reply.

Item	Countries	Status
Shoes	Austria	Done
Suit	Austria,Denmark	Done
Jacket	Chile	In progress
Glasses	Estonia	Todo
Sneakers	Denmark,Belize	In progress
Trousers	Belize	In progress
Sunglasses	Austria	Todo
Bag 1	Denmark,Austria,Estonia	Done
Bag 2	Denmark	Done

Let me try to explain with this example. The column Countries may have several Countries referring to a specific Item. The countries are separated by commas (but that can be changed). I want to visualize the distribution of countries (%) in a pie chart. In Excel, I would for example use the COUNTIF(B2:B10;"*Austria*") function, to get a result of 4 (marked in green). Same then for all other countries (Denmark 4, Belize 2, Chile 1, Estonia 2) and then I can easily create a pie chart from the resulting numbers:

Simply speaking, I need a way to adopt the COUNTIF function in PowerBI or find an equal way to . 

I can also look if I can create a dummy .pbix without sensitive information if my table example does not help.

Thanks!

[v-jingzhang · Accepted solution]

Hi @FaH 

One option is to use Power Query Editor to split Countries column by delimiter comma into rows. Then create a pie chart based on the transformed table. 

Another option is to prepare a Country table which has all distinct country values in advance. Then add a new column in it with below DAX. 

Number = COUNTROWS(FILTER('Table (2)',SEARCH('Country'[Country],'Table (2)'[Countries],1,0)>0))

I have attached a sample pbix with both demos at bottom. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[amitchandak]

@FaH , Not very clear. You can use split by delimiters in Power Query into rows

https://www.youtube.com/watch?v=Jv1GQM3QwMY

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.