Pivoting/transposing problem

Here's a method of generating this in PQ for Excel.

In PQ, select the Advanced Editor and paste the code below in place of what you see.

You may need to alter the first one (or two) lines of code so it refers to your actual data source instead of this dummy data.

Examine the code comments and the Applied Steps window to understand the algorithm

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZQ7b8IwEID/ipWZIRgIbKgDJQNtKlEJEGKw4Eos5VHlAeLf9+y6VcAXN83g2LL13dn32dnvvSH3fW/gLSGDQiQ4GmILxQXYLa/ZKc+Abec4tYPSLL7inHcYECi30N1cA/gZYVvlVxpUi5tYHmOkNPxZwAcUCn6PpU6sgFCe42aAN1FUTG1p7OJFhV2A7QVOsk7JABMdQFRMlqzCjIpc5yngODubBC04N9Fb8i+SErDzicL94lNXyR9SP21Xzz19WWiLr2+UN4RZ5H+FmQDdhGl+Rh7bKcyopgvWxZUJMLJP/peq5l1p4lE0CaY9ddlsiy/qitmwQ5l+oA8F/wnQRZnmOXHsvu/rju8mzbV9hzji93D4Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UserID = _t, Catergory = _t, #"Question No." = _t, Question = _t, Answer = _t, #"Answer Score" = _t, #"Answer Band" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UserID", type text}, {"Catergory", type text}, {"Question No.", Int64.Type}, {"Question", type text}, {"Answer", type text}, {"Answer Score", Int64.Type}, {"Answer Band", type text}}),
 
//Remove unneeded columns
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Catergory", "Question"}),

//Select User Id and Question No -- then Unpivot other columns
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"UserID", "Question No."}, "Attribute", "Value"),

//Add custom column with desired column headers
// then remove Attribute column
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "colHeaders", 
        each "Q." & Number.ToText([#"Question No."]) & 
                    "#(lf)" & 
                    List.Range({"Answer","Score","Rating"},
                        List.PositionOf({"Answer","Answer Score","Answer Band"},[Attribute]),1){0}),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Question No.", "Attribute"}),

//Add Index and modulo columns to enable sorting into desired header order
// then remove those columns
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns1", "Index", 0, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 3), type number),
    #"Sorted Rows" = Table.Sort(#"Inserted Modulo",{{"Modulo", Order.Ascending}, {"Index", Order.Ascending}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Sorted Rows",{"Index", "Modulo"}),

//Pivot the colHeaders column with NO aggregation
    #"Pivoted Column" = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[colHeaders]), "colHeaders", "Value")
in
    #"Pivoted Column"

Hi k2s2 ,

Using below M codes:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UserID", type any}, {"Catergory", type text}, {"Question No.", Int64.Type}, {"Question", type text}, {"Answer", type text}, {"Answer Score", Int64.Type}, {"Answer Band", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each "Q."&Text.From([#"Question No."])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each "Second"&[Custom]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each "Third"&[Custom]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Catergory", "Question No.", "Question"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Answer"),
    #"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Custom.1]), "Custom.1", "Answer Score"),
    #"Pivoted Column2" = Table.Pivot(#"Pivoted Column1", List.Distinct(#"Pivoted Column1"[Custom.2]), "Custom.2", "Answer Band"),
    #"Grouped Rows" = Table.Group(#"Pivoted Column2", {"UserID"}, {{"Q.1", each List.Max([Q.1]), type nullable text}, {"Q.2", each List.Max([Q.2]), type nullable text}, {"Q.3", each List.Max([Q.3]), type nullable text}, {"Q.4", each List.Max([Q.4]), type nullable text}, {"Q.5", each List.Max([Q.5]), type nullable text}, {"Q.6", each List.Max([Q.6]), type nullable text}, {"Q.7", each List.Max([Q.7]), type nullable text}, {"Second Q.1", each List.Max([SecondQ.1]), type nullable number}, {"Second Q.2", each List.Max([SecondQ.2]), type nullable number}, {"Second Q.3", each List.Max([SecondQ.3]), type nullable number}, {"Second Q.4", each List.Max([SecondQ.4]), type nullable number}, {"Second Q.5", each List.Max([SecondQ.5]), type nullable number}, {"Second Q.6", each List.Max([SecondQ.6]), type nullable number}, {"Second Q.7", each List.Max([SecondQ.7]), type nullable number}, {"Third Q.1", each List.Max([ThirdQ.1]), type nullable text}, {"Third Q.2", each List.Max([ThirdQ.2]), type nullable text}, {"Third Q.3", each List.Max([ThirdQ.3]), type nullable text}, {"Third Q.4", each List.Max([ThirdQ.4]), type nullable text}, {"Third Q.5", each List.Max([ThirdQ.5]), type nullable text}, {"Third Q.6", each List.Max([ThirdQ.6]), type nullable text}, {"Third Q.7", each List.Max([ThirdQ.7]), type nullable text}})
in
    #"Grouped Rows"

And you will see:

Check my sample attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!