Query Variables

Let’s say we are building a query to clean up and prepare some data for use, and we have a specific value we need to use within the query. But, we would like to be able to change the value as desired, before refreshing the query. One option would be to enter and update that value manually in the query editor. But, that is kind of cumbersome. Another option would be to create a variables table in Excel, and just manage the value there.

Objective

Before we get too far, let’s look at an example. Let’s say that each month, our company’s health insurance carrier sends us an updated CSV file that contains the premium amount for each employee by month. We use Power Query (Get & Transform) to import and process the file.

Each month, for each employee, we need to compute the portion of the premium to be withheld (the employee’s share of the premium). This year, the company is going to cover 40% of the premium, leaving 60% for the employee. But, this percentage may change in the future, and if it does, we’d like our workbook to be easy to update. Plus, we’d like an easy way to update the filters so that we include only the current month values.

So, rather than enter the 60% inside the query editor, along with the date range filters, we’d like to be able to change those values in our Excel workbook. So, we decide to create a variables table in Excel.

The variable values can be referenced inside Power Query as needed. Here is an example of a variables table:

So, let’s jump into the details.

Details

I’ve created a video to demonstrate the steps and I’ve prepared a written narrative below as well.

Video

Narrative

Here is the basic process.

• Create variables table
• Import table
• Filter and access the values
• Reference in formulas
• Reference in filters

Let’s take them one at a time.

Note: The steps below are presented with Excel for Windows 2016. If you are using a different version of Excel, please note that the features presented may not be available or you may need to download and install the Power Query Add-in.

Create variables table

First, we’ll create an Excel table to store the variables we’ll need to reference in our query. This is an ordinary table (Insert > Table) and contains Name, Value, and Notes columns, as shown below.

I’ve named the table Variables (Table Tools > Name) so that it is easy to identify inside the query editor.

Next, we’ll  get the variables table into the query editor.

Import table

To bring the variables table into the query editor, we click any cell in the table and use the Data > From Table/Range command.

It is immediately available in the query editor, as shown below.

We complete this step by saving the query as a connection only query, by clicking Close & Load To … Only Create Connection, as shown below.

With that done, it is time to access the values.

Filter and access the values

Now, we’ll create one new query for each variable. So, we just need to create a new query that references the original Variables query. One way to do this is to right-click the Variables query in the Queries & Connections pane and click Reference, as shown below.

This creates a new query with a Source equal to the Variables query. Any changes we make in our new query won’t impact the original Variables query. So, we first filter the results to display the EE row just by using the drop-down filter control in the Name column. The results are shown below.

Next, we need to navigate to the value, so, we simply right-click the value and select Drill Down, as shown below.

The query results in a single value, as shown below.

This is exactly what we want, so, we rename the query EE as shown below, and load as a connection only.

Now, anytime we need to use the value, we can simply reference the query name, EE. Let’s try it out.

Reference in formulas

Let’s import the hmo premium csv file sent to us by the carrier. Back in Excel, we use the Data > From Text/CSV command and browse to the csv file.

In the resulting dialog, shown below, we click Edit.

Now, we can reference the EE value as needed. For example, if we wanted to create a new column full of the EE value, we can just click Add Column > Custom Column. We set the column name to EE and the formula to =EE, as shown below.

Note: Power Query is case sensitive, so, be sure to enter the name exactly as you’ve named the query, for example, ee vs EE. 

Or, perhaps we wanted to use it in a formula to compute the employee’s share. We could update the custom column formula by clicking the gear icon and multiplying EE by the premium amount, as shown below.

The resulting values are shown below.

In addition to referencing variables in formulas, we can use them in filters. In our example, the csv file contains many months, but, we’d like to filter it for a desired period. So, let’s use the From and To variables to accomplish this.

Reference in filters

To create the FromDate and ToDate variables, we just follow the same steps we used to create the EE query, but, we’ll want to change the Value data type to Date. So, we create a new query that references the Variables query. We name it FromDate. Then we filter the rows to include only the From variable. Then, we change the data type of the Value column to Date by clicking the data type icon in on the left side of the column label as shown below.

Then, we do the drill down and save as a connection only query.

Then, we repeat for the ToDate query.

Now we can reference our two new FromDate and ToDate queries in a filter. For example, in the query that imports the csv file, we could apply a filter to the Date column by selecting the Date column drop-down and selecting Date Filters > Custom Filter. Then, we temporarily pick a specific date (we’ll replace the dates with the variable names in the next step), as shown below.

Then, we simply edit the resulting M code. Don’t worry, this is easy. First, you’ll want to be sure that you can see the formula bar by checking the View > Formula Bar checkbox. That way, you can see the M code for each step, as shown below:

We just replace each of the #date() functions with our variable names, FromDate and ToDate, as shown below.

Now, when we close and load, the query results will be filtered to include only the dates defined in the variables table. We can change any of the variables in the Excel table and just refresh the query.

If you have any other fun Power Query tricks for storing variables, please share by posting a comment below.

Sample files

• Excel variables file: Variables.xlsx
• CSV data file:hmo_premiums.csv