Combine rows based on unique id

[TBenders]

Hi,

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

I have a table like this:

id	branche
101	business
101	hr
102	business
103	business
103	finance
103	hr

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

id	branche
101	business, hr
102	business
103	business, 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?

[Greg_Deckler · Accepted solution]

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.

[Greg_Deckler]

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?

[oscarflorez99]

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.

[huguest]

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]

Nevermind, I found the problem 🙂