Forum Discussion
Fill rows based on combined values from succeeding rows
Here's my current dataset:
| Column 1 | Column 2 |
| Customer Name1 | aaa |
| Street Name1 | bbb |
| City Name1 | ccc |
| Country1 | ddd |
| Customer Name2 | eee |
| Street Name2 | fff |
| City Name2 | ggg |
| Country2 | hhh |
I want it to look like this:
| Column 1 | Column 2 |
| Customer Name1, Street Name1, City Name1, Country1 | aaa |
| Customer Name1, Street Name1, City Name1, Country1 | bbb |
| Customer Name1, Street Name1, City Name1, Country1 | ccc |
| Customer Name1, Street Name1, City Name1, Country1 | ddd |
| Customer Name2, Street Name2, City Name2, Country2 | eee |
| Customer Name2, Street Name2, City Name2, Country2 | fff |
| Customer Name2, Street Name2, City Name2, Country2 | ggg |
| Customer Name2, Street Name2, City Name2, Country2 | hhh |
Hi Anonymous
Does every customer have the same 4 rows? Here is one way, otherwise you need other approaches
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xcw7DoAgEADRq5itafQK9DaWhILPAhZIgkvB7RVJiNq+SUYI4OWkFDFPq4o4AwOlFEgmYKOMSIO11g/znepAY0zHVA7KtZG1ttP7u9wBEf/fxs6577eh9/79bRRCACkv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundTowardZero([Index]/4)), #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"all", each Text.Combine( _[Column1],",") }}), #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Custom"}, #"Grouped Rows", {"Custom"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"all"}, {"all"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded Grouped Rows",{"all", "Column2"}) in #"Removed Other Columns"
3 Replies
Hi Anonymous
Does every customer have the same 4 rows? Here is one way, otherwise you need other approaches
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xcw7DoAgEADRq5itafQK9DaWhILPAhZIgkvB7RVJiNq+SUYI4OWkFDFPq4o4AwOlFEgmYKOMSIO11g/znepAY0zHVA7KtZG1ttP7u9wBEf/fxs6577eh9/79bRRCACkv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundTowardZero([Index]/4)), #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"all", each Text.Combine( _[Column1],",") }}), #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Custom"}, #"Grouped Rows", {"Custom"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"all"}, {"all"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded Grouped Rows",{"all", "Column2"}) in #"Removed Other Columns"Hi @rblaze21
Does every customer have the same 4 rows? Here is one way, otherwise you need other approaches
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xcw7DoAgEADRq5itafQK9DaWhILPAhZIgkvB7RVJiNq+SUYI4OWkFDFPq4o4AwOlFEgmYKOMSIO11g/znepAY0zHVA7KtZG1ttP7u9wBEf/fxs6577eh9/79bRRCACkv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundTowardZero([Index]/4)), #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"all", each Text.Combine( _[Column1],",") }}), #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Custom"}, #"Grouped Rows", {"Custom"}, "Grouped Rows", JoinKind.LeftOuter), #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"all"}, {"all"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded Grouped Rows",{"all", "Column2"}) in #"Removed Other Columns"
