Forum Discussion

pang22's avatar
pang22
Helper III
2 years ago
Solved

Group data into one row

Dears,

 

I have below data in PowerBI.

Original data:

 

How to group the column in one row using power query like below output?

Output:

 

Need Help
  • TomMartens's avatar
    TomMartens
    2 years ago

    Hey pang22 ,

     

    to achieve this you can use the following two steps.

     

    My table looks like this:

    The first step groups the rows by number, but it's important to keep all the rows by using the aggregation function AllRows:

    After this step your table will look like this:

    Then in the second step you can add a custom column using Text.Combine:

    The M function for the custom column:

    Text.Combine( [AllRows][Color] ,  ",")

    [AllRows] is referencing the table and the column selector [Color] is extracting the values of the column Color as a list, the first parameter for Text.Combine.

    Finally, you can delete the column "AllRows". Without deleting the column the final table will look like this:

    Hopefully, this provides what you are looking for.

     

    Regards,

    Tom

3 Replies

  • manvishah17's avatar
    manvishah17
    Solution Supplier
    2 years ago

    Hi pang22 ,
    I have solved similar problem like yours

     

    let
    Source = Excel.Workbook(File.Contents("D:\OneDrive - inkeysolutions.com\country.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"country", type text}, {"state", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"country"}, {{"ConcatenatedStates", each Text.Combine([state], ""), type text}})

in

   #"Grouped Rows"

    Just add this step in your power query .

    #"Grouped Rows" = Table.Group(#"Changed Type1", {"number"}, {{"Concatenate", each Text.Combine([color], ";"), type text}})

     

  • TomMartens's avatar
    TomMartens
    Super User
    2 years ago

    Hey pang22 ,

     

    to achieve this you can use the following two steps.

     

    My table looks like this:

    The first step groups the rows by number, but it's important to keep all the rows by using the aggregation function AllRows:

    After this step your table will look like this:

    Then in the second step you can add a custom column using Text.Combine:

    The M function for the custom column:

    Text.Combine( [AllRows][Color] ,  ",")

    [AllRows] is referencing the table and the column selector [Color] is extracting the values of the column Color as a list, the first parameter for Text.Combine.

    Finally, you can delete the column "AllRows". Without deleting the column the final table will look like this:

    Hopefully, this provides what you are looking for.

     

    Regards,

    Tom

  • Thejeswar's avatar
    Thejeswar
    Super User
    2 years ago

    Hi pang22 ,

    In case you want to create this CombinedStates Column in DAX, then you can do with a Calculated Table Option with the below DAX Statement

    newtable = SUMMARIZE('Table', 'Table'[Number], "CombinedStates", CONCATENATEX('Table', 'Table'[Color],";"))

    Click on the new table option in table view and put in the above shared DAX Query

     

    Regards,