Power Query: Need help in transformation

[Anonymous]

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.

[BiNavPete]

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.

PBIX Here

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 · Accepted solution]

@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"