Aggregating multiple key-value columns

[asm495]

I have some data in this format:

Id	Question1	Value1	Question2	Value2	Question3	Value3
aaaa	Pears	4	Apples	2	Oranges	5
aaab	Oranges	2	Pears	5	Apples	7
aaac	Kiwi	1	Oranges	8	Apples	3

The options for each of the questions are the same. The values are all numerical.

What I would like to be able to do is to transform it to be in this format with multiple rows where the question options are all in one column and the values are all in one column.

Id	Question	Option	Value
aaaa	Question1	Pears	4
aaaa	Question2	Apples	2
aaaa	Question3	Oranges	5
aaab	Question1	Oranges	2
aaab	Question2	Pears	5
aaab	Question3	Apples	7
aaac	Question1	Kiwi	1
aaac	Question2	Oranges	8
aaac	Question3	Apples	3

I cannot seem to find a way to achieve this. Does anyone have any suggestions please? Thanks!

[HotChilli · Accepted solution]

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.

[AntrikshSharma · Accepted solution]

@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

[asm495]

I have tested this and this also works well. One thing that anyone else who uses this solution should be aware of is that it is required for the sequence of the original columns to be: Question,Value,Question,Value,...

My data wasn't in this sequence and I had to correct this before it would work.

[asm495]

Thank you for the reply, it is appreciated. I have not had the chance to try this as I was trying the simpler solution offered first. I will try and test this later also to see if it works.

[HotChilli · Accepted solution]

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]

This is brilliant and seems to do exactly what I need. In my real life scenario I did not have the columns so neatly named with numbers but I adapted the solution by splitting on a different delimiter.