skip to Main Content

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 solutioninterchangeable parameters
  • 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.

 

  1.        Main file containing the M code                          Analysis.xlsx                                   C:\fileshare\Analysis.xlsx
  2.        Jan sales                                                             Sales.csv                                        C:\fileshare\finance\file\Jan\sales.csv
  3.        Jan inventory                                                       Inventory.csv                                 C:\fileshare\finance\file\Jan\inventory.csv
  4.        Feb sales                                                             Sales.csv                                        C:\fileshare\finance\file\Feb\sales.csv
  5.        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:

My folder setup

 

analysis.xlsx

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!

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.

Second substitution variable

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

update source file string

l) Replace the ‘old’ file string with the new string which includes the File Path and Month subvars and “\sales.csv”

replace old file string

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

Source file string segments

4.  Repeat the process for inventory.

                             from this:              “C:\fileshare\finance\file\Jan\inventory.csv”

                                  to this:              subVar_File_Path& subVar_Month & “\inventory.csv”

repeat process for inventory

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:

SubVars table

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

Feb results

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.

Summary

We now have two methods to manage the inevitable changes that occur with Power Query files. Use whichever method suits your needs.

  1. References let many queries point to the same data source in one location (refer to Reid’s post at https://goo.gl/5g1BLc)
  2. 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.

Doug Burke

Doug is an independent consultant (www.dougburkedata.com) with 20+ years of experience creating Financial data and analytics systems at Fortune 500 companies.

He believes in the ability of Power Query and Power BI to
• Encourage your data dreams
• Fix your data challenges
• Lead you to ongoing data success

This Post Has 14 Comments
  1. Hi Doug … for sure this will help many people who need to change the source at each update. I used to do this all the time but I use some simple steps to do that (correct me if I´m wrong or missing something)

    1 – In any Excel sheet, combine the many variables into one single cell to define the file path (basically, what you did in “Combined:”)
    2 – Give a name to this cell (e.g. FilePathX)
    3 – In PQ include the two lines below using Advanced Editor to define the source and done. Next time just need to update the references
    FilePath= Excel.CurrentWorkbook(){[Name=”FilePathX”]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(FilePath), null, true),

    Instead of variables in PQ, you can define your variables in Excel and use them in PQ.

    1. Gilberto,

      Excellent point. You are absolutely correct. Once you understand a bit of the M code, the heavens open and the angels sing.

      One advantage of the method you describe is that it can be modified using Visual Basic code. That opens up many development possibilities.

      Thank you for sharing!

      Doug

  2. Synchronicity!

    Microsoft’s Adam Saxton (guyinacube.com) posted today about ‘Power BI parameters – Updating in the Power BI Service or with APIs’.

    Focuses on ‘how you can update Power BI parameters within the service or by using APIs’. Particular attention paid to using parameters for server and database connection to data source.

    His perspective is valuable and provides a different spin on the topic. Thanks to MSFT for sharing.

    Check it out.

    https://guyinacube.com/2018/04/power-bi-parameters-updating-power-bi-service-apis/

    Doug

  3. Hi, I am new here. can anybode tell me how i can unpivot many colums of many types?

    suppose code 1,code 2 and code 3 is one type and name 1, name 2 and name 3 is another type. i want to unpivot code wise and name wise.

    Help required

    Abbas

      1. Doug … I created a report with some “time intelligence” using a not linked calendar table that works fine at month level but not working at quarter/year level. I tried two options but both just work for month. Any specific forum or other way that I can use to submit my problem/doubt and try to get some help? Thanks in advance

  4. Excellent post and a topic that recently came up for us as well.
    I wondered why you wouldn’t use the in-built Manage Parameters feature for your variables?

  5. I do something similar, but I keep all of the data in an Excel table called “tblParameters” that has two columns – First column is Parameter, 2nd is ParameterValue. Then I use this custom function (put in a blank query through the Advanced editor.

    (Parameter as text) =>
    let
    ParamSource = Excel.CurrentWorkbook(){[Name=”tblParameters”]}[Content],
    ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = Parameter)),
    Value=
    if Table.IsEmpty(ParamRow)=true
    then null
    else Record.Field(ParamRow{0},”ParameterValue”)
    in
    Value

    Then, to refer to it in a connection string, I just do something like := Sql.Database(fnParameters(“Database”), “DATA”) where the “Database” row in my tblParamters points to a given SQL server.

    It could also be dynamic. SOme of my workbooks need to pull data based on where they are, so using the =INFO() function in Excel, I can dynmically build a path when the workbook opens, which is in the table, then feeds the fnParameters(“Path”) function in a connection string.

Leave a Comment or Question