Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Load & transform scorecard

  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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

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.