Appending queries

How To Append Power Queries In Power BI

No comments

In this tutorial, we’re going to learn how to append power queries in Power BI. This technique is the process of layering one thing on top of the other. I’m going to show a perfect example as to why we need to do this.

In the tutorial that I made for staging queries, we created staging queries for our sales tables. We did that because the sales information was in three different tables and we don’t want to bring three different tables with exactly the same structure in our Power BI model. If they have the same structure (like the same column with the same column names), it’s much better to layer them on top of one another by appending them.

Creating The Main Table

First, let’s create the main table. In this case, the main table is our fact table, which we’re going to call Sales. To start, let’s right click on Sales_2014 then select the Reference option.  

Referencing table for power queries in power bi

Then drag the reference table Sales_2014 (2) into our data model.  

moving the reference table for power queries in power bi

After that, let’s rename this table into Sales. Now, this is going to be the main table where we’re going to append the Sales_2015, and Sales_2016 tables.

Note that we don’t want to remove or merge these Sales_2015 and Sales_2016 staging queries at all. This is because we’d want to see what things look like in their initial form.

Moreover, we don’t want to lose our understanding of how our model is developed because we won’t see the underlying plugins that made our tables and our model. That’s the main reason why we created a reference table where we can append these Sales_2015 and Sales_2016 tables.

Appending Power Queries

To append our tables, let’s select the Sales table that we’re going to set up as our main table. Then, go to the Home ribbon and select the Append Queries option.

Appending Power Queries in Power BI

Now, we’ll see that there’s a slight change inside the Queries pane. As you can see, the icon beside the Sales table was changed to an exclamation mark.  

In this case, we just need to click the dropdown input of the Table to append option, and select the Sales_2015 table. Click the OK button to apply the changes.

Appending Power Queries in Power BI

Let’s also append the Sales_2016 table by doing the same step. 

Appending Power Queries in Power BI

After doing those steps, we’ll notice that we already have a newly-pinned query transformation such as the Appended Query and the Appended Query1 in the Applied Steps under the Query Settings pane.

Let’s then click the Advanced Editor and we’ll see that it runs this simple information. Noticeably, it looks simple and understandable. The first line shows us the source which is the Sales_2014 table while the second and the third lines specify that we appended the Sales_2015 and Sales_2016 tables.

Let’s think about how this would work as we move along. We might get a 2017 sales data that might be another table inside a spreadsheet or it might be another data source. Hence, all we need to do is to query that new table and put it in as a staging query, lay it out intuitively, then append it to our main sales table.

Conclusion

Appending power queries in Power BI is an easy yet powerful technique in terms of setting up and optimizing models. We don’t need to create any new calculations, new filters, or new visualizations because it should all filter into anything we have built before. Combining this with the other techniques makes our model scalable from an analytical solution to a reporting perspective.

Hopefully, you can see the potential and the importance of understanding this technique well.

All the best,

Sam

Membership Banne

***** Related Links *****
How Query Dependencies Work In Power BI
Organizing Your Queries: Power BI Query Editor Tutorial
Simple Power BI Transformations For More Optimized Data

***** Related Course Modules *****
Advanced Data Transformations & Modeling
Best Practices & Feature Reviews
Power Query Series

***** Related Support Forum Posts *****
Power Query IF And OR
Inconsistent Columns While Importing In Power Query
Near Duplicates – Power Query
For more Power queries in Power BI queries to review see here….

Leave a Reply

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