Power Query Parameters – How to use Named Cells as Flexible Inputs

 

Check out our YouTube channel with weekly videos dedicated to Excel, Power Query and Power BI

Power Query is the best thing to happen to Excel since Tables were introduced in Excel 2007

**Updated May 2018**

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 table into this file from another Excel file, however I’d like to allow the user to change the File Path or the CutoffDate in the orange cells click refresh and hey presto the data gets pulled in.

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. Change the data type to the required type. (e.g. for a text parameter choose text and for a date parameter choose date.) Then Right-Click on the Text in Column 1 and select Drill Down

5. Go to Home – Close & Load – Connection only

You now have a parameter query called FilePath with the letters ABC next to it showing it’s a text based parameter. I’ve repeated this for CutoffDate in the screenshot above which has a Date icon next to it (because I changed the type to date in step 4).

6. Now we can use these parameters in our main query

For example, here I am connecting to the demo file and filtering on date

Then I go into the Advanced Editor and change the code as follows:

And the query now works but is controlled by those 2 named ranges in my Excel sheet.

 

IMPORTANT NOTE

For this to work you will need to change your privacy settings to “Ignore”, via Query Options > Privacy > Always ignore Privacy Level settings

This is not ideal and I have raised a request on Excel user voice to get this changed.  Please vote here

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/32691571-add-get-parameter-button-to-power-query-and-avoid

Create or Download your own Power Query Calendar Here

If you have any thoughts or comments let us know.

Extra Notes

1. Just for clarity purposes I tend to name my parameter queries beginning with a p. So pFilePath and pCutoffDate to make it obvious in my code that these are parameter queries.
2. Autocorrect shortcut

For those of you comfortable with Power Query here’s the simple trick I sometimes use to shortcut this process – 

I use Autocorrect to store this piece of Power Query Formula (including the comma at the end) as pqname

ParameterName = Excel.CurrentWorkbook(){[Name=”NamedRange”]}[Content]{0}[Column1],

Then whenever I type pqname into Excel this formula appears which I copy and paste into my Query.

 Want to learn more? Follow these useful links

• Public courses
• Online training
• In-house courses