Same idea, but without hardcoding column names
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(
Source,
{"address"},
{
{"Tables",
each
[ #"Removed Columns" = Table.RemoveColumns(_,{"address"}),
#"Added Index" = Table.AddIndexColumn(
#"Removed Columns",
"Index", 1, 1, Int64.Type
),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
#"Added Index",
{"Index"}, "Attribute", "Value"
),
#"Merged Columns" = Table.CombineColumns(
Table.TransformColumnTypes(
#"Unpivoted Other Columns",
{{"Index", type text}}, "en-GB"
),
{"Attribute", "Index"},
Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"
),
#"Transposed Table1" = Table.Transpose(#"Merged Columns"),
#"Promoted Headers" = Table.PromoteHeaders(
#"Transposed Table1",
[PromoteAllScalars=true]
)
][#"Promoted Headers"]
},
{"Count", each Table.RowCount(_), Int64.Type}
}
),
// Generate Column Names
MaxRows = List.Max(#"Grouped Rows"[Count]),
RemoveAddress = Table.RemoveColumns(Source,{"address"}),
#"Kept First Rows" = Table.FirstN(RemoveAddress,1),
#"Added Custom" = Table.AddColumn(#"Kept First Rows", "Index", each {1..MaxRows}),
#"Expanded Index" = Table.ExpandListColumn(#"Added Custom", "Index"),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Index", {"Index"}, "Attribute", "Value"),
GetTogether = Table.CombineColumns(
Table.TransformColumnTypes(
#"Unpivoted Other Columns",
{{"Index", type text}}, "en-GB"),{"Attribute", "Index"},
Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"ColumnNames"
),
ColumnNames = GetTogether[ColumnNames],
#"Expanded Tables" = Table.ExpandTableColumn(#"Grouped Rows", "Tables", ColumnNames)
in
#"Expanded Tables"
