By Wyn Hopkins
Power Query is the best thing to happen to Excel since Tables were introduced in Excel 2007
When developing Power Query solutions for others to use, or when needing parameters that can be changed easily (e.g. source file or date) you can store these variables in named cells and reference them via Power Query parameters.
Unfortunately it’s not super straightforward, but nor is it super complex.
For example, in the image below, I’m pulling a simple 3 row table into this file from another Excel file, however I’d like to allow the user to change the File Path in the orange cell click refresh and hey presto the data gets pulled in from the new file.
For those of you comfortable with Power Query here’s the simple trick I used to achieve this (avoiding steps 1 to 7 below).
I use Autocorrect to store this piece of Power Query Formula (including the comma at the end) as pqname
Then, whenever I type pqname into Excel, this formula appears which I copy and paste into my Query.
If you’re doing this for the first time then here’s the steps I go through to get this formula
1. Firstly I name a cell as FilePath (or whatever parameter name is suitable)
2. Data > Get & Transform > From Table (I know it’s not a table but it works!)
3. Delete the 3rd and 2nd steps in the Query editor
4.Right-Click on the Text in Column1 and select Drill Down
5. Then go to the Advanced Editor
6. This is what the Power Query code will look like
7. Amend the Code to look like this.
Essentially you are removing the words Column1 = Source from the second line.
Make sure you keep a comma on the end
Highlight and Copy this single line of code (see that this is the code I store in my Autocorrect settings to save me the hassle of doing the above steps every time)
Close out of that window and discard any changes (don’t save it)
8. Then go into your original query (Right-Click Edit)
Then paste your code to the first line, and amend the 2nd line of code to refer to the word FilePath rather than the full file path text string.
Click Done and you now have a parameter feeding your query rather than a hard-coded piece of text.
This can be applied to all sorts of steps in Power Query including filtering data by certain parameters, and replacing certain characters etc.
If you have any thoughts or comments, let us know.