Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe 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!
Check out the July 2025 Power BI update to learn about new features.