Forum Discussion
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("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])
in
Source
I need to have the final table as below
Please help.
Thanks,
Mohan V.
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"
2 Replies
- BiNavPete
Resolver III
Hi Anonymous
Check out PQ piece in PQ below.
The tricky bit here is identification of the country rows. Once that is done an unpivot and repivot creates the desired table.
The sample PBIX works by identifying the country row where Jun-19 is null. Plainly this is not ideal and you will need a more solid way to identify the country row.Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Happy to help!!
Pete
Linked In: https://www.linkedin.com/in/pete-smith-955b73181
Web: https://binavigation.com - amitchandak
Super User
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"
