Unpivoting clustered columns

Hi DouweMeer 

You can put the  following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcyxCcAwEATBVszHwqC7ch4FchGu31h70cIG012zRr3zPhHxyWZu5mY+86JK/XeNLuEIRzjCEY5wFEdxFEfHMY5xjGMc4xjHcRzHcVxrfQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, V1 = _t, #"V2:" = _t, V3 = _t, #"A1:" = _t, #"A2," = _t, A3 = _t, #"B1 " = _t, B2 = _t, B3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"V1", type text}, {"V2:", type text}, {"V3", type text}, {"A1:", type text}, {"A2,", type text}, {"A3", type text}, {"B1 ", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Id"}, "Attribute", "Value"),
    #"Split Column by Position" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByPositions({0, 1}, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Text.Combine(
  List.RemoveNulls(
    List.Transform(
      Text.ToList([Attribute.2]),
    each if Value.Is(Value.FromText(_), type number) 
    then _ else null)
    )
  )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute.1]), "Attribute.1", "Value"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Pivoted Column",{{"Custom", Int64.Type}})
in
    #"Changed Type2"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.