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
Here is the data for the starting table:
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.2 | Column2 | Q | Q | Category | Question | |
Category | General | Q01 | Q01 | General | Question Text 1 | |
Question | Question Text 1 | Q01 | Q02 | General | Question Text 2 | |
Category | General | Q02 | Q03 | Key | Question Text 3 | |
Question | Question Text 2 | Q02 | Q04 | Key | Question Text 4 | |
Category | Key | Q03 | ||||
Question | Question Text 3 | Q03 | ||||
Category | Key | Q04 | ||||
Question | Question Text 4 | Q04 | ||||
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"