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

[GraceJinM]

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!

[AlienSx · Accepted solution]

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]

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: 

[dufoq3]

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

[v-xinruzhu-msft · Accepted solution]

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.

[ronrsnfld]

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

[v-xinruzhu-msft]

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]

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 · Accepted solution]

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

[Omid_Motamedise]

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]

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]

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

[AlienSx]

@GraceJinM , check this out. Looks similar.