Forum Discussion
Solved
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...
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
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