Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hi folks, I'm hoping someone might be able to help.
I have a table consisting of two columns of strings ("URL" and "Labels"). For each URL, there are zero (to multiple) Labels. Each label is shown on a separate row, so there duplicates of URLs. There is also a default row for each URL that shows a blank Label field. So if I have a URL with two labels, there will be 3 rows. If I have a URL with 4 labels, there will be 3 rows. If I have a URL with no labels, there will always be one row. Example:
URL | Labels |
String1 | ABC |
String1 | |
String2 | |
String3 | |
String3 | ABC |
String3 | GHI |
String3 | DEF |
String4 | GHI |
String4 | ABC |
And here's the output I need. I need one row for each String, and a summary of all labels applied to them, separated by commas:
URL | Labels |
String1 | ABC |
String2 | |
String3 | ABC, DEF, GHI |
String4 | ABC, GHI |
Any help would really be appreciated 🙂
Solved! Go to Solution.
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"URL"}, {{"All labels", each Text.Combine([Labels],","), type nullable text}})
in
#"Grouped Rows"
Hope this helps.
Hi,
This M code works
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"URL"}, {{"All labels", each Text.Combine([Labels],","), type nullable text}})
in
#"Grouped Rows"
Hope this helps.
Thanks so much for the quick reply @Ashish_Mathur . I'm attempting to slot that M code into my report and I'm getting the error below:
Here's the previous step:
And here's the code I'm attempting (and failing) to use:
let
Source = PowerPlatform.Dataflows(null),
Workspaces = Source{[Id="Workspaces"]}[Data],
#"d8c267e9-89d3-4266-82e5-ab0414af7778" = Workspaces{[workspaceId="d8c267e9-89d3-4266-82e5-ab0414af7778"]}[Data],
#"65b124cc-063b-4c6f-9f76-7096733c6c23" = #"d8c267e9-89d3-4266-82e5-ab0414af7778"{[dataflowId="65b124cc-063b-4c6f-9f76-7096733c6c23"]}[Data],
#"Kotahi_Pages and Files_Taxonomy_" = #"65b124cc-063b-4c6f-9f76-7096733c6c23"{[entity="Kotahi_Pages and Files_Taxonomy",version=""]}[Data],
#"Grouped Rows" = Table.Group(Source, {"URL"}, {{"All labels", each Text.Combine([Labels],","), type nullable text}})
in
#"Grouped Rows"
What am I doing wrong? 🙂
I do not know why it isn't working. Worked just fine on a simple Excel table.
Might there be a DAX measure or a virtual table that could achieve the same result?
With DAX, it works like this:
SumTable =
ADDCOLUMNS(
VALUES(TableName[URL]),
"Labels",
CALCULATE(
CONCATENATEX(
VALUES(TableName[Labels]),
TableName[Labels],
", "
)
)
)
in case of big data, Power Query is better.
Nevermind - I've managed to update the source info in the M code and get rid of the error. Thanks so much! 🙂
You are welcome.
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
105 | |
69 | |
48 | |
47 | |
47 |