Forum Discussion

GraceJinM's avatar
GraceJinM
Frequent Visitor
2 years ago
Solved

How to merge multiple rows into one row based on section name?

Hello, 

 

I need to merge multiple rows of my data into one row based on the section name. I'd like to do this in power query.

 

Here is an example of what my data currently looks like: 

 

What my current data looks like

 

Here is what I want my data to look like:

 

What I want my data to look like

 

 

 

Thanks!

Need Help
  • AlienSx's avatar
    AlienSx
    2 years ago
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tx = List.Transform(
        List.Skip(Table.ColumnNames(Source)), 
        (x) => {x, (tbl) => 
            [col = List.RemoveNulls(Table.Column(tbl, x)),
            func = if col{0}? is text 
                then Text.Combine(col, ",")
                else List.Sum(col)][func]}
    ),
    group = Table.Group(Source, "Section", tx)
in
    group
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi GraceJinM 

    You can try the following.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTICYiByBlGxOhAxQyA2BmIXsDxMFIKMYAqdEHxXZDFDiLgbFjF3iHmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Section = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Section", type text}, {"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Section"}, {{"Data1", each try List.Sum([Column1]) otherwise Text.Combine([Column1],",")}, {"Data2", each try List.Sum([Column2]) otherwise Text.Combine([Column2],",")}, {"Data3", each try List.Sum([Column3]) otherwise Text.Combine([Column3],",")}, {"Data4", each try List.Sum([Column4]) otherwise Text.Combine([Column4],",")}})
in
    #"Grouped Rows"

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

11 Replies

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Icon for Super User rankSuper User
    2 years ago

    Follow this step
    this is your data in power query

     

    right click on column section and use unpivot other columns to reach the next result

     

     

    then select Attribute column and go to transform tab and pick Pivot column comand.
    like the below image pic value for value column.

    hit ok to reach your result as below.



    I hope this help you, if you have any other question, pls do not hesitate and ask

    • GraceJinM's avatar
      GraceJinM
      Frequent Visitor
      2 years ago

      I realized I misrepresented my data and what I want my end product to look like. When I took a closer look at my data, I realized that there were mutliple rows that had responses in the same column. For this situations, I needed the value to be summed if it was a number and split by comma when it was text. 

       

      Here is what my data actually looks like: 

       

       

      Here is what I want it to look like: 

       

       

      • Omid_Motamedise's avatar
        Omid_Motamedise
        Icon for Super User rankSuper User
        2 years ago

        what happened on number 2 for the third rows on column 3 for section A?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,

    Thanks for the solutions Omid_Motamedise and AlienSx offered, and i want to offer some more infotmation for user to refer to.

    hello GraceJinM , you can create a blank query and input the following code.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUpMSgaSUBSrAxEEotQ0CF2SgSwKROkZMJVOEAEjJM0gIUMI3xhNGUxxbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Section = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Column1", type text}, {"Column2", type text}, {"Column4", type text}, {"Column3", type text}}, "en-US"),{"Column1", "Column2", "Column4", "Column3"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Section"}, {{"Data", each Text.Combine([Merged],",")}}),
    #"Trimmed Text" = Table.TransformColumns(#"Grouped Rows",{{"Data", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Data", each Text.Combine(List.RemoveItems(Text.Split(_,","),{""}),",")}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Cleaned Text", "Data", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Data.1", "Data.2", "Data.3", "Data.4"})
in
    #"Split Column by Delimiter"

     

    Output

     

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • GraceJinM's avatar
      GraceJinM
      Frequent Visitor
      2 years ago

      I realized I misrepresented my data and what I want my end product to look like. When I took a closer look at my data, I realized that there were mutliple rows that had responses in the same column. For this situations, I needed the value to be summed if it was a number and split by comma when it was text. 

       

      Here is what my data actually looks like: 

       

       

       

      Here is what I want it to look like: 

      • AlienSx's avatar
        AlienSx
        Icon for Super User rankSuper User
        2 years ago
        let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tx = List.Transform(
        List.Skip(Table.ColumnNames(Source)), 
        (x) => {x, (tbl) => 
            [col = List.RemoveNulls(Table.Column(tbl, x)),
            func = if col{0}? is text 
                then Text.Combine(col, ",")
                else List.Sum(col)][func]}
    ),
    group = Table.Group(Source, "Section", tx)
in
    group
  • GraceJinM's avatar
    GraceJinM
    Frequent Visitor
    2 years ago

    Hi Everyone! Thank you for the responses so far. 

     

    If anyone has this specific issue, then the solutions below will work for you; however, I realized I misrepresented my data and what I want my end product to look like. When I took a closer look at my data, I realized that there were mutliple rows that had responses in the same column. For this situations, I needed the value to be summed if it was a number and split by comma when it was text. 

     

    Here is what my data actually looks like: 

     

    Here is what I want it to look like: 

     

     

     

     

    • ronrsnfld's avatar
      ronrsnfld
      Icon for Super User rankSuper User
      2 years ago

      What about when have both numbers and text, as in Column3?

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi GraceJinM 

      You can try the following.

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTICYiByBlGxOhAxQyA2BmIXsDxMFIKMYAqdEHxXZDFDiLgbFjF3iHmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Section = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Section", type text}, {"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Section"}, {{"Data1", each try List.Sum([Column1]) otherwise Text.Combine([Column1],",")}, {"Data2", each try List.Sum([Column2]) otherwise Text.Combine([Column2],",")}, {"Data3", each try List.Sum([Column3]) otherwise Text.Combine([Column3],",")}, {"Data4", each try List.Sum([Column4]) otherwise Text.Combine([Column4],",")}})
in
    #"Grouped Rows"

      Output

      Best Regards!

      Yolo Zhu

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

       

    • dufoq3's avatar
      dufoq3
      Icon for Community Champion rankCommunity Champion
      2 years ago

      Hi GraceJinM, different approach:

       

      Output v1

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTICYiByBlGxOhAxQyA2BmIXsDxMFIKMYAqdEHxXZDFDiLgbFjF3iHmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Section = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    GroupedRows = Table.Group(Source, {"Section"}, {{"All", each
        [ a = Table.RemoveColumns(_, {"Section"}),
          b = List.TransformMany(Table.ToColumns(a), each {
                     [ b1 = List.Transform(_, (w)=> try Number.From(w) otherwise w),
                       b2 = try List.Sum(b1) otherwise Text.Combine(List.Select(List.Transform(b1, Text.From), (w)=> not List.Contains({"0".."9"}, w)), ",")
                     ][b2] },
                     (x,y)=> y ),
          c = Table.FromRows({ {[Section]{0}} & b }, Value.Type(_))
        ][c], type table}}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined

       

      Output v2

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTICYiByBlGxOhAxQyA2BmIXsDxMFIKMYAqdEHxXZDFDiLgbFjF3iHmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Section = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    GroupedRows = Table.Group(Source, {"Section"}, {{"All", each
        [ a = Table.RemoveColumns(_, {"Section"}),
          b = List.TransformMany(Table.ToColumns(a), each {
                     [ b1 = List.Transform(_, (w)=> try Number.From(w) otherwise w),
                       b2 = try List.Sum(b1) otherwise Text.Combine(List.Transform(b1, Text.From), ",")
                     ][b2] },
                     (x,y)=> y ),
          c = Table.FromRows({ {[Section]{0}} & b }, Value.Type(_))
        ][c], type table}}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined