Forum Discussion
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.
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.
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
Super User
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
