Load & transform scorecard

[Anonymous]

Hi all,

I have a .xlsx scorecard I want to load in Power BI. The excel is in the below structure: . 

I would like to load in Power BI and transfor to below structure, with months arranged in a column. The reason for this is that if not months in columns, then I would see each month as column in Fields pane in BI. How can I do this efficiently in BI so that I can refresh monthly ? 

Thanks! 

[Ashish_Mathur]

Hi,

With the help of the following M code, i have been able to transform your data (as show below)

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Jun-18", Int64.Type}, {"Jul-18", Int64.Type}, {"Aug-18", Int64.Type}}),
    #"Uppercased Text" = Table.TransformColumns(#"Changed Type",{{"Column1", Text.Upper, type text}}),
    #"Added Custom" = Table.AddColumn(#"Uppercased Text", "Custom", each if Text.Start([Column1],9) = "INDICATOR" then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each if [Column1]<>[Custom] then "Keep" else "Ignore"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom.1] = "Keep")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Column1", "Jun-18", "Jul-18", "Aug-18"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom", "Indicators"}, {"Column1", "Countries"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Indicators", "Countries"}, "Attribute", "Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Renamed Columns1", {{"Date", type date}}, "en-IN")
in
    #"Changed Type with Locale"

You can now drag the fields to your visual.

[Anonymous]

This can be done in Power Query with a few steps. Can you post some sample data?

[Anonymous]

Sorry, I don't now how I can post data? Thanks,

[Anonymous · Accepted solution]

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:

https://1drv.ms/u/s!Amqd8ArUSwDSz3grLk4FXOof3mG1

[hnguy71]

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