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

Need Help

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

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