Forum Discussion
reshape
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
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
1 Reply
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