Power BI Extract Transform Load (ETL): A Comprehensive Guide

As the popularity of databases increased in the 1970s, Extract, Transform, and Load (ETL) was introduced as a process of integrating and loading data for computation. However, at the end of the 1990s, Microsoft standardized the ETL idea with a program called Data Transformation Service (DTS). Since then, ETL has been the dominant method for processing data, later used for analysis in Business Intelligence (BI) Applications. 

Microsoft’s Power BI is one of the leading Data Visualization BI Tools that uses ETL in Power BI to process data. This article is a comprehensive guide covering Power BI ETL. Power BI extract transform load ideas around the Power BI data transformation engine – Power Query, and some useful features you can benefit from in Power BI. Read on to get started with the Power BI ETL process.

What Is Power BI?

Microsoft Power BI is one of the most powerful Business Intelligence Tools in the data industry. It was first created by Ron George in 2010 and named Project Crescent. Later, Microsoft renamed it Power BI. In 2015, Power BI was made public for the first time. 

Over the years, Microsoft has introduced a slew of features to Power BI, that include natural language processing techniques, marvelous dashboards, exciting graphs and dashboards to work upon, Power BI Extract Transform Load (ETL) capabilities, integration with other Microsoft Apps, and robust security standards. 

More information on Power BI parts can be found in this guide here – Understanding Microsoft Power BI: A Comprehensive Guide. To discover the different types and options for creating Power BI visuals, we have another helpful guide at your disposal – Ultimate Guide on Power BI Visuals: 20+ Types to Use in 2022.

What Is ETL?

The Extract, Transform, and Load (ETL) is a process that extracts, transforms, and loads data from multiple sources to a data warehouse or a unified data repository. This centrally collected data, in the repository, makes it easier to analyze further and handle the data. It also acts as a single point for accurate and consistent data requirements of teams and businesses.

What Is the Need for Power BI Extract Transform Load?

Having been briefed on the elementary steps of the Power BI Extract Transform Load mechanism, let’s look at the reasons you need Power BI ETL. Because we are simply making a copy of the data, the whole Power BI Extract Transform Load process does not and cannot modify or influence anything in the original data source.

However, when declining performance becomes a problem, we can examine the query to discover what is causing the slowdown and what modifications should be made. But, we must first ensure that the query is correct before working on increasing query performance.

The Power Query available in Power BI uses Power BI Extract Transform Load (ETL) to process data and increase performance. In this article, we will look at how Power BI’s Power Query handles data processing using the Power BI Extract Transform Load mechanism.

Understanding Power Query Power BI Extract Transform Load Mechanism

Power BI’s Power Query is a tool for data transformation and preparation. It comes with an intuitive Graphical User Interface (GUI) and a Power Query Editor. The Power Query Power BI Extract Transform Load Interface enables you to obtain data from multiple sources, while the Power Query Editor allows you to perform transformations on these data. 

Data Acquisition

According to a 2017 IDC survey, business users spend nearly 80 percent of their time preparing data, which delays the analysis and decision-making processes. However, Power Query Power BI Extract Transform Load mechanism can address such issues with its wide range of available features.

Power Query allows users to define data transformations against a subset of the complete dataset, quickly filtering and converting the data to a manageable size. Using the process (query), Power Query can easily be refreshed in the future to receive the most recent data. In addition, Power Query provides connectivity to a wide range of data sources of all sizes and shapes. 

Transformation Tool 

The transformation tool in the Power Query contains a number of prebuilt transformation functions that can be accessed using the Power Query Editor’s Graphical Interface. Power Query Power BI Extract Transform Load interface cottages a broad range of transformations ranging from simple ones like column elimination, row filtering, first row as a table header, or advanced ones like merge, append, group by, pivot, and unpivot.

Dataflows

Power BI Extract Transform Load (ETL) Dataflow is a cloud-based platform that helps users prepare data. Users can also utilize Power BI Dataflows to ingest, convert, and load data into Microsoft Dataverse environments, Power BI workspaces, or an Azure Data Lake Storage account.

Using Dataflows, users can receive and manipulate the data based on their requirements. Instead of sending the result to Power BI or Excel, users can save the data in alternative storage solutions like Dataverse or Azure Data Lake Storage. 

Power Query, a unified data connectivity and preparation experience found in many Microsoft applications, including Excel and Power BI, is used to create dataflows. Customers can plan dataflows to run on demand or on a set timetable, ensuring that data is constantly updated.

M Formula Language

Microsoft Power Query is a powerful “get data” tool that comes with a slew of functions. One of Power Query’s core features is filtering and aggregating the data from one or more available data sources. 

The Power Query Formula Language (informally known as “M”) represents any type of data mashup. It is Power Query’s data transformation language, and everything that happens in the query is written in M. 

You can utilize the Advanced Editor to access the query’s script (given below) and edit it as needed to execute advanced transformations with the Power Query tool.

let
    Source = Exchange.Contents("xyz@contoso.com"),
    Mail1 = Source{[Name="Mail"]}[Data],
    #"Expanded Sender" = Table.ExpandRecordColumn(Mail1, "Sender", {"Name"}, {"Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Sender", each ([HasAttachments] = true)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Subject] = "sample files for email PQ test") and ([Folder Path] = "Inbox")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Attachments"}),
    #"Expanded Attachments" = Table.ExpandTableColumn(#"Removed Other Columns", "Attachments", {"Name", "AttachmentContent"}, {"Name", "AttachmentContent"}),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Expanded Attachments", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Mail", each #"Transform File from Mail"([AttachmentContent])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from Mail"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Mail", Table.ColumnNames(#"Transform File from Mail"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}})
in
    #"Changed Type"

Code Credits: Microsoft

Power Query Experiences

Power Query can connect to various data sources, allowing users to work with the data they want. Currently, Microsoft offers two Power Query experiences:

1. Power Query Online
2. Power Query Desktop

Power Query Online

The following are the steps for getting data in Power Query Online:

Connection Settings and Authentication

Visit the Power Query Online experience and head to the “Connect to the data source” page. Here, you have to enter these values in two distinct sections:

• Connection settings: The connection settings section defines the information needed to create a connection to your data source. Consider an example where you are using an SQL database as a data source.

• Connection credentials: The connection credentials necessary to establish a connection to your data source are made up of the privacy levels, credentials, and the rest of the connector-specific fields.

Enter your credentials, such as User ID and Password, to proceed further with the ETL process in Power BI

• Authentication: In Power Query, each and every connection must be authorized. Authentication techniques differ from connection to the connector.

Data Preview

The purpose of the data preview stage is to make previewing and selecting your data as simple as possible. You can preview data using one of the following methods, depending on the connector you’re using:

• Navigator window
• Table preview dialog box

Query Editor

For Power Query Online, it is required to load the data into the Power Query editor, where users can further transform and enrich the query they choose.

Select the Table that you want from the data source. To proceed to transform data, check all dimensions and fact tables. You can carry out transformations and then load data to complete the Power BI ETL process. 

Some of the transformations you can do on your data are:

• Removing Unnecessary Columns

To do so, select the columns you want to remove, right-click, and then click on remove columns. 

• Setting Column Headers

Consider an example where you want to remove the top row as actual headers are in the second row. For this, click “Remove Rows” in the Power Query Editor toolbar. Then click on “Remove Top Rows” and enter “1” in the Number of Rows dialog box.

Now, to set the first row as our column headers, on the Power Query toolbar, click the “Use First Row as Headers” option

Your table will now show the correct column headers.

Power Query For Desktop

The following are the stages for gathering data in the Power Query Desktop experience:

Connection Settings and Authentication

• Connection Settings: Most connectors require at least one parameter to create a connection to the data source. In conjunction with the connection, the connector parameters specify your data source path.
• Authentication: In Power Query, each and every connection must be authorized. Authentication techniques differ from connector to connector.

Data Preview

The purpose of the data preview stage is to make previewing and selecting the data as simple as possible. Depending on the connector that you’re using, you can preview data by using either:

• Navigator window
• Table preview dialog box

Query Destination

In the Query destination stage, users can define where the query should be loaded. The possibilities vary depending on the integration, but loading data into the Power Query editor to further transform and enhance the query is always an option.

ETL with Power BI dataflows

Once you know what is ETL in Power BI, you can carry out this process with Power BI dataflows. They are collections of tables you create and manage in workspaces of Power BI service. It is similar to some tables in your database, which you can update through refresh schedules. It works using Power Query while the resultant data is loaded in the Azure Data Lake Storage Gen2. For this, you are required to have a premium license for a Power BI Service. 

You can follow the below steps to create a dataflow with Power BI:

• Login to Power BI Service. In your workspace, click on new and then dataflow.

You will see 4 options that allow you to create dataflows using different methods.

Method 1: By defining new tables

Click on Add new tables and then connect them to your data source. Here, we are using an SQL server database as a source.

Configure SQL server as source in Power BI.

Select the table you need and then click on transform data.

Method 2: Using linked tables

In this method, a link is created to an existing table. For this, select Add Links Tables from four options and select the table you wish to link from the hierarchies of tables. Then, click on Transform Data. 

Method 3: Using Computed Tables

This is done by including a reference table and doing some storage computations. You can edit the linked tables and start creating computed tables from here. For this, you can right-click a table and check  Enable load. 

Method 4: Using Import/Export

You can export an existing Dataflow to a JSON file with this. You can then create a Dataflow in another workspace by importing the model using the exported file.

Then, you can refer to the four options for creating a new Dataflow again. You need to go to another workspace and create a new dataflow to import the model that you exported. Select the Import model. Search for the JSON file you exported and then click OK.

Thus, Power BI dataflows can also be an efficient way of carrying out the ETL process if you know the ETL process in Power BI.

Conclusion

In this blog, you learned about Power BI extract transform load and how to perform data preparation using Power BI and Power Query.

When it comes to building your own ETL pipelines, integrating and merging heterogeneous data sources can get overwhelming, especially for starters when you don’t have a helping hand. Configuring and running your ETL pipelines would require recurring assistance from your engineering teams, and non-data teams would need a great deal of training to become familiar with your setup. 

Luckily, Hevo has done all the legwork for you, so you can set up and configure your ETL pipelines in minutes. Hevo Data is a No-code platform that supports data ingestion from multiple sources be it your frequently used databases or SaaS applications like MySQL, PostgreSQL, Salesforce, Mailchimp, Asana, Trello, Zendesk, and other 150+ Data Sources. Hevo migrates your data to a secure central repository like a Data Warehouse in minutes with just a few simple clicks.

Why not try Hevo and the action for yourself? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also check out our Hevo Pricing plans to choose the best-matched plan for your business needs. 

Share your experiences after reading our comprehensive guide on Power BI Extract Transform Load mechanism in the comment section below! We would love to hear your thoughts.

FAQ