Forum Discussion
Unpivoting data from Survey Monkey
Hi amied ,
I have recreated the scenario using the sample data provided can you confirm is this what you are expecting ?
input :
op :
:let Source = Table, Unpivoted = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"), AddType = Table.AddColumn(Unpivoted, "Type", each if Text.Contains([Attribute], "comment") then "Comment" else "Answer"), AddQuestion = Table.AddColumn(AddType, "Question", each Text.BeforeDelimiter([Attribute], " ")), RemoveAttr = Table.RemoveColumns(AddQuestion, {"Attribute"}), Pivoted = Table.Pivot( RemoveAttr, List.Distinct(RemoveAttr[Type]), "Type", "Value" ) in Pivoted
Thanks .
If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster
Super UserHi amied
Can you please try the below steps to get your requirement ?
1. select id column and then upivot the other column
2 Separate the attribution and create two custome column in power query editory using below two m code
Question
a. if Text.Contains([Attribute], "comment")
then Text.BeforeDelimiter([Attribute], " comment")
else [Attribute]
Type
b. if Text.Contains([Attribute], "comment")
then "Comment"
else "Answer"
3. Now Pivot
select Type column and click Pivot column
Values column = Value
Advanced = Don't Agggregate
4. Now select the Answer column and go to transform -> fill ->Down
5. Now Select the Comment column and remove the null
If this answers your questions, kindly accept it as a solution and give kudos.
This is very close, but I ran into an issue with the last 2 steps.
4. Fill down - I noticed that this can result in the wrong answer being attributed to the comment, as the order of the rows is such that the row above the comment could be the answer from the other respondent. Even when I applied a sort order to ensure it was in the right order to fill down, somehow in the process of filling down, it reordered and some cells would end up getting the value from the previous question (note that I am using a larger dataset to test this, so you might not see it with the example data, I'm not sure).
5. Remove nulls - I need for it to be possible for there to be no comment, so I have skipped this step or I will lose data.
So with the first 3 steps, I end up with:
ID Question Answer Comment 2 1 Agree 1 1 Strongly agree 1 1 Test comment 1a 2 1 Test comment 1b 1 2 Disagree 2 2 Neutral 1 2 Test comment 2a 2 2 Test comment 2b So close!
Even if I were able to guarantee the order of the rows was Answer, Comment for each ID when it fills down, I'd still need a way to remove the extra comment rows without just removing all nulls.
Any ideas?
