Transform raw data with messed up structure

[ksab23]

hello all, 

I presume there will be no solution for this but before I endeavor on a tedious manual task I have to ask. I have a survey dataset that is simply put terrible. Instead of having one survey with all answers in one row, questions being column headers and values being ratings (image below)

I have these, questions are broken down into values and for each question and user, I have a row with a response, etc. which makes the data unusable in visuals (image below)

Is there any way I can transform questions into columns and attach ratings as values per each user survey (like the first image, and table below)? Data will be updated on a certain cadence, so I really want to automate this if possible...

User	Question 1	Question 2	Question 3
X	Agree/5	Strongly Agree/4	free text
Y	Disagree/1	Neutral/3	free text

Thanks a lot!

[Peter_Beck · Accepted solution]

Hi -

It looks like you start with this:

...and want to end up with this...

... is that the case?

If so, you should be able to use "Table.Pivot" to do this, I have attached an example below:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLVIwVNJRCi4tKkutBDMDS1OLSzLz88Acx/Si1FSlWB0CSo2AHJfM4kRU1UZ4DPbTdySs0AjsApCRsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UserID = _t, Survey = _t, QuestionText = _t, QuestionResponse = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"UserID", type text}, {"Survey", type text}, {"QuestionText", type text}, {"QuestionResponse", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[QuestionText]), "QuestionText", "QuestionResponse")
in
#"Pivoted Column"

This is done by selecting the "QuestionText" column, selecting "Pivot Column" under "Transform", and selecting "QuestionResponse" as the "Values" column. Be sure to select "Don't Aggregate" under the "Advanced" option, because you are not aggregating numbers. "QuestionText" becomse columns, with the appropriate value from "QuestionResponse" in each column.

Hope this helps!

Peter