Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Great thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.