Aim: connect, shape and transform raw source data with Power Query Editor

Dimensional modelling is already done on the raw data, so the raw files are available in Fact & Lookup format.

About ETL

The project provides 10 raw .csv files, which contain information about transactions, returns, products, customers, and sales territories. The loaded data is being prepared for use in the modelling and analysis phases of the project.

<aside> <img src="/icons/report_orange.svg" alt="/icons/report_orange.svg" width="40px" />

Note:

There are transformations that can be made after data is loaded into the back-end of Power BI, in the analysis phase for example. This process is known as ELT. The ETL process being currently discussed is specifically focused on steps that prepares the data directly entering the data model. ETL is typically more focused on data preparation/cleansing.

</aside>

Power Query automatically produces M-code as ETL steps are implemented, however, knowing this languages deeply isn’t essential to work with Power BI.

ETL Steps

The 10 .csv files are connected in a sequence, with data pre-processing being done after establishing each connection. It is general procedure to appropriately name a table and check data types assigned to each column while establishing a connection.

<aside> <img src="/icons/report_orange.svg" alt="/icons/report_orange.svg" width="40px" />

Note:

• The Applied Steps section of the Power Query Editor contains all transformation steps defined by the user. Whenever the connection with the data source is refreshed, Power BI will run through all these steps by itself as an automated ETL process.
• Each individual applied step can be renamed by the user for increased clarity.
• To visually see what changes were made with the current step, a previous step can be clicked. </aside>

The connection is established using the Import Mode, where tables are stored within Power BI’s memory. The pre-processing steps applied are listed below:

1. Added the Territory Lookup Table
2. Added the Product Lookup Table
   1. Changed data type of ‘ProductCost’ and ‘ProductPrice’ to currency
   2. Removed ‘ProductSize’ because inconsistent data type is confusing
   3. Extracted ‘SKU Type’ from ‘Product SKU’
   4. Replaced “0” in ‘Product Style’ with “NA”
   5. Added ‘Discount Price’ (90% of original price)
3. Added the Product Category Lookup Table