ETL Process in Power BI

 We can make a data analysis process easy using the ETL process in Power BI Desktop. 

What is ETL in Power BI Desktop ?

ETL (Extract, Transform and Load) data is a process by which we can change the shape of our dataset. You can perform several operations in Power Query editor. 

During the ETL process, data is extracted from a data source (raw data), then transformed, corrected and then loaded for the next step.

You can do this process manually in Excel but it is time consuming process and at the end of the day, we don't want to do the same process for upcoming new data. It should be done by automation.

If you have not read my last blog on Power BI then please go through the link Blog

We will start the ETL process step by step

A. Extract Data 

B. Transform Data

C. Load Data

A. Extract Data from a data source

To extract data from a data source, click on Get Data. We will extract raw data from Excel sheet and then will connect the sheet with Power BI.

You can extract data from any data source that is available in the list. First you need to select Data source and then click on Connect button.

Then you will see a new window and it is ask to select which file you want to connect. 

You just need to select the file and then click on Open button to connect the file to Power BI desktop.

Note : This is one time process. You don't need to connect the data source every time whenever you will open the Power BI desktop. 

But what will happen when new records added into the sheet? 🤔 Is there any solution? Should I have to connect my data source to Power BI again?🤔

The answer is you don't need to connect the data source again.

Tip : If new records added in your sheet then you don't need to connect the sheet to Power BI. You can click on Refresh button to load the new records into Power BI. 👍

You can find the Refresh button in Ribbon menu in Power BI Desktop.

After connecting the data source to Power BI, you will see the Navigation windows and it will ask to select the table or sheet from your data source. Then it will ask you to whether you want to select load data or transform. We will transform the data first, so click on Transform button.

Here you have done the first part of ETL process. Now coming to the second part. 

B. Transform Data 

This is very important and time consuming task in any ETL process. In this, you have to take care of each column and you have to clean and transform the data into required format. You have to handle many things while transforming or cleaning.

Here we will see some important steps and features of Power Query Editor. 

You will find many sections in Home section of Power Query Editor. 

1. Close : Close & Apply is used to load the transformed data into Power BI Desktop.
2. Query : In Query section, you can refresh the dataset to load get the updated data from data source. You can create/delete a copy of the data.
3. Manage Columns : In this, you can do several operations on columns i.e. select or remove column(s)
4. Reduce Rows : In this, you can remove Top N rows, Bottom N rows, duplicate rows and rows having any error.
5. Transform : You will find several features to transform the data format. You can choose data type of a column. You can replace values of a column.

You will also find several sections in Transform section of Power Query Editor.

1. Table : In this, you can transpose the table. You can do group by and count the rows
2. Any Column : In this, you can fill the missing values with many techniques using Fill option. You can create a pivot table and vice versa.
3. Text Column : Here, you can split a column by different delimiter. 

You can also do many operations on table by selecting the column. You fill find several options there. Select any column and do right click to see the options.

You can rename the column by double click on Column Name. You can move the column by drag. You can change the data type of the selected column with different format. Most of the time Power BI detect the data type and assign it to the column automatically. 

You can also check the Column quality (Error or valid percentage) of each column below the column name. If it is not showing in your pane then go to View section and then enable column quality.

Here, we have completed the basics of Transform Data step. 

C. Load Data 

This is the last step of any ETL process in Power BI. After completing the Transform part you can load the cleaned/transformed data into Power BI for visualization. 

You will find the an option Close & Apply to load the newly shaped and transformed data for visualization. 

Congrats !!!....

Hope you understand How the ETL works in Power BI.

If you have any query or question in your mind then my comment section is always waiting for you. 👇