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

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

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.