staging queries using Power Query editor

Power Query Editor: Staging Queries

No comments

In this tutorial, we’ll learn about staging queries using Power Query editor. This is a new concept that I’d like to discuss for us to have an understanding of when and where we can use this in the query editor.  

Staging queries can be used to configure and expose the data sources parameters to the dimension and fact table queries of the dataset.

For Power BI datasets, we need to define parameters specific to the data sources, and develop a staging query which references those parameters. This is a recommended data access method.

Analyzing The Data Model

If we look at our model right now, we already have the essential elements, but it’s still too complicated. Therefore, we need to utilize the queries to integrate our tables or data into a more refined and optimized model. Staging queries via Power Query editor is an intermediary step to achieve this.

data model

Here, we have tables like Channel DetailsCustomersProductsRegions, and a range of Sales tables for 20142015, and 2016.  

Let’s see how our demo data might look from disparate data sources that we may have to connect in the future. If we check out the Sales Orders tables in this demo data, we’ll notice that it is exactly the same data that only differ in a year. We need to query this data so we can bring it into our model. Hence, we have to find a way to integrate them while still maintaining those queries, so they’d still continue to find the data.

Sales Orders tables

Moreover, we shouldn’t leave them with the same look and shape like their initial query. That’s why we need to create this intermediary step called staging area for these tables. Then, within the query editor, we’re going to optimize the table or make it as a single table.

We have the Sales table, and the lookup tables such as Customers, Regions, and Products. We also have the Channel Details table, which we’re going to use as a staging table. Later on, we need to merge the Channel Details table into our fact table (Sales table). For the meantime, we’ll put the Channel Details table into our staging query. This is just to show you how we can utilize this table.

Setting Up The Model Via Power Query Editor

Now, let’s go back to our demo model. I’ll show you how I would suggest setting it up since we only want to create one table of sales out of these three tables.

We need to keep these three queries. So, we’re going to create a new group for them and name it Staging Queries.

staging queries group via power query editor

Let’s also move the Channel Details table into our Staging Queries group. 

Power Query editor: moving the Channel Details table within the staging queries folder

Then, let’s move the CustomersProducts, and Region tables inside the Data Model group.

organizing tables within the Power Query editor

This is just another good example of how to properly organize our tables. 

Finalizing The Staging Query Via Power Query Editor

The only other thing to recognize with staging is the need for us to continue querying the data source. However, we shouldn’t let these tables go into our models.

To organize that, let’s start with right clicking on the Sales_2014 table. In this table, we’ll maintain the Include in report refresh option. Then, disable the load by unticking the Enable Load option.

disabling the load within the Power Query editor

Let’s also disable the Sales_2015, Sales_2016, and Channel Details table by unticking the Enable Load option. This is the last step before we commit these queries to our model.

As a result, this is what our staging query should look like. They’re still going to query, but we’re not going to bring them into our model.

Staging Query Via Power Query Editor

This is to remove data source parameters from data retrieval queries and also, to make it easy to manage data sources and their queries.

Conclusion

Keep in mind that staging is about using these queries either from those that are already in a model and the ones that are in our staging area. Then, we need to visualize the model that we want to build out of these. We have to optimize, clean and shape our tables to get them into the model that we’re going to build relationships with.

In the other tutorials, we’ll learn how to append and merge these tables from the staging area. Hopefully, this gives you a good idea on how you can utilize this concept and how you should think about it from a query perspective.

All the best,

Sam

Membership Banne
Center of Excellence

***** Related Links *****
Organizing Your Queries: Power BI Query Editor Tutorial
Organizing Your Queries Into Groups In Power BI
Querying Data From Multiple Data Sources

***** Related Course Modules *****
Power BI Super Users Workshop

Power Query Series
Advanced Data Transformations & Modeling

***** Related Support Forum Posts *****
Power Query Editor Help Or Use DAX Calc Column?
Changing Data Sources In Power BI/Query Editor
Importing Data With New Columns In Power Query
For more Power query editor queries to review see here….

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.