• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Power Query Variables 3 Ways

You are here: Home / Power Query / Power Query Variables 3 Ways
power query variables
August 4, 2022 by Mynda Treacy

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

Subscribe YouTube



Download Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.


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):

Power query variables 1

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):

Power query variables 2

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

Power query variables 3

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:

Power query variables 4

In the QuerySalaries I can add a custom column:

Power query variables 5

That uses the SheetBonusRate variable to calculate the bonuses:

Power query variables 6

And my QuerySalaries table now looks like this:

Power query variables 7

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

Power query variables 8

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):

Power query variables 9

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

Power query variables 10


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.

Power query variables 11

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

Power query variables 12

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

Power query variables 13

*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:

Power query variables 14

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

Power query variables 15

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):

Power query variables 16

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:

Power query variables 17

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

Power query variables 18

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

Power query variables 19

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

Power query variables 20

Which results in:

Power query variables 21

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

power query variables

More Power Query Posts

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.
easily compare multiple tables in power query using list functions

Easily Compare Multiple Tables in Power Query

Compare tables or lists in Power Query using List Functions. This method is great when dealing with 3 or more tables or lists.


Category: Power Query
Previous Post:Import data from a picture to ExcelImport Data from a Picture to Excel
Next Post:Easily Remove Excel Password ProtectionEasily Remove Password Protection from Excel Files

Reader Interactions

Comments

  1. fcatcher

    February 4, 2023 at 8:04 pm

    Hi, thx for this: very clear.
    I was trying to use the cell to filter my table (in my case just came from a table connected trough odbc) wich contains a column req_id

    I created the filter query, via the drill down but I Cant figure how to filter the table with the value of query created (req_id = SheetBonusRate).

    any Idea how to do this ?

    Reply
    • fcatcher

      February 4, 2023 at 8:43 pm

      in the end I thought it was easier to directly filter the query on the select to the db and I solved it this way

      Source = Odbc.Query(“dsn=matrixpy”, “select * from dl_req #(lf) where req_id='” & MyId & “‘” )

      thx again for your post

      Reply
      • Mynda Treacy

        February 5, 2023 at 7:47 pm

        Thanks for sharing your solution.

        Reply
  2. Lebenya

    September 7, 2022 at 2:19 am

    Very informative…
    I have a question for you though…
    How do I go about dynamically changing a table name in an sql script using power query in excel?

    Reply
    • Mynda Treacy

      September 7, 2022 at 8:27 am

      Depends what you mean by dynamically and what the new name is based on. If you add a step in Power Query to change the column name, it will make that change every time you refresh the query, if that’s what you want. If it’s something else, then perhaps you can post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  3. Zaigham uddin Farooqui

    August 6, 2022 at 8:14 pm

    I always found your tutorials very helpful, very well explained and easy to understand. It shows your firm grip on Excel and PQ. Though I have a little use of PQ. However I’ve tried it in my some projects by following the tutorials available at various sites.

    I have a request, can you make a tutorial on getting data from folder from many Workbooks having multiple worksheets with common layout but different names using PQ?

    Reply
    • Mynda Treacy

      August 7, 2022 at 8:54 am

      Hi Zaigham,

      Please see this tutorial: Importing multiple files containing multiple sheets with Power Query.

      Mynda

      Reply
      • Zaigham uddin Farooqui

        August 12, 2022 at 8:12 pm

        Thanks a lot for guiding. May God bless you and your family.

        Reply
  4. RichardW

    August 5, 2022 at 7:45 am

    Very useful. Thanks.

    I’ve always put my worksheet variables for PQ in cells with range names. But that requires knowing the PQ code for referencing a named range. As I can’t remember the code, I have to look it up every time. The worksheet variable method given here (using a table instead of a named range) is simpler.

    Reply
    • Mynda Treacy

      August 5, 2022 at 9:50 am

      Glad it’ll be useful to you, Richard 🙂

      Reply
  5. Jim

    August 4, 2022 at 11:46 pm

    A fourth way is to use a named range.

    variable = Excel.CurrentWorkbook(){[Name=”NamedRange”]}[Content]{0}[Column1]
    will return the value contained in NamedRange. However, this method is susceptible to language/regionalization issues in that “Column1″ is English-centric.

    A language-insensitive approach is:
    variable = Table.FirstValue(Excel.CurrentWorkbook(){[Name=”NamedRange”]}[Content])

    (thanks to Pascal D.)

    Reply
    • Mynda Treacy

      August 5, 2022 at 9:49 am

      Thanks for sharing, Jim!

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.