Forum Discussion

Frequent Visitor

4 years ago

Solved

Pivoting/transposing problem

Hello I'm struggling to get power query to transpose / pivot this: UserID Catergory Question No. Question Answer Answer Score Answer Band 1200 General 1 Have you done X? Yes 1 N...

Need Help

ronrsnfld

4 years ago

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"

v-kelly-msft

4 years ago

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!

Pivoting transposing problem.xlsx22 KB

k2s2

Frequent Visitor

4 years ago

Can anybody give me some guidance on how to do this in Power Query, please?

BA_Pete

Super User

4 years ago

Hi k2s2 ,

Try the code below.

The tricks are in creating unique column values to be pivoted to column headers, then condensing the table to non-null values at the end.

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}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Catergory", "Question"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Question No.", "qAnswer"}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns", "qAnswer", "qScore"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "qScore", "qRating"),
    #"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column1",{"UserID", "qAnswer", "qScore", "qRating", "Answer", "Answer Score", "Answer Band"}),
    #"Added Prefix" = Table.TransformColumns(#"Reordered Columns", {{"qAnswer", each "Answer Q" & Text.From(_, "en-GB"), type text}}),
    #"Added Prefix1" = Table.TransformColumns(#"Added Prefix", {{"qScore", each "Score Q" & Text.From(_, "en-GB"), type text}}),
    #"Added Prefix2" = Table.TransformColumns(#"Added Prefix1", {{"qRating", each "Rating Q" & Text.From(_, "en-GB"), type text}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Prefix2", {{"qAnswer", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Added Prefix2", {{"qAnswer", type text}}, "en-GB")[qAnswer]), "qAnswer", "Answer"),
    #"Pivoted Column1" = Table.Pivot(Table.TransformColumnTypes(#"Pivoted Column", {{"qScore", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Pivoted Column", {{"qScore", type text}}, "en-GB")[qScore]), "qScore", "Answer Score"),
    #"Pivoted Column2" = Table.Pivot(#"Pivoted Column1", List.Distinct(#"Pivoted Column1"[qRating]), "qRating", "Answer Band"),
    #"Grouped Rows" =
	Table.Group(
		#"Pivoted Column2",
		{"UserID"},
		{
			{"Answer Q1", each List.Select([Answer Q1], each _ <> null){0}},
			{"Answer Q2", each List.Select([Answer Q2], each _ <> null){0}},
			{"Answer Q3", each List.Select([Answer Q3], each _ <> null){0}},
			{"Answer Q4", each List.Select([Answer Q4], each _ <> null){0}},
			{"Answer Q5", each List.Select([Answer Q5], each _ <> null){0}},
			{"Answer Q6", each List.Select([Answer Q6], each _ <> null){0}},
			{"Answer Q7", each List.Select([Answer Q7], each _ <> null){0}},
			{"Score Q1", each List.Select([Score Q1], each _ <> null){0}},
			{"Score Q2", each List.Select([Score Q2], each _ <> null){0}},
			{"Score Q3", each List.Select([Score Q3], each _ <> null){0}},
			{"Score Q4", each List.Select([Score Q4], each _ <> null){0}},
			{"Score Q5", each List.Select([Score Q5], each _ <> null){0}},
			{"Score Q6", each List.Select([Score Q6], each _ <> null){0}},
			{"Score Q7", each List.Select([Score Q7], each _ <> null){0}},
			{"Rating Q1", each List.Select([Rating Q1], each _ <> null){0}},
			{"Rating Q2", each List.Select([Rating Q2], each _ <> null){0}},
			{"Rating Q3", each List.Select([Rating Q3], each _ <> null){0}},
			{"Rating Q4", each List.Select([Rating Q4], each _ <> null){0}},
			{"Rating Q5", each List.Select([Rating Q5], each _ <> null){0}},
			{"Rating Q6", each List.Select([Rating Q6], each _ <> null){0}},
			{"Rating Q7", each List.Select([Rating Q7], each _ <> null){0}}
		}
	)
in
    #"Grouped Rows"

Pete

Forum Discussion

Pivoting/transposing problem

Related content

Pivot/ transpose

Transpose & Pivot using Power Query

TRANSPOSE OR PIVOT

pivot or transpose?

Transpose & Pivot Order Problem