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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors