split multiple column values into multiple rows

A more dynamic solution in Power Query, that allows for additional groups of columns, is:

1. Unpivot all columns except Record ID.

2. Get rid of the sequence numbers in the "Attribute" column (former column names).

3. Add a temporary Index column (from 0) and integer-divide this by 4 (the number of fields in each group),
    so you get 0,0,0,0,1,1,1,1,2,2,2,2 etcetera.

4. Pivot the "Attribute" column with advanced option "Don't Aggregate".

5. Remove the temporary Index column.

Code below and this video takes you through the steps (the video starts at a "Changed Type" step which I removed after the recording as this would jeopardize the dynamics of the solution).

At the end of the code you may want to add a "Change Type" step.

The steps are all created via menu options in Power Query, but I adjusted the "Extracted First Characters" code to extract all characters up to the first digit (0-9).

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Record ID"}, "Attribute", "Value"),
    #"Extracted First Characters" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.Start(_, Text.PositionOfAny(_,Text.ToList("0123456789"))), type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Extracted First Characters", "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 4), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"