Forum Discussion
Anonymous
3 years agoNot applicable
Power Query: Need help in transformation
Hi All, I have the sample data as below. PQ: let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJR8irN0zW0BDNyIAzH0nQQI1YnWsk5vzSvpKjSESgKRWDRxJLU9Pyi...
- 3 years ago
Anonymous , try this code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJR8irN0zW0BDNyIAzH0nQQI1YnWsk5vzSvpKjSESgKRWDRxJLU9PyiSkOggCGYMAIRxiiSICEjMGEMIkxQJEFCxmDCBESYIlvmhMcyAwgJts4A0z5DIwhpDCYxrTQ0hjgUwgbKxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"Jun-19", Int64.Type}, {"Jul-19", Int64.Type}, {"Aug-19", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"", "Category"}}), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Country", each if [#"Jun-19"] = null then [Category] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Country"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [#"Jun-19"] <> null and [#"Jun-19"] <> ""), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Country", "Category"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Country]), "Country", "Value", List.Sum) in #"Pivoted Column"
amitchandak
Super User
3 years agoAnonymous , try this code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJR8irN0zW0BDNyIAzH0nQQI1YnWsk5vzSvpKjSESgKRWDRxJLU9PyiSkOggCGYMAIRxiiSICEjMGEMIkxQJEFCxmDCBESYIlvmhMcyAwgJts4A0z5DIwhpDCYxrTQ0hjgUwgbKxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"Jun-19", Int64.Type}, {"Jul-19", Int64.Type}, {"Aug-19", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"", "Category"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Country", each if [#"Jun-19"] = null then [Category] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Country"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each [#"Jun-19"] <> null and [#"Jun-19"] <> ""),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Country", "Category"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Country]), "Country", "Value", List.Sum)
in
#"Pivoted Column"