Protiviti / SharePoint Blog

SharePoint Blog

July 13
ETL= Power Query for Power BI

There is a lot being written today about the theory that ETL is dead; an old horse that can no longer keep up with today’s automobile - Big Data (ETL stands for Extract data, Transform the data, and Load the data).  To some extent this is true, but not in the Microsoft ecosystem of Cloud First, Mobile First self-service BI world.   In just four steps I will show you how you can do ETL today!

First, why would you want to do ETL or even care about it? 

ETL in the traditional sense is leveraging data sources that are structured and performed by a SQL developer.  A tool like SSIS (SQL Server Integration Services) is used to mold the data and fill up a data warehouse.  A package is created by the developer and when executed, data is populated into a “warehouse” on a schedule.  Sounds complicated, right?

Enter the world of today – Data comes from everywhere: Structured and Unstructured.  Power users need to analyze this data now.  They don’t want to wait for the time it takes to do traditional ETL.  Never mind the cost. 

What can you do?

Power Query is the equivalent of ETL for the self-service BI user.  You can access it via Excel.  The question is, how?   Learn how by following these simple steps!

Step 1 – Get (Extract) the Data

We are using Excel.  Choose the data source as shown below – in our case we will be using a SharePoint list as the source.  (Notice the Power Query tab – ensure you have this)

peter713151.PNG

Step 2 – Shape (Transform) the data

After the list is chosen, you can see every field as shown below:

peter713152.PNG

Obviously we don’t need all of these fields so we will select to display only the columns we want.

Also, we may need to change types of data – this is as simple as right clicking in the proper column and selecting Change Type as seen below:

peter713153.PNG

Taking a look at the ribbon, one can see the variety of different types of changes that can be made.

 Step 3 – Load the Data

The last step in the acronym ETL is “load” which in our case, means sending the query to a Data Catalog.  As you can see in the example below, this is a workbook query.  As the list changes, the workbook will pick up the data.

peter713154.PNG

At this point you, the power user, have created an ETL “package”. 

Step 4 – Power BI

We are not done, there has to be a step 4.  This step is actually creating the report that can be analyzed by users and this is done via Power BI (Mobile and Cloud First technology!).  You should have Excel Professional Plus.  To check and create the report, click on Insert in Excel, and then you should have a button that looks like this:
peter71315.PNG

Click on the Power View button and create the report.   After it is created you can upload it to your Power BI instance.

As shown, with four steps you can be an ETL pro via Excel and Power Query.

Quick Launch


© Protiviti 2019. All rights reserved.   |   Privacy Policy