Fill rows based on combined values from succeeding rows

[Anonymous]

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

[Syndicate_Admin]

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"

[Vera_33 · Accepted solution]

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"

[Anonymous]

Thank you so much!