cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
TBenders Regular Visitor
Regular Visitor

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

Accepted Solutions
Super User
Super User

Re: Combine rows based on unique id

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


4 REPLIES 4
Super User
Super User

Re: Combine rows based on unique id

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?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

Re: Combine rows based on unique id

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


huguest Member
Member

Re: Combine rows based on unique id

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.

huguest Member
Member

Re: Combine rows based on unique id

Nevermind, I found the problem Smiley Happy