Excel formula function or macro? Data Migration for Horizontal Columns to Vertical Columns

sashakorniakUK 

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"