Table Group, text combine with count of occurrence

[Ferdy]

Hello!

I followed these steps to create a table with a concatenated list of values:

https://stackoverflow.com/a/44058791

This helped me a lot, but I need to take it one step further.

My current table is now like this:

A	apple, apple
B	apple, pear, pear
C	pear, orange, orange, orange
D	apple, apple, orange, orange
E	pear

But I'd like to show it like this:

A	2 x apple
B	1 x apple, 2 x pear
C	1 x pear, 3 x orange
D	2 x apple, 2 x orange
E	1 x pear

Can anyone help me to achieve this?

[mahoneypat · Accepted solution]

You can use this formula in a custom column.  Column2 is the column with the text list.

= let
mylist = Text.Split([Column2],",")
in
Text.Combine(List.Transform(List.Distinct(mylist), each Number.ToText(List.Count(mylist) - List.Count(List.RemoveItems(mylist, {_}))) &"X " & _), ", ")

Regards,

Pat

[raveravnn]

Thank you for the solution. It helps.

I would really appreciate it on how to flip the order?

For context, I adjusted the code as follows:

=let
mylist = Text.Split([rate_values],",")
in
Text.Combine(List.Transform(List.Distinct(mylist), each Number.ToText(List.Count(mylist) - List.Count(List.RemoveItems(mylist, {_})))&" Count -"&_&"%"),", ")

This generates an output "1 Count - 10%, 2 Count - 20%"

My desired format is as follows:
"10% - 1 Count, 20% - 2 Count".

[Ferdy]

Great thanks!