Power Query Group by column

kcmuppet

Active Member
Joined
Nov 2, 2005
Messages
439
Office Version
  1. 365
Platform
  1. Windows
I'm struggling with what I think must be a simple thing for power query:

I'm trying to transform data as follows, but can't work out whether I should be using Group by or pivot

From this:To this:
Column1.2Column2QQCategoryQuestion
CategoryGeneralQ01Q01GeneralQuestion Text 1
QuestionQuestion Text 1Q01Q02GeneralQuestion Text 2
CategoryGeneralQ02Q03KeyQuestion Text 3
QuestionQuestion Text 2Q02Q04KeyQuestion Text 4
CategoryKeyQ03
QuestionQuestion Text 3Q03
CategoryKeyQ04
QuestionQuestion Text 4Q04



Here is the data for the starting table:

Power Query:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sSU3PL6pU0lFyT81LLUrMAbICDQyVYnWilQJLU4tLMvPzQEJQpkJIakWJgiGSIhwmGOE3wQhJEZIJ3qmVYAlj/LqNkRRh0W2CX7cJTFEsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1.2 = _t, Column2 = _t, Q = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1.2", type text}, {"Column2", type text}, {"Q", type text}})
in
    #"Changed Type"
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
It would be a pivot on the first column:

Power Query:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sSU3PL6pU0lFyT81LLUrMAbICDQyVYnWilQJLU4tLMvPzQEJQpkJIakWJgiGSIhwmGOE3wQhJEZIJ3qmVYAlj/LqNkRRh0W2CX7cJTFEsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1.2 = _t, Column2 = _t, Q = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1.2", type text}, {"Column2", type text}, {"Q", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Column1.2]), "Column1.2", "Column2")
in
    #"Pivoted Column"
 
Upvote 1
Solution
It would be a pivot on the first column:

Thank you.

(I was getting stuck with which column to select before clicking on Pivot Column in the ribbon. In case it helps anyone else here, it seems to be:

First select (click on) the the column containing the names that you want to turn into columns - in my case Column1.2
Then
Click Transform > Pivot Columns
Make sure the Values Column is the one that you want to keep as one per row
Click Advanced options
Select Don't Aggregate


1680082432443.png
 
Upvote 0
You don't really need to select the relevant column first since you can just select it in the dropdown in the dialog. I should have mentioned the 'don't aggregate' part though!
 
Upvote 0
You don't really need to select the relevant column first since you can just select it in the dropdown in the dialog. I should have mentioned the 'don't aggregate' part though!

I found it didn't produce expected results unless I selected the column first.
For example If select this column:

1680084687515.png


and then still select correct pivot like this:

1680084743960.png

... I get this:
1680084777352.png
 
Upvote 0
Sorry - I must still be half asleep. You do have to select the pivot column first since the dropdown is for the values column.
 
Upvote 0

Forum statistics

Threads
1,223,362
Messages
6,171,640
Members
452,413
Latest member
N3edHelp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top