Forum Discussion
Extracted data (from list) with separator; representation in diagrams
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.
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.
3 Replies
- amitchandak
Super User
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. 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-jingzhangCommunity Support
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.
