You are reading the article Power Query Variables 3 Ways updated in September 2023 on the website Lanphuongmhbrtower.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 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
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.
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’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:
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.
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.
You're reading Power Query Variables 3 Ways
Update the detailed information about Power Query Variables 3 Ways on the Lanphuongmhbrtower.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!