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

[Ferdy]

Great thanks!