I love the idea behind Power Query (M)agic. These posts let Power Query users learn and share best practices (tips & tricks) that solve real problems. Query management comes up a lot.
- What if multiple PQ queries point to the same data source that changes location or gets updated?
- How can I avoid updating every single query that references it?
- How can I simplify relinking to source data files when they’re moved to different directories?
Reid Havens wrote a helpful post on the topic involving file references. He shows how references let many queries point to the same data source in one location.
His guidance: “The idea is to create a BASE QUERY that acts as the singular data source for that connection to all other queries. If the data connection ever needs updating, you only need to update ONE QUERY!”
A brilliant tip when you have sourced data coming from one database table. Update once – – view many.
But what if you have many queries pointing to different data sources located in different locations? Or use the same query for different months of data without repointing to different source files (as in time-based reporting)?
Can file references do that?
Alas, no. But help is on the way in the form of SUBSTITUTION VARIABLES.
SUBSTITUTION VARIABLES to the rescue!
- They are the ultimate ‘write once, read many’ data development solution
- Simple to learn & implement and have untapped superpowers
- Think of them as universal translators for Power Query users
Note: Power Query documentation calls them ‘parameters’ but the term is interchangeable
- introduce query flexibility and simplify time-based reporting
- stops you from ‘hard coding’ file paths and source file names that may change over time
- avoids broken queries and increases system integrity when sharing Power Query with other users
- increases user confidence (substitution variables signal that you’re a higher level PQ developer)
What Are Substitution Variables (SubVars)?
A substitution variable substitutes a variable (a changing value) to get a different result
a + b = c (where ‘a’ and ‘b’ are substitution variables that define value ‘c’)
- If a = 5 and b = 2 then c = 7
- If a = 25 and b = 9 then c = 34
SubVars are especially helpful when
- you need to move Power Query files from one file path, folder and file name to something completely different
- you want to use the same PQ reports for different time periods (ie changing months)
It involves looking for changeable items. If something may or will change in the future, it’s a candidate for a substitution variable.
- file paths are good for subVars
- so are time elements such as Year or Month
- you can also combine subVars where both the file path and month may change
Here is an example:
I am including downloadable Excel files so that you can follow along. You can download the zip file here.
- Main file containing the M code Analysis.xlsx C:\fileshare\Analysis.xlsx
- Jan sales Sales.csv C:\fileshare\finance\file\Jan\sales.csv
- Jan inventory Inventory.csv C:\fileshare\finance\file\Jan\inventory.csv
- Feb sales Sales.csv C:\fileshare\finance\file\Feb\sales.csv
- Feb inventory Inventory.csv C:\fileshare\finance\file\Feb\inventory.csv
Note: The folder structure I use is shown below. Feel free to redirect according to your own file path.
My folder setup:
This is all possible because Power Query runs on ‘M’ code. It’s a programming language that can be used in many different ways and lets you refine your solutions.
The best part? You don’t need to become a programmer to use the (M)agic.
Let me demonstrate how to embed subVars into your ‘M’ code. Then, feel free to copy these procedures to improve your own Power Query files. That’s what sharing ideas are all about.
You just created your first substitution variable!
You just created your second substitution variable! Now let’s use them together.
Here is a different perspective on how the ‘Source’ file string for File.Contents using subVars is constructed
- it now contains 5 segments
- the & (ampersand) symbol joins each segment
- the 5 combined segments create the new string
- uses unformatted double-quotes
4. Repeat the process for inventory.
from this: “C:\fileshare\finance\file\Jan\inventory.csv”
to this: subVar_File_Path& subVar_Month & “\inventory.csv”
Anytime we need to change the file path or use the same analysis file for a different month, just update two simple substitution variables. No more messing around with M code or manually repointing to different files. How cool is that?
One More Thing: What happens if the file names supplied to us suddenly start containing the month names in the (such as ‘sales_Feb.csv)?
No problem. Substitution variables to the rescue once again.
We already have a subVar for the month name, so let’s also add it to the source file string.
Review the existing file name and insert the Month subVar into it as shown
From this: “C:\fileshare\finance\file\Jan\sales.csv”
To this: subVar_File_Path & subVar_Month & “\sales.csv”
To this: subVar_File_Path & subVar_Month & “\sales_”& subVar_Month & “.csv”
From this: “C:\fileshare\finance\file\Jan\inventory.csv”
To this: subVar_File_Path & subVar_Month & “\inventory.csv“
To this: subVar_File_Path & subVar_Month & “\inventory_” & subVar_Month & “.csv”
Now we have a 9 segment string. Here’s how it looks for the sales file:
What happens when time moves forward and you need the next month’s data?
Set Month subVar to “Feb” and get February results. The subVar simply ‘repoints’ to the new data source files. (M)agical
Wow, that was a lot of detail.
But PowerPivotPro blog readers want the details. That’s what sets us apart from casual Power Query users.
We now have two methods to manage the inevitable changes that occur with Power Query files. Use whichever method suits your needs.
- References let many queries point to the same data source in one location (refer to Reid’s post at https://goo.gl/5g1BLc)
- Substitution Variables lets many queries point to different data sources located in separate locations (described here). Super powerful.
I encourage you to explore the possibilities. Try it out with your own files. Have spectacular failures. Don’t worry, you’ll soon get the hang of it.
If you have questions, please email me at [email protected] and I will happily assist.
Microsoft’s platform is the world’s most fluid & powerful data toolset. Get the most out of it.
No one knows the Power BI ecosystem better than the folks who started the whole thing – us.
Let us guide your organization through the change required to become a data-oriented culture while squeezing every last drop* of value out of your Microsoft platform investment.
* – we reserve the right to substitute Olympic-sized swimming pools of value in place of “drops” at our discretion.