Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Ferdy
New Member

Table Group, text combine with count of occurrence

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:

 

Aapple, apple
Bapple, pear, pear
Cpear, orange, orange, orange
D

apple, apple, orange, orange

Epear

 

But I'd like to show it like this:

A2 x apple
B1 x apple, 2 x pear
C1 x pear, 3 x orange
D2 x apple, 2 x orange
E1 x pear

 

Can anyone help me to achieve this?

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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".

Great thanks!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.