Forum Discussion
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,
KellyDid I answer your question? Mark my reply as a solution!
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!
Thanks all three of you for you replies with solutions (not sure how to mark all 3 as solutions).
ronrsnfld , thank you so much for commenting the sections with explanations and also for a solution that appears to be replicable largely using the UI - both are so helpful for a beginner like me.
In the step to "Add custom column with desired column headers then remove Attribute column"...
"Q." & Number.ToText([#"Question No."]) & "#(lf)" & List.Range({"Answer","Score","Rating"}, List.PositionOf({"Answer","Answer Score","Answer Band"},[Attribute]),1){0}I would have tried to add a series of conditional column like...
"Q."&[#"Question No."]&" "&[Attribute]...then googled the resulting error (We cannot apply operator & to types Text and Number.), ...and perhaps adjusted to
"Q."&Number.ToText([#"Question No."])&" "&[Attribute]That seems to have produced a similar result to the list aproach. I guess you're also using that opportunity to rename "Band" to "Rating" so perhaps one less step. Is that why you chose it or am I missing something else?
I'd never seen the modulo function so googled it and found in this video that it basically means "How many rows until the data repeats itself"
Then sticking with UI-based approach, am I right in thinking you had only the UserID column selected before clicking Transform> Pivot Column?
lkey , Thank you for including the spreadsheet showing your solution, which pivots 3 times and then groups. I'm sure I undersstand why "Max" works as the operation. Is there any easy explanation?
BA_Pete, Thanks for your solution, which also pivots 3 times and then groups. I can't work out what settings you selected in the group dialogue to get the last step it to work.
Hi k2s2 ,
The last group step was handwritten rather than via the GUI dialog.
As you say, it probably wasn't the best way to achieve this for a beginner to follow, but does hopefully give an example of how to do custom groupings that aren't included in the GUI for those, like yourself, that seem eager to learn.
Also, to answer your question to Kelly about using the MAX aggregator in the group step, this is because NULL has no value, so the only non-blank cell in each column per group will always be the MAX value. It's a much tidier way of grouping out the nulls than my, admittedly, protracted list-manipulation technique.
Pete
I just wrote the Custom column as a formula in the Add Custom Column dialog box. I used the "List" because I was renaming both the 2nd and 3rd columns, and it seemed more efficient than a series of "if" statements.
When you pivot a table, the column you "select" will be the one that will become the headers of your pivot table columns. If you double click on that step in Applied Steps, you should be able to see what was selected for which entry (it'll be below the first line in the dialog. Below that will be a selection for "Values" and that is what will populate the table. Finally, under Advanced, you would select "don't aggregate"
