Aggregating multiple key-value columns

Unpivot all columns except ID.

Select the Attribute column. Split it by delimiter (from Non-digit to digit).

Then Pivot the column Attribute.1 with Value in Values and choose Don't Aggregate in the advanced section.

asm495 Here is another way to solve this, a bit complex, but good to know how to do purely using M language.

let
    Source = Table.FromRows (
        Json.Document (
            Binary.Decompress (
                Binary.FromText (
                    "i45WSgQCJR2lgNTEomIgbQLEjgUFOakgjhEQ+xcl5qWDeaZKsTpg9UkowkZIuk2RdZvD1CcDOd6Z5ZlAyhBFqwWycmOl2FgA",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
        in
            type table [
                Id = _t,
                Question1 = _t,
                Value1 = _t,
                Question2 = _t,
                Value2 = _t,
                Question3 = _t,
                Value3 = _t
            ]
    ),
    ChangedType = Table.TransformColumnTypes (
        Source,
        {
            { "Id", type text },
            { "Question1", type text },
            { "Value1", Int64.Type },
            { "Question2", type text },
            { "Value2", Int64.Type },
            { "Question3", type text },
            { "Value3", Int64.Type }
        }
    ),
    AddedCustom = Table.AddColumn (
        ChangedType,
        "Custom",
        each
            let
                TargetCols = Record.RemoveFields ( _, "Id" ),
                RecToList = Record.ToList ( TargetCols ),
                Questions = 
                    List.Select (
                        Record.FieldNames ( TargetCols ),
                        each Text.StartsWith ( _, "Question" )
                    ),
                SplitIntoGroups = List.Split ( RecToList, 2 ),
                ToRecords = 
                    List.Transform (
                        SplitIntoGroups,
                        each Record.FromList ( _, { "Option", "Value" } )
                    ),
                Result = 
                    Table.FromColumns (
                        { Questions } & Table.ToColumns ( Table.FromRecords ( ToRecords ) ),
                        type table [ Question = text, Option = text, Value = Int64.Type ]
                    )
            in
                Result,
        type table [ Question = text, Option = text, Value = Int64.Type ]
    ),
    RemovedOtherColumns = Table.SelectColumns ( AddedCustom, { "Id", "Custom" } ),
    ExpandedCustom = 
        Table.ExpandTableColumn (
            RemovedOtherColumns,
            "Custom",
            { "Question", "Option", "Value" },
            { "Question", "Option", "Value" }
        )
in
    ExpandedCustom