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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MichaelHutchens
Helper IV
Helper IV

Group results and remove duplicates and blanks

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:

URLLabels
String1ABC
String1 
String2 
String3 
String3ABC
String3GHI
String3DEF
String4GHI
String4ABC


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:

URLLabels
String1ABC
String2 
String3ABC, DEF, GHI
String4ABC, GHI

 

Any help would really be appreciated 🙂

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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:

MichaelHutchens_0-1674446106040.png

Here's the previous step:

MichaelHutchens_1-1674446134283.png

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

hi @MichaelHutchens 

With DAX, it works like this:

SumTable = 
ADDCOLUMNS(
    VALUES(TableName[URL]),
    "Labels",
    CALCULATE(
        CONCATENATEX(
        VALUES(TableName[Labels]),
        TableName[Labels],
        ", "
        )
    )
)

 

FreemanZ_0-1674452757799.png

in case of big data, Power Query is better.

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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