Data IS Life…and “Life, Uh, Finds A Way.”
P3 Adaptive friends…for those of you in the know (and for those who aren’t), Power Query (or PQ for short) is one of the cornerstones of the Microsoft BI Suite. It’s a tool built into Excel 2016 and Power BI Desktop that allows us to extract our data (78 data connectors and GROWING), transform our data (it’s more than meets the eye), and finally load our data into a Data Model. Wait…so PQ extracts, transforms, and loads data? Those words spell out ETL as an initialism!
Anyone familiar with SQL Server Analysis Services (SSAS) or SQL Server Integration Services (SSIS) might have caught that. I carefully chose to describe PQ that way because in many many ways it reproduces what was historically done with these other tools. While PQ is not a complete replacement for these tools, it does allow you to transform or clean your data for nearly 99% of the data scenarios you encounter.
For a more in-depth post about what Power Query is and when to use it I’d recommend reading this week’s post by Rob. It’s called M/Power Query “Sets Up” DAX, so Learn DAX (and Modeling) First and it does a fantastic job of breaking down when and where transformations should be applied, and when to use DAX. Matt Allington also wrote up a great technical post titled Power Query as an SSIS Alternative that gives a great (and DEEP!) dive into some great ways to leverage Power Query in conjunction with Power Update. Between these two posts you’ll get a pretty good grasp of how much of a heavy hitter Power Query can be!
Knowledge is a Terrible Thing to Waste
Now that we have a basic grasp of what Power Query is, I’d like to impart some knowledge to you. Specifically I’d love to introduce you to a new post series called Power Query (M)agic. The goal of this series is to share some of the wonderful best practices that we at P3 Adaptive have acquired over the years. These practices have been either been self taught, or graciously passed down from other BI Ninjas. These are practices that can be implemented in nearly any Excel or Power BI Report. The best part is MOST of these practices won’t require you to be a Power Query expert, or even an advanced user!
As long as you have a BASIC UNDERSTANDING of Power Query you’ll be able to implement these yourself. With that being said let’s dive in to today’s technique shall we?
Technique #1 – One Data Source To Rule Them All
So I LOVED discovering this technique! I’ve seen the Reference button in Power Query for a while…but honestly I never took the time to figure out how to fully utilize it. Avi Singh is owed credit for showing me how to fully use this feature. So the idea behind this Reference technique is to create a BASE QUERY that acts as the singular data source for that connection to all other queries.
Hold on Reid, don’t ALL PQ queries already have a source?? Well YES, technically whenever you create a new PQ query, the first step shown IS called Source, and it does point to your data. However, let’s imagine a scenario where you’ve built out a report that has 20+ PQ queries in it, EACH with their own INDIVIDUAL data source. Now if multiple PQ queries point to a data source that changed location or got updated…you’d have to update every single query that references it. Not ideal in my book!
This is where the Reference feature comes in. Reference let’s you create new query, referencing the original query as the data source in any number of additional queries. So if the data connection ever needs updated, now you only have to update ONE QUERY! Have I lost any of you yet? Don’t worry, I’ll paint you a picture with words (well…screenshots) to help explain this better.
Traditional list of PQ queries, each with it’s own separate data source:
New PQ source query, referenced by SEVEN other queries:
Does that first image of queries look familiar? This is a typical setup in most report models…just a long list of queries. The problem (as stated earlier) is that they all have individual sources, many of them pointing to the same connection or location! The solution and output will look something like the second image; a single source query that is referenced by any other query that requires that shared connection. It’s relatively straight forward to implement, so “come with me if you want to learn” and I’ll explain how it’s done. 
Step 1 – Create a new PQ query to ANY table in the database you’re wanting to reference:
Step 2 – DELETE the navigation step, and rename your query. Now you have a base connection to any table in the database:
Interested in Learning How to Do this Kind of Thing?
Our New Online Power Query Course just launched this year. You’ll learn Power Query from the best in the business, two Excel MVPs Ken Puls and Miguel Escobar. We’ve included 7 Hours of Self-Paced Video over 31 Courses, with lots of example files, and a Digital Badge powered by Acclaim.
CLICK HERE to claim your spot.
Step 3 – Right click the query and select Reference to create a new query with this as the data source:
Step 4 – Navigate to the DB connection the table you want, and rename the query (DONE!):
Simple right, and NOT a single line of code required! This step can be repeated for as many tables or views that you have in that database. But wait…what if there’s a report that’s already been built, and we want to update existing PQ queries? Well there’s a solution for this too! Although for this we will technically we will have to write some M code, it’ll be painless though I promise. All it will require is a single line of code to be written, that will override the Source code of your existing query.
Go to the source step of the existing PQ query you wish to update, and replace the SQL Connection with the connection query name:
When typing in the name, just make sure to include a pound symbol and quote (#”) before the PQ query name, and a quote (“) following it. If you’re ever having trouble figuring out how to write this Source step, you can create a new Reference Query and copy the code from there, then delete that reference query. Honestly one of the great things about this function is how similar the steps are for doing this with any data source. The majority of the steps I’m outlining here are transferrable! Well that’s it for today P3 Adaptive Nation, stay tuned for the next part in this series so until then.
