How To Parameterize Your Power Query

Лист "Query Parameters",Таблица "Parameters"

Лист "Data",Таблица "Office_Supply_Sales_Data_2017_Q1"

Posted by John | Nov 18, 2017 | Get & Transform, Power Query | 14 |

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.

Download Example File

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.

Table of Contents

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.