cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
k2s2
Frequent Visitor

Pivoting/transposing problem

Hello

I'm struggling to get power query to transpose / pivot this:

UserIDCatergoryQuestion No.QuestionAnswerAnswer ScoreAnswer Band
1200General1Have you done X?Yes1None
1200General2Have you done Y?No3Low
1200General3Which do you prefer?This10High
1200Part 14Which do you prefer?That6Medium
1200Part 15What is this?Something4Medium
1200Part 26Which do you prefer?Else0None
1200Part 27Have you done X?Yes4Medium
AXLFGeneral1Have you done X?Yes1None
AXLFGeneral2Have you done Y?Yes2Low
AXLFGeneral3Which do you prefer?This10High
AXLFPart 14Which do you prefer?This8Medium
AXLFPart 15What is this?That4Medium
AXLFPart 26Which do you prefer?That3Low
AXLFPart 27Have you done X?Something3Low
OO567General1Have you done X?Yes1None
OO567General2Have you done Y?Else0None
OO567General3Which do you prefer?No8Medium
OO567Part 14Which do you prefer?No2Low
OO567Part 15What is this?Something4Medium
OO567Part 26Which do you prefer?That8Medium
OO567Part 27Have you done X?That6Medium

 

..into one row per user, like this:

 

UserIDQ. 1 answerQ. 2 answerQ. 3 answerQ. 4 answerQ. 5 answerQ. 6 answerQ. 7 answerQ. 1 ScoreQ. 2 ScoreQ. 3 ScoreQ. 4 ScoreQ. 5 ScoreQ. 6 ScoreQ. 7 ScoreQ. 1 RatingQ. 2 RatingQ. 3 RatingQ. 4 RatingQ. 5 RatingQ. 6 RatingQ. 7 Rating
1200YesNoThisThatSomethingElseYes13106404NoneLowHighMediumMediumNoneMedium
AXLFYesYesThisThisThatThatSomething12108433NoneLowHighMediumMediumLowLow
OO567YesElseNoNoSomethingThatThat1082486NoneNoneMediumLowMediumMediumMedium

 

Can anybody help?
In case it helps, here is the source dummy data in M:

 

 

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}})
in
    #"Changed Type"

 

 

(Cross-posted here: https://www.mrexcel.com/board/threads/pivoting-transposing-problem.1184339/)

2 ACCEPTED SOLUTIONS
ronrsnfld
Resolver IV
Resolver IV

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"

 

View solution in original post

v-kelly-msft
Community Support
Community Support

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:

vkellymsft_0-1634196416616.png

Check my sample attached.

 

Best Regards,
Kelly

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

View solution in original post

11 REPLIES 11
v-kelly-msft
Community Support
Community Support

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:

vkellymsft_0-1634196416616.png

Check my sample attached.

 

Best Regards,
Kelly

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

View solution in original post

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.  

 

k2s2_0-1634202633520.png

 

 

 

 

 

 

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"

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

ronrsnfld
Resolver IV
Resolver IV

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"

 

View solution in original post

k2s2
Frequent Visitor

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

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

k2s2
Frequent Visitor

Hi @BA_Pete ,

 

Sorry if I confused you.  I'm trying to do this in Power Query in Excel so that I can subsequently merge with other data about the User, and then load the table to a sheet to allow other users to create pivots from it.  

 

What I'm looking for is help with the way to do the transfornation in Power Query.  The transformation will be part of regular monthly reporting cycle, which is one of the reasons I'm wanting it to be done in Power Query

 

BA_Pete
Super User
Super User

Hi @k2s2 ,

 

The real question here is why you want to do this?

Your original data is in the optimum structure for reporting within Power BI.

If you want your reports to display on a user-row basis, then just grab a table or matrix visual, drag in the user field, then each of your question fields etc. and it will display to the end user as you wish.

You shouldn't disrupt your source data structure to make it look like how you want to display it, that's really not the 'Power' in Power BI.

 

Here's your data in it's original structure put into a matrix visual:

BA_Pete_0-1634024846208.png

 

Also: kudoed your post for providing M code for example data - perfect delivery!

 

Pete

k2s2
Frequent Visitor

Hi @BA_Pete ,

 

Thanks for your reply.  I'm using Power Query in Excel rather than Power BI, so I don't really follow the suggestion you kindly made ("...grab a table or matrix visual, drag in the user field...", etc.)

 


@BA_Pete wrote:

The real question here is why you want to do this?

 

I have a bunch of other data (e.g. demographic, status, etc.) associated with the UserID, that I want to be able to set up reporting for using pivot tables (easier for my users).

 

I tried using the menus in Power Query, but trial and error didn't get me very far.   

 

Hi @k2s2 ,

 

My bad, sorry. Just used to people asking about Power BI in the Power BI forum.

The matrix visual is just a Power BI pivot table so, to get what you need in Excel, we just need to do a couple of extra steps to get the text to display in an Excel pivot table.

 

Follow this guide here to add your PQ query to the data model in your Excel file and create measures to force text-display of your values:

https://www.mrexcel.com/excel-tips/pivot-table-with-text-in-values-area/ 

 

I've done it for a couple of your output values in a couple of minutes and got this output so far, which looks exactly like the PBI matrix output:

BA_Pete_0-1634028132606.png

 

Obviously you would name your measures something a bit more intuitive for your end users to use, but the principle is sound.

 

Pete

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors