Group results and remove duplicates and blanks

[MichaelHutchens]

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 🙂

[Ashish_Mathur · Accepted 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.

[MichaelHutchens]

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? 🙂

[Ashish_Mathur]

I do not know why it isn't working.  Worked just fine on a simple Excel table.

[MichaelHutchens]

Might there be a DAX measure or a virtual table that could achieve the same result?

[FreemanZ]

hi @MichaelHutchens 

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.

[MichaelHutchens]

Thank you @FreemanZ , that's super helpful as well 🙂

[MichaelHutchens]

Nevermind - I've managed to update the source info in the M code and get rid of the error. Thanks so much! 🙂

[Ashish_Mathur]

You are welcome.