Solved: Re: Pivoting/transposing problem

k2s2 · ‎10-12-2021

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	None
1200	General	2	Have you done Y?	No	3	Low
1200	General	3	Which do you prefer?	This	10	High
1200	Part 1	4	Which do you prefer?	That	6	Medium
1200	Part 1	5	What is this?	Something	4	Medium
1200	Part 2	6	Which do you prefer?	Else	0	None
1200	Part 2	7	Have you done X?	Yes	4	Medium
AXLF	General	1	Have you done X?	Yes	1	None
AXLF	General	2	Have you done Y?	Yes	2	Low
AXLF	General	3	Which do you prefer?	This	10	High
AXLF	Part 1	4	Which do you prefer?	This	8	Medium
AXLF	Part 1	5	What is this?	That	4	Medium
AXLF	Part 2	6	Which do you prefer?	That	3	Low
AXLF	Part 2	7	Have you done X?	Something	3	Low
OO567	General	1	Have you done X?	Yes	1	None
OO567	General	2	Have you done Y?	Else	0	None
OO567	General	3	Which do you prefer?	No	8	Medium
OO567	Part 1	4	Which do you prefer?	No	2	Low
OO567	Part 1	5	What is this?	Something	4	Medium
OO567	Part 2	6	Which do you prefer?	That	8	Medium
OO567	Part 2	7	Have you done X?	That	6	Medium

..into one row per user, like this:

UserID

Q. 1 answer

Q. 2 answer

Q. 3 answer

Q. 4 answer

Q. 5 answer

Q. 6 answer

Q. 7 answer

Q. 1 Score

Q. 2 Score

Q. 3 Score

Q. 4 Score

Q. 5 Score

Q. 6 Score

Q. 7 Score

Q. 1 Rating

Q. 2 Rating

Q. 3 Rating

Q. 4 Rating

Q. 5 Rating

Q. 6 Rating

Q. 7 Rating

1200

Yes

No

This

That

Something

Else

Yes

1

3

10

6

4

0

4

None

Low

High

Medium

None

Medium

AXLF

Yes

This

That

Something

1

2

10

8

4

3

None

Low

High

Medium

Low

OO567

Yes

Else

No

Something

That

1

0

8

2

4

8

6

None

Medium

Low

Medium

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/)

ronrsnfld · ‎10-13-2021

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 · ‎10-14-2021

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!

View solution in original post

v-kelly-msft · ‎10-14-2021

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!

k2s2 · ‎10-14-2021

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.

ronrsnfld · ‎10-14-2021

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"

BA_Pete · ‎10-14-2021

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

ronrsnfld · ‎10-13-2021

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"

k2s2 · ‎10-13-2021

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

BA_Pete · ‎10-13-2021

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

k2s2 · ‎10-12-2021

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 · ‎10-12-2021

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:

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

Pete

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

k2s2 · ‎10-12-2021

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.

BA_Pete · ‎10-12-2021

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:

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

Pete

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!