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

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

 

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

**Updated Aug 2017**

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.

Power Query CutoffDate

1. Firstly I “Name” a cell as FilePath  (or whatever parameter name is suitable)

Power Query FilePath

 

 

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

Power Query drill down

 

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

Power Query close and load

 

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

Power Query main query

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

Power Query tblData

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

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.

 

2017-08-08T12:26:11+00:00 Power BI|
  • Rohit

    This is exactly what I was looking for, but I have a slight problem. My solution needs a date parameter, and even though I’ve formatted the cell with the date parameter value as Date, it’s being treated as a string in the Power Query. Any ideas how to mark it as date type in the query?

    • Wyn Hopkins

      Sorry for the slow response.

      After step 3 change the type to Date, if that doesn’t work let me know

      • Rohit

        Hi Wyn – Thanks for the response. I had tried that, but the field kept getting treated as text, and I was unable to proceed. However, your approach has been very helpful and I combined the concept with something I read on another site, and eventually got it to work. Thanks anyway!

        • Nils Maas

          I’m having this exact issue. Can you provide a little more detail on what your solution was? I’m banging my head against the wall.

          • Wyn Hopkins

            These days I use a modified approach and after step 3 I change the type I want ( e,g. Data) then drill down (step 4) but then Close and Load as Connection Only.

            I don’ bother with steps 5 6 or 7 any more

            I then use the Query name in any other query where I need to use the parameter

          • Rohit

            Here’s where I finally picked up the approach, and it worked like a charm for my date parameter: https://www.youtube.com/watch?v=gK2yBpiITvI

          • Wyn Hopkins

            Hi

            I will be updating this article shortly to show the easier way of doing this. Also a bit simpler than Mike Girvin’s approach in the video.

            I’ve already posted the updated article here
            https://www.linkedin.com/pulse/how-used-named-cells-parameter-inputs-power-query-wyn-hopkins