Forum Discussion
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: ...
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.
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
Community Champion
Community ChampionHi 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