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

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Sorry, I don't now how I can post data? Thanks,
Anonymous's avatar
Anonymous
Not applicable
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

  • hnguy71's avatar
    hnguy71
    Super User
    7 years ago

    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