The Challenge
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.
The Solution
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
Benefits
- 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:
a) Two files in one directory with a varying month name (Jan, Feb, Mar . . . ) in the directory
b) Same two files after being moved to a different directory which also has a varying month name
c) Same files using subVars for file path & and month name
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.
Embed SubVars
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.
1. Create the File Path subVar
a) Open your Power Query file
b) Open a blank query: Data > New Query > From Other Sources > Blank Query
c) Enter whatever File Path you choose (ie C:\fileshare\finance\file\) into formula box
d) Press ‘Enter’ and File Path name will be copied to data section of Query Editor
e) Rename query from ‘Query 1’ to whatever name you choose (I like ‘subVar_File_Path’)
You just created your first substitution variable!
2. Create the Month subVar
f) Open another blank query: Data > New Query > From Other Sources > Blank Query
g) Enter a month name (ie Jan) into the formula box
h) Press ‘Enter’ and Month name will be copied to data section of Query Editor
i) Rename query from ‘Query 1’ to whatever name you choose (I like ‘subVar_Month’)
You just created your second substitution variable! Now let’s use them together.
3.Update the ‘Source’ file string for query ‘data_sales’ to include the File Path and Month subVars
j) Open ‘data_sales_without_subVar’ query (which points specifically to the Jan data source) to see the ‘before’ view
k) Select ‘Source’ > look for the file string in the formula bar. This is what you’ll replace with the subVar values
l) Replace the ‘old’ file string with the new string which includes the File Path and Month subvars and “\sales.csv”
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”
The payoff?
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
Sales
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”
Inventory
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 P3 Adaptive blog readers want the details. That’s what sets us apart from casual Power Query users.
Summary
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 doug@dougburkedata.com 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.
Get in touch with a P3 team member