Count of comma separated values from xls.

[gauravchhaunker]

Hi There,

I have a question on how to get the count of comma separated value in xls.

I have the following requirement:

	Ingredients
Dish1	A,B,C,D,E,S,U,X
Dish2	B,X,Y,Z,G
Dish3	X,D,E,I,O,K
Output 1
Dish1	8
Dish2	5
Dish3	6
Output 2
A	1
B	2
C	1
D	2
E	2
S	1
U	1
X	3
Y	1
Z	1
G	1
I	1
O	1
K	1

 any pointers are highly appreciated!

Regards,

Gaurav.

[Pady]

This video helps to create both the outputs using power query

https://www.youtube.com/watch?v=WiZH16-nq_g

[Pady]

Hope this video helps:

https://www.youtube.com/watch?v=EsCbOBRsQgY&t=22s

The second half of the video deals with this.

[gauravchhaunker]

Thanks a lot, Pady, this is very relevant and useful.

Regards,

Gaurav.

[rsbin]

@gauravchhaunker ,

Please see attached pbix file.

After importing your Excel file into PBI, Transform your table by Splitting by Delimiter (comma).
Using Advanced options, be sure to Split into rows.

Hope this gets you going.

Regards,

[gauravchhaunker]

Thank you so much rsbin! This works so far, let me try tomorrow in the real use case and update!

regards,

Gaurav.

[rsbin]

@gauravchhaunker ,

Just letting you know that I will be away for a long weekend.
So, if things do not work out tomorrow , feel free to continue this thread and we can pick up any problems or issues on Monday.

Kind Regards,

[gauravchhaunker]

Hi rsbin,

Thanks again, sharing in detail about my exact use case via one drive....

pbi 

Regards,

Gaurav.

[rsbin]

@gauravchhaunker ,

Ok, you attached your file.  What is your question or issue that you having?

Regards,

[gauravchhaunker]

Hi Rsbin, yes, for now able to see the results without your measure so 1. am i missing anything and 2. how to get the measure out side of the queries.....

That's all for now.

Regards,

Gaurav.