How To Parameterize Your Power Query

2017-11-18

When you create a power query in Excel to import or transform your data, Excel is creating the query behind the scenes in a language called M. You can see this M code by going to the Advanced Editor from within the power query editor.

When it creates the code, elements of it will be hardcoded. For example if you’re importing data from an external source the folder path and file name will both be a hardcoded (static or unchanging) string of text in the M code.

If you want to update the folder path or file name to be imported then you need to go into the advanced editor and update the path and file name.

To avoid this, I like to set up a parameter table in my workbooks. This allows me to easily update folders, file names and other inputs in my queries. In this example we’re going to import some data in a CSV and do some minor transformations. Then we will parameterize the resulting query so we can easily update it.

Video Tutorial

Set Up A Parameter Table

Let us set up our parameter table. For this query, I want to import a CSV from a folder and then filter on a given product and date range. I want to have input parameters so I can easily update the folder path, file name, product, and date range that my query will be based on.

The table I created has 3 columns, but it only really needs the Value column where the input value is. The Index and Parameter column are for information and are just there to remind me what row number a value is in (the Index) and a description of what the value is used for (the Parameter).

We need to turn the parameter data into an Excel table by going to the Insert tab and selecting Table or by using the Ctrl + T keyboard shortcut. Name the table Parameters, this is how we will reference the table in our power query. To name a table select it and go to the Design tab and type in a new name under the Table Name: box.

Create A Query Function To Reference Your Parameter Table

Create a blank query. Go to the Data tab in the ribbon and select Get Data in the Get & Transform Data section. Select From Other Sources then select Blank Query from the menu.

Name the query fParameters. This will be how you call the values in your parameter table.

Open the Advanced Editor from either the Home tab or the View tab in the query editor. Copy and paste in the following code then press the Done button.

let Parameter=(TableName,RowNumber) =>
 
let
	Source = Excel.CurrentWorkbook(){[Name=TableName]}[Content],
	value = Source{RowNumber-1}[Value]
in
	value

in Parameter

This query function has two inputs TableName and RowNumber.

  • TableName – This input refers to the table name. In our case it will be “Parameters“.
  • RowNumber – This input refers to the row number that our parameter of interest is in within our table.

Note that power query will count rows from 0, so we will use RowNumber-1 in order to reference our rows in the natural counting order from 1 to N.

Now save the query function.

  1. Go to the Home tab in the query editor.
  2. Select Close & Load.
  3. Select Close & Load To from the menu.
  4. Select Only Create Connection in the Import Data window.
  5. Press the Ok button.

Creating Our Initial Import And Filter Query

Go to the Data tab in the ribbon and select From Text/CSV in the Get & Transform Data section. Select your CSV file then press the Edit button in your query result preview window.

Now let’s add a filter.

  1. Click on the Filter icon on the right hand side of the Product Sold column.
  2. Select Text Filter from the menu.
  3. Select Equals from the menu.

Set the filter to Pencils and press the OK button.

Now we can do a similar thing to filter the Order Date between 2017-02-01 and 2017-03-31.

If you open up the Advanced Editor from the Home tab, your query should look like the following. You can see the parts that are hardcoded (highlighted in red).

let
    Source = Csv.Document(File.Contents("C:\Users\John\Google Drive - Excel\Excel Website\Get & Transform\How To Parameterize Your Power Query\Office Supply Sales Data 2017 Q1.csv"),[Delimiter=",", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Customer Name", type text}, {"Customer Country", type text}, {"Product Sold", type text}, {"Sales Channel", type text}, {"Order Date", type date}, {"Quantity", Int64.Type}, {"Cost", type number}, {"Price", type number}, {"Total", type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Product Sold] = "Pencils"),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Order Date] >= #date(2017, 2, 1) and [Order Date] <= #date(2017, 3, 31))
in
    #"Filtered Rows1"

We can Close & Load this query to a Table in a new sheet and see our data.

Replace The Hardcoded Items With Our Query Function

Now we can replace any instance of a hardcoded reference we wish to turn into a parameter in our query. Replace the text including any parathesis around them with fParameters(“Parameters”,N) where N is the Index in our parameter table which we want to refer to.

let
    Source = Csv.Document(File.Contents(fParameters("Parameters",1)&"\"&fParameters("Parameters",2)),[Delimiter=",", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Customer Name", type text}, {"Customer Country", type text}, {"Product Sold", type text}, {"Sales Channel", type text}, {"Order Date", type date}, {"Quantity", Int64.Type}, {"Cost", type number}, {"Price", type number}, {"Total", type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Product Sold] = fParameters("Parameters",5)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Order Date] >= #date(Date.Year(fParameters("Parameters",3)), Date.Month(fParameters("Parameters",3)), Date.Day(fParameters("Parameters",3))) and [Order Date] <= #date(Date.Year(fParameters("Parameters",4)), Date.Month(fParameters("Parameters",4)), Date.Day(fParameters("Parameters",4))))
in
    #"Filtered Rows1"

For the dates, I’ve had to use the Date.Year, Date.Month and Date.Day power query function to convert my parameter dates into numbers for the year, month and day.

Now we can easily change any of the parameters and Refresh the query! We can create much more flexible queries with this parameterization method.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Related Articles

Comments

14 Comments

  1. Kevin Lehrbass

    Thanks for this post John! One of my goals this year is to learn Get & Transform. Parameters seem to be extremely powerful yet initially a bit tricky to understand. Your post is very helpful !
    Cheers,
    Kevin Lehrbass
    https://www.myspreadsheetlab.com/blog/

    • John

      There are also parameters built into power query. I’ve been meaning to update the post with the built-in basic way.

      The method above also means you can’t move around the rows in the table since you’re referring to them by row number, so I want to update the method a bit to be row independent.

  2. YOKE-YIN PURCARO

    Thanks for sharing your knowledge. I’m struggling with something I’m working on recently and it’s very similar to this scenario. Mine is I received the order report every month that needs to apply reason code.the order report and reason code file save in same folder. Only the files name change every month. I don’t know if I can apply whatever you explain to situation? If I want to setup the parameters, in my case, that would be 2 columns with 3 row?

    If you could provide a little more information, I would appreciate it.

    Thank you.

    Yoke-Yin

    • John

      It sounds like you need two parameters, so 2 columns and 2 rows. One parameter for the file path, and other for the file name. Then when the file changes each month, you can change the parameter value for the file name and refresh the query to import the new data.

      I’ve added my video to the post, check that out.

  3. Rakesh

    Thanks for the post John. I am executing a SQL stored procedure with arguments which needs to be parameterized using the parameters table. I created a function to fetch the parameters but it doesn’t work and throws a syntax error “token comma is expected”. I would appreciate if you can provide bit more information for this scenario.

    Thank you,
    Rakesh

    • John

      Sounds like you are missing a comma at the end of a line somewhere.

  4. Adrian Service

    John – seems to work BUT keep getting

    An error occurred in the ‘fParameters’ query. Expression.Error: The field ‘Value’ of the record wasn’t found.
    Details:
    Index=1
    Parameter=Folder
    value=G:\Team Drives\AssetWize\Test Report Transformation Adrian S\Old Files

    Any ideas…..Thanks

    • John

      Power query is case sensitive and it looks like the field Value is not an exact match (ie not capitalized) based on what you’ve posted.

  5. Adrian Service

    Thanks – plain as the nose etc etc – you are a lifesaver!

    • John

      No problem!

  6. Mickael Taieb

    Very helpful, thank you very much!

    • John

      No problem, glad is was helpful!

  7. GARRY DUPPER

    I’m just trying to pass a single date in text format to the query in two places. I appreciate your help in building the connection, that works and it’ll see my number (it does put my table name (Start Date) at the beginning but when I try to edit the advanced, I get nothing but errors. And why won’t it let me put a query into the parameters to simply see a single cell?

    • John

      Maybe try the parameters in the query editor. Home tab in PQ editor then Manage Parameters.

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest