From the course: Power BI Data Methods

The extract, transform, load (ETL) framework - Power BI Tutorial

From the course: Power BI Data Methods

Start my 1-month free trial Buy for my team

The extract, transform, load (ETL) framework

“

- [Instructor] To use Power Query effectively, you'll want to be familiar with the ETL framework. It's a must-know for working with any type of data intake framework, including working with Power BI. The acronym ETL stands for Extract, Transform, and Load. It helps us create data tables in tabular form that we can then use in our Power BI models. Extracting the data enables us to connect to a variety of built-in and other connectors directly within Power Query. Power BI has over 100 data connectors available directly in the application, including connecting to both cloud and on-premises databases, web connections, and flat files stored either in a computer or online. Transforming the data enables us to take this initial data connection and transform it into a tabular data format that we can use in Power BI. With these steps, there are different types of transformations we can take. This includes cleaning the data to remove unnecessary spaces, correcting spelling issues, and choosing the correct data types. Data enrichment involves adding columns to the queries or filtering our data, for example, Data integration includes changing the overall structure of our data by doing transformations, like transposing, pivoting, or grouping the data table. In the final ETL step, we load the shaped and transformed data tables into the Power BI interface. This involves choosing the type of connection we want, like importing the data or using a direct connection, and connecting related tables together in a multi-dimensional model for our Power BI semantic layer. From there, we can leverage capabilities such as creating DAX measures and data visualizations. The entire ETL process does not and cannot change or affect anything in the original data source that we connect to because we're essentially making a copy of the data to import. So don't worry that you're going to change the underlying data by doing something in Power BI. It's in safe hands. ETL isn't a one-and-done thing either. You create a strong framework once and then you can use it over and over again for different projects or updates. So it's important to think deeply about the thing you're trying to do before actually diving headfirst into the data itself.

Contents