Forum Discussion
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 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!
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:
5 Replies
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:
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.
