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
TBenders
Helper II
Helper II

Combine rows based on unique id

Hi,

This should be easy to achieve, yet I'm breaking my head over it.

I have a table like this:

idbranche
101business
101hr
102business
103business
103finance
103hr



I want to combine the branches and make id unique, like this:

idbranche
101business, hr
102business
103business, finance, hr



Anyone know how to achieve this, or how to get the combined branches in a new column so I can remove dupplicate rows based on id?

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

Here is the Power Query "M" code version:

 

let
    Source = Csv.Document(File.Contents("C:\temp\powerbi\group.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"id", type text}, {"branche", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"Branches", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([Branches][branche],",")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Branches"})
in
    #"Removed Columns"

Basically, group the rows but keep all as the aggregation, return a table for "branche". Then, do a text combine on that table to get all of the values formatted as a single list of values with a comma separator.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

Here is the Power Query "M" code version:

 

let
    Source = Csv.Document(File.Contents("C:\temp\powerbi\group.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"id", type text}, {"branche", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"Branches", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([Branches][branche],",")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Branches"})
in
    #"Removed Columns"

Basically, group the rows but keep all as the aggregation, return a table for "branche". Then, do a text combine on that table to get all of the values formatted as a single list of values with a comma separator.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

Create a DAX measure like this:

 

Branches = CONCATENATEX(VALUES('group'[branche]),[branche],",")

Put "id" column and "Branches" measure in a table visualization and set your "id" column to "Do not summarize". 

 

Or, did you want this in an "M" solution?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Good afternoon Greg,

 

thanks for providing the DAX code, I really appreciate it. In addition, I would like to know if there is another formula that could be added in order to list the concatenated values from lowest to highest.

 

Example (random data): 

 

ID:             Year:

1               2010

1               2008

1               2009

 

Based on this data, I would like to have the years listed as follows: 2008,2009,2010. However, without doing any trasnformations to your DAX code, I get the numbers this way: 2010,2008,2009.

 

Hope I have explained myself clearly enough that you are able to help me with this. 

 

Thanks,

 

Oscar F.

Hello,

I am trying to use this DAX formula but instead of getting the concatenated branche values specific to each ID, I get all branche values for each ID in my table. Any idea what I am doing wrong?

Thanks.

Nevermind, I found the problem 🙂

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.