Pivot in Power Query Help

[jakoob]

Hello,

I'm currently working with some customer satisfaction data that currently looks similar to the following:

Sent Survey ID	Survey Question ID	Answer	Survey ID
1	1	Less than 30 minutes	4
1	2	5	4
1	3	Yes	4
1	4	5	4
2	1	Less than 30 minutes	4
2	2	5	4
2	3	Yes	4
2	4	5	4
3	1	Less than 30 minutes	4
3	2	5	4
3	3	Yes	4
3	4	5	4

In the above table, each value in the column "Sent Survey ID" represents a single customer survey. The issue is that currently, a single survey is split across multiple rows in the table. I need to be able to perform aggregations, such as averages for each "Survey Question ID" question and answer (Example: Average score of 5 for Question ID 2). Ideally, my goal is to have a table that looks like the following below with a single survey being included on a single row:

Sent Survey ID	Question 1 Answer	Question 2 Answer	Question 3 Answer	Question 4 Answer	Survey ID
1	Less than 30 minutes	5	Yes	5	4
2	Less than 30 minutes	5	Yes	5	4
3	Less than 30 minutes	5	Yes	5	4

My thought was to Pivot the "Survey Question ID" column from the first table above in Power Query, with the attributes set to the "Answer" column also from the first table. However when I do so, I get a table that looks like the following:

Sent Survey ID	Question 1 Answer	Question 2 Answer	Question 3 Answer	Question 4 Answer	Survey ID
1	Less than 30 minutes	null	null	null	4
1	null	5	null	null	4
1	null	null	Yes	null	4
1	null	null	null	5	4
2	Less than 30 minutes	null	null	null	4
2	null	5	null	null	4
2	null	null	Yes	null	4
2	null	null	null	5	4
3	Less than 30 minutes	null	null	null	4
3	null	5	null	null	4
3	null	null	Yes	null	4
3	null	null	null	5	4

While this above table would meet my requirements, which is to be able to perform aggregation on each answer column, its not optimized best with all the excess rows that contain null values in the question columns.

Is there a better way to accomplish what I am trying to achieve?

Thank you in advance!

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Prefix" = Table.TransformColumns(Source, {{"Survey Question ID", each "Question " & Text.From(_, "en-IN"), type text}}),
    #"Added Suffix" = Table.TransformColumns(#"Added Prefix", {{"Survey Question ID", each _ & " Answer", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Added Suffix", List.Distinct(#"Added Suffix"[#"Survey Question ID"]), "Survey Question ID", "Answer")
in
    #"Pivoted Column"

Hope this helps.

[ryan_mayu]

@jakoob 

you can try to select the question column and pivot

then you can change the column name

pls see the attachment below