Pivot some columns

[ErinKlomp]

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!

[dufoq3 · Accepted solution]

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"

[Joe_Barry]

Hi @ErinKlomp 

Try using a Matrix visual

Put the first two columns in the row section and the last column in column section and the 3rd column in the value section.

That should do it for you.

Hope this helps

Joe