Power Query Variables 3 Ways

Power Query variables are a great way to store data that can be used multiple times and can be easily updated in one place. There are a few ways you can create variables.

1. From the worksheet, which means you don’t even need to open the query editor to update the variable. This is great if you’re handing the file over to someone else to maintain.

2. You can declare a variable inside the query editor. This makes it more difficult for users to easily change the variables and keeps the data all in one place.

3. You can derive a variable from another query making it dynamic. As the data in the query changes, the variable automatically updates on refresh based on the latest data.

Watch the Video

Download Workbook

Power Query Variables from the Worksheet

We’ll look at Power Query variables stored in the worksheet. Here I have a table of employee salaries (TblSalaries):

I want to calculate the bonus using Power Query and I want the user of this file to be able to adjust the bonus percentage without opening Power Query. I’ve stored the bonus variable in a separate table in the worksheet (BonusRate):

I’ve loaded both of these tables to Power Query. In the BonusRate table I simply right-click > Drill Down on the rate cell:

I’ll rename this query ‘SheetBonusRate’ for the purpose of this tutorial, so we know it comes from the sheet. In practice you’re more likely to simply call it ‘BonusRate’.

In the Query Editor I now have two queries, my QuerySalaries and SheetBonusRate variable:

In the QuerySalaries I can add a custom column:

That uses the SheetBonusRate variable to calculate the bonuses:

And my QuerySalaries table now looks like this:

I’ll Close & Load to a table in the Excel file:

If I want to update the bonus rate, I simply enter a new rate in cell E5 (1) and then right-click the QuerySalaries table and Refresh (2):

And just like that, the table is updated with the new bonus amounts:

Power Query Variables in the Query Editor

If you prefer to store your variables inside the Query Editor, you can simply add them via the Advanced Editor. In the example below I have sales data (TblSales) and I need to add a column to calculate the sales tax.

To add a variable in the Power Query editor, go to the Home tab > Advanced Editor:

After the ‘let’ enter the variable name* and the variable value, followed by a comma:

*If your variable name has spaces in it, you must declare it with # and surround it in double quotes, e.g.: #"Tax Rate"

If your variable is text, it must also be surrounded by double quotes.

You can now see the variable in the Applied Steps list, and going forward you can edit it in the formula bar:

I can use the variable in a calculated column by referencing the name:

Note: if your variable name has spaces in it, you must reference with the hash and double quotes like so:

= [Sale] * #"Tax Rate"

Power Query Dynamic Variables

Lastly, you can derive a variable from another query making it dynamic. As the data in the query changes, the variable automatically updates on refresh based on the latest data.

In the example below my source data contains sales by product (TblProdSales):

I want to add a column that calculates the percentage of total sales and obviously this will change with each month’s new data.

In the Query Editor I need to duplicate QueryProdSales – right-click query name > Duplicate:

Then select the Sales column > Transform tab > Statistics > Sum:

This returns a single value, i.e. your TotalSales variable. Rename the query accordingly:

Then back in the QueryProdSales query add a custom column that calculates the percentage of total sales:

Which results in:

As you add to or replace the data in your source table (TblProdSales) and refresh the query, it dynamically updates.