Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Another Pivot Problem

Update: It appears that there is a row limit for pivot tables.  If I reduce the number of rows from 4,000 to 2,000, then it works.  I will have to filter the table before I can pivot it. Thank you ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

If I transpose the table I get every country as it's own column, which is... different, unfortunately.

camargos88's avatar
camargos88
Community Champion
6 years ago

Anonymous ,

 

Paste this code on the Advanced Editor:

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY+xDoQwDEP/pTND4iSUG+E3EENHJCYOFr7+aCtBpWsnR7L17MyzG13nxvN77GFbw32DaLhF4L25pasFmKL0Zn0rgCgsnAlTvQJsnhqBXAGVZiBVGD70bJj2cK1b8YJp8ULhJrgoqO5GsrDHW/1HVtKqm2czGm4i583LDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Measurement Type" = _t, Country = _t, Year = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Measurement Type", type text}, {"Country", type text}, {"Year", Int64.Type}, {"Value", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "pt-BR"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "pt-BR")[Year]), "Year", "Value")
in
#"Pivoted Column"