Forum Discussion

TBenders's avatar
TBenders
Helper II
10 years ago
Solved

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: id branche 101 business 101 hr 102 business 103 business 103 finance ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    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's avatar
Greg_Deckler
Community Champion
10 years ago

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's avatar
oscarflorez99
Frequent Visitor
5 years ago

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.