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?

  • huguest's avatar
    huguest
    Advocate II
    8 years ago

    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's avatar
      huguest
      Advocate II
      8 years ago

      Nevermind, I found the problem :)

  • 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.