reshape

[saritart1]

Hello Everyone,

I am having a problem with my reshape. Currently, I have the data as the picture 1 shows.  I want to have it as Picture 2.

That is, I want it to be uniquely identified by "codigo_participante" and to have multiple columns derivated from "Valor". I will really aprreciate if you could help me. 

I tried using "Pivot" but I just can see the UnPivot Column, no the Pivot one.

Thank you in advance.

Picture 1Picture 2

[Anonymous · Accepted solution]

Hi @saritart1 ,

Pivot operation need at least two fields(category and value), I'd like to suggest you add a custom column to store count of valor, then do pivot on valor and count fields.

Full Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1MFDSUVKK1QGyDQwMkdhGQHaAX6gLMt8xJzkxJyUzMd45MScTImFoZAGUcM1NSsxKTEmMDy5NTc5MRJZCmAHhYzHDwNICbrEx2EEY5sUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Code = _t, Valor = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", Int64.Type}, {"Valor", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Count", each if [Valor] <> "" then 1 else null),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Valor]), "Valor", "Count", List.Sum),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{""})
in
    #"Removed Columns"

Regards,

Xiaoxin Sheng