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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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