Forum Discussion
Pivot in Power Query Help
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!
2 Replies
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.
