Quickly Transform Data

Click here to go to the “Quickly Transform Data” dashboard.

This project shows how quickly data from a non – tabular Excel file can be be cleaned up and transformed into a Power BI dashboard. The steps are:

1. Get data and edit.
2. Delete two steps and go to Navigation step.
3. Transpose: dates are rows and categories and cities are columns.
4. Promote headers, Add column heading for Cities and Categories.
5. Fill down on Cities.
6. Select first two columns, Cities and Categories and pivot other columns.
7. Rename columns.
8. Change types.

These actions are quickly done using the UI commands. However this can also be written in “M” code as shown by the Advanced Editor.

let
Source = Excel.Workbook(File.Contents(“C:\Users\nat_c\OneDrive\Desktop\Video data.xlsx”), null, true),
Sheet1_Sheet = Source{[Item=”Sheet1″,Kind=”Sheet”]}[Data],
#”Transposed Table” = Table.Transpose(Sheet1_Sheet),
#”Promoted Headers” = Table.PromoteHeaders(#”Transposed Table”, [PromoteAllScalars=true]),
#”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“Column1”, type text}, {“Column2”, type text}, {“2002”, Int64.Type}, {“2003”, Int64.Type}, {“2004”, Int64.Type}, {“2005”, Int64.Type}, {“2006”, Int64.Type}, {“2007”, Int64.Type}, {“2008”, Int64.Type}, {“2009”, Int64.Type}, {“2010”, Int64.Type}, {“2011”, Int64.Type}, {“2012”, Int64.Type}, {“2013”, Int64.Type}}),
#”Filled Down” = Table.FillDown(#”Changed Type”,{“Column1”}),
#”Renamed Columns” = Table.RenameColumns(#”Filled Down”,{{“Column1”, “Cities”}, {“Column2”, “Categories”}}),
#”Unpivoted Other Columns” = Table.UnpivotOtherColumns(#”Renamed Columns”, {“Cities”, “Categories”}, “Attribute”, “Value”),
#”Renamed Columns1″ = Table.RenameColumns(#”Unpivoted Other Columns”,{{“Attribute”, “Years”}, {“Value”, “Sales Total”}}),
#”Changed Type1″ = Table.TransformColumnTypes(#”Renamed Columns1″,{{“Years”, Int64.Type}})
in
#”Changed Type1″