Forum Discussion
Solved
Pivot some columns
Hi all, another question from a first time user.
I finally have all my data, but now as a final step, I want to pivot some of my colums. I have 4 colums, where I want to pivot two of the colums (and sort the rest accordingly)
| evaluatieperiodeid | specialisme | gegevenantwoordwaarde | vraagcode |
| 59141 | AA | 1 | resp_1_Alg1 |
| 59141 | AA | 3 | resp_1_Alg2 |
| 59141 | AA | 2 | resp1_Alg3 |
| 59142 | BB | 4 | resp_1_Alg1 |
| 59142 | BB | 2 | resp_1_Alg2 |
| 59142 | BB | 0 | resp1_Alg3 |
I want to achieve this:
| evaluatieperiodeid | specialisme | resp_1_Alg1 | resp_1_Alg2 | resp1_Alg3 |
| 59141 | AA | 1 | 3 | 2 |
| 59142 | BB | 4 | 2 | 0 |
There are about 800 different vraagcodes.
Hoping someone can help me achieve this!
Hi ErinKlomp, if you want Power Query solution:
Result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrU0NDFU0lFydAQSIEZRanFBvGG8Y066oVKsDpoCYxQFRpgKjKAKwPLGcHmQsJMTkDDBYQNcgREOG+AKDNBsiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [evaluatieperiodeid = _t, specialisme = _t, gegevenantwoordwaarde = _t, vraagcode = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"gegevenantwoordwaarde", Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[vraagcode]), "vraagcode", "gegevenantwoordwaarde") in #"Pivoted Column"
2 Replies
Hi ErinKlomp, if you want Power Query solution:
Result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrU0NDFU0lFydAQSIEZRanFBvGG8Y066oVKsDpoCYxQFRpgKjKAKwPLGcHmQsJMTkDDBYQNcgREOG+AKDNBsiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [evaluatieperiodeid = _t, specialisme = _t, gegevenantwoordwaarde = _t, vraagcode = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"gegevenantwoordwaarde", Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[vraagcode]), "vraagcode", "gegevenantwoordwaarde") in #"Pivoted Column"
