Transform data for pivot

[k3s3]

Hello, would greatly appreciate some help with power query that transforms my data from this:

User

Q1Question

Q1Category

Q1Answer

Q2Question

Q2Category

Q2Answer

Q3Question

Q3Category

3Answer

Q4Question

Q4Category

Q4Answer

Q58Question

Q58Category

Q58Answer

User 1

Age?

Physical

High

Height?

Physical

Low

Shoe Size?

Physical

Medium

Preferred food?

Preference

High

Preferred Colour?

Preference

High

User 2

Age?

Physical

High

Height?

Physical

High

Shoe Size?

Physical

Medium

Preferred food?

Preference

Low

Preferred Colour?

Preference

Low

User 3

Age?

Physical

Medium

Height?

Physical

High

Shoe Size?

Physical

Medium

Preferred food?

Preference

Low

Preferred Colour?

Preference

Low

User 4

Age?

Physical

Medium

Height?

Physical

High

Shoe Size?

Physical

High

Preferred food?

Preference

Low

Preferred Colour?

Preference

Medium

User 5

Age?

Physical

Medium

Height?

Physical

High

Shoe Size?

Physical

High

Preferred food?

Preference

Medium

Preferred Colour?

Preference

Medium

User 6

Age?

Physical

Low

Height?

Physical

Medium

Shoe Size?

Physical

Low

Preferred food?

Preference

Low

Preferred Colour?

Preference

Medium

...into  a format that will allow me to create a pivot table in Excel that will alllow my pivot table to look like this, counting the answsers of each type:

	High	Medium	Low
Physical
Age?	2	3	1
Height?	4	1	1
Shoe Size?	2	3	1
Preference
Preferred food?	1	1	4
Preferred Colour?	1	3	2

i.e. I want to group by 'Category' and then by 'Answer'

I have thousands of rows and 58x Question, Question Category and Answer and the fields arenot contiguous   

Here is a mock-up of the starting table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZE9C4MwEIb/imR2qR9dRVw6tNAincRB9PwAayAqYn994ykR7JW2KLTLe5rnwj1JgoBdaxBMZ5cdhuZFDWRc9PLPreoOhIbAwCAoAhODoAgsDIIisDEIarNQH/VQwc3AkeWc93URR6X8PBRZPhSQtVmwI+9k+jkHzS/uy50nSIr2NqwJSEEISLSU88RRK1DFME+Yuzxe8lbQfcrW+Np2Ymt1x0O/tR3alKxJyqqJ/6Zrba/79MhrZCcT5Wv/ypd4hA+V96TyeH7SV416Zby8uy0uOHwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, Q1Question = _t, Q1Category = _t, Q1Answer = _t, Q2Question = _t, Q2Category = _t, Q2Answer = _t, Q3Question = _t, Q3Category = _t, Q3Answer = _t, Q4Question = _t, Q4Category = _t, Q4Answer = _t, Q58Question = _t, Q58Category = _t, Q58Answer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User", type text}, {"Q1Question", type text}, {"Q1Category", type text}, {"Q1Answer", type text}, {"Q2Question", type text}, {"Q2Category", type text}, {"Q2Answer", type text}, {"Q3Question", type text}, {"Q3Category", type text}, {"Q3Answer", type text}, {"Q4Question", type text}, {"Q4Category", type text}, {"Q4Answer", type text}, {"Q58Question", type text}, {"Q58Category", type text}, {"Q58Answer", type text}})
in
    #"Changed Type"

It seems to me that I need to find a way to first stack the entries in all the fields ending 'Category', and 'Answer', but I'm struggling to know how to start?

[k3s3 · Accepted solution]

Just closing the loop.  The solution is here: https://www.mrexcel.com/board/threads/power-query-to-transform-data-ready-for-pivot.1231281/

[k3s3]

So... inspired by this video from howtoexcel.org, I managed to get a solution by first merging the columns for each question, and then selecting all the merged columns, unpivoting, then splitting the columns again by the delimiter.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLVIwVNJRckxPtQdSARmVxZnJiTlApkdmegaISgXSJWhyPvnlQDI4Iz9VITizCl2nb2pKZmkuSKwoNS21qCg1RSEtPz/FHi6SmpecirABoco5Pye/tAi7ulgdqGuNSHYtVI5S50I8TdC1IGVwxxpjdSzcxsHmXBPqOxcjkilxLNQlcPeaDpR7sUQCkU42w+pkiP+xuhduFS4Xo4cdNQI4FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, Q1Question = _t, Q1Category = _t, Q1Answer = _t, Q2Question = _t, Q2Category = _t, Q2Answer = _t, Q3Question = _t, Q3Category = _t, Q3Answer = _t, Q4Question = _t, Q4Category = _t, Q4Answer = _t, Q58Question = _t, Q58Category = _t, Q58Answer = _t]),
       #"Merged Columns" = Table.CombineColumns(Source,{"Q1Question", "Q1Category", "Q1Answer"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Q1"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Q2Question", "Q2Category", "Q2Answer"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Q2"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Q3Question", "Q3Category", "Q3Answer"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Q3"),
    #"Merged Columns3" = Table.CombineColumns(#"Merged Columns2",{"Q4Question", "Q4Category", "Q4Answer"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Q4"),
    #"Merged Columns4" = Table.CombineColumns(#"Merged Columns3",{"Q58Question", "Q58Category", "Q58Answer"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Q58"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns4", {"User"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Value", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Value.1", "Value.2", "Value.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}, {"Value.3", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Value.1", "Question"}, {"Value.2", "Category"}, {"Value.3", "Answer"}})
in
    #"Renamed Columns"

...But, that will be very cumbersome for 60-odd columns. Does anyone know how to do that in M / Power Query so that it will merge all columns beginning with Qsomething, where Qsomething can be a single or double-digit number?