Forum Discussion
Load & transform scorecard
Generally easier to add via dropbox or onedrive. But was able to get the data out from the screenshot.
In the attached pbix file take a look at the applied steps in power query. The first half is just the steps i needed to make the data usuable from when I imported. But after that.
1. Create a custom column to look for indicator in the 1st column. If not there, then give null. Then we can just fill down
then can merge that new column with the 1st column
Selecting that column we then can unpivot other columns
Now split that merged column
Then just setting data types, names, ect. I added a few columns so we can sort by date and country in our final table
Here's the pbix file:
Generally easier to add via dropbox or onedrive. But was able to get the data out from the screenshot.
In the attached pbix file take a look at the applied steps in power query. The first half is just the steps i needed to make the data usuable from when I imported. But after that.
1. Create a custom column to look for indicator in the 1st column. If not there, then give null. Then we can just fill down
then can merge that new column with the 1st column
Selecting that column we then can unpivot other columns
Now split that merged column
Then just setting data types, names, ect. I added a few columns so we can sort by date and country in our final table
Here's the pbix file:
I have an alternative solution to Nick's which closely resembles your desired output.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY+xCoQwEET/JbVCdjcWVx5X6S+EFHIecnAoiBb+ve6OTdATwmOYLV4mRucK1yxDSQ8LP4Tn0mtIRXT10H3f7TxOtPfH0/7Vzp9+nFatycAKyY5asUEUITtqJYagqHId3+g8aEJ/NhKDYjxLSfBV5JB75b+XPWiDLryMqdh6OZaNyPs9bQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Let's Promote Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Initial Change Type" = Table.TransformColumnTypes(#"Let's Promote Headers",{{"", type text}, {"Jun-19", Int64.Type}, {"Jul-19", Int64.Type}, {"Aug-19", Int64.Type}}),
#"Extracted Indicators" = Table.AddColumn(#"Initial Change Type", "Indicators", each if Text.Contains([#""], "Indicator") then [#""] else null),
#"Let's Fill-Down the nulls" = Table.FillDown(#"Extracted Indicators",{"Indicators"}),
#"Filtered Out Original Indicators" = Table.SelectRows(#"Let's Fill-Down the nulls", each Text.Contains([#""], "Category")),
#"Unpivot Other Columns" = Table.UnpivotOtherColumns(#"Filtered Out Original Indicators", {"", "Indicators"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivot Other Columns",{{"", "Country"}, {"Attribute", "Date"}, {"Value", "Amount"}}),
#"Pivot Indicators Based On Amount" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Indicators]), "Indicators", "Amount", List.Sum)
in
#"Pivot Indicators Based On Amount"To test or sample, you may go to:
GET DATA > BLANK QUERY > ADVANCED EDITOR > REPLACE DEFAULT CODE WITH CODE ABOVE