Parameterized Power Query

Post date: Apr 12, 2018 9:16:44 PM

Watch this video for a 5 minute overview of this process then come back for more detail

About Parameters

Parameters give us better control over what we import into Excel. I use parameters to get only the data I need from large data sources. These large data sources would easily overwhelm Excel's row limitations if brought into Excel without filtering out unwanted records. But if I use parameters to import only transactions for a specific customer over a specific date range Excel has no problems presenting it for analysis. And with parameters, merely changing a few numbers in an Excel table loads a completely different customer for an entirely different date range with no changes to PQ. Read on to learn how.

Process Overview

• • Create a place in Excel to hold parameters

  • Create a function in PQ to read parameters

  • Create a query in PQ to use the function's results to filter out unwanted rows.

Create Place to Hold Parameters

The first thing we need to do is create a place in our Excel workbook where we enter our parameters. For this purpose I'd like everyone to consider having a standard table in their projects: tblProperties. An example is shown below.

In my projects I use tblProperties to hold in one place all single value user inputs. Many modelers might call these "Assumptions". I prefer the more generic term "Properties" partly because some of what modelers call "Assumptions" are "Knowns" and not assumptions at all, and partly because this table can include technical items such as file locations or anything else for that matter. Placing properties in a table groups them into one place making them easier to keep track of. The table has three columns.

• Property is a very brief name for the property. We can add as many properties as we like and name them whatever we want as long as each entry here is unique.

• Value is what we change in order to change what Power Query loads to Excel

• UOM is the value's units of measure or additional text to help document the property. It is basically a comment.

Be sure to name this table tblProperties:

OPTIONAL!

I prefer turning tblProperties entries into names by selecting the first two columns then using Excel's Create From Selection option. This gives us the option to work with parameters directly by name or by looking them up by name.

Create Function to Read Parameters

We will create two PQ functions. We only need one so we can chose one or the other, or both if we like. One gets parameters by their name if we used the optional step Create From Selection. We will call this function: fnGetName(). We will call the other function fnProperty() and it will behave much like using Excel's VLOOKUP function. Below is a VLOOKUP version of what I mean:

=VLOOKUP("Organization", tblProperties, 2, False).

Creating either function is simple and both start the same way. We start by opening a blank query. How we open a Blank Query depends on which version of Excel we have.

Excel 2010 and 2013

In Excel 2010 and 2013 Power Query can be found in its own tab. Click From Other Sources > Blank Query.

Excel 2016

In Excel 2016 Power Query is part of the Data tab in a group box labeled Get & Transform. Get Data > Blank Query

fnGetName()

When Power Query launches it brings up the screen shown below. Under Query Settings (right side) change Query1 to fnGetName then click Advanced Editor (top left).

In Advanced Editor type this function.

Code Explanation

/* ... */

/* designates a multi-line comment's start. */ designates comment's end. Everything in between is documentation.

(Name as text) =>

This defines the input to our function. The parentheses are required.

• Name is the function's parameter name.

• as text declares Name contains character data which, in this case, is the Excel name.

• => is Power Query's "goes to" symbol indicating our parameter is sent to our function.

let

Indicates our procedure's start

From = Excel.CurrentWorkbook(){[Name=Name]}[Content],

Get the current Excel workbook, find the object named Name and put its contents in From.

Result = From{0}[Column1]

Get From's first record and put the value from Column1 in Result.

The curly brackets next to From designate which record to get. The trailing square brackets designate which field, or column to get from that record.

in

Indicates our procedure's end and that what follows is what will be returned.

Result

Returns what was placed in field Result

fnProperty()

fnGetName() is very simple, just two lines of procedural code. fnProperty() also has two but pulls data from an Excel table instead of an Excel Name. fnProperty() also starts as a Blank Query and since we are already in Power Query we create one by opening Power Query's Home tab then clicking New Source > Other Sources > Blank Query. Just as we did before, under Query Settings change Query1 to fnProperty then click Advanced Editor (in PQ's Home tab). In Advanced Editor type this function:

Code Explanation

/* ... */

/* designates a multi-line comment's start. */ designates comment's end. Everything in between is documentation.

(Property as text) =>

This defines the input to our function.

• Property is the function's parameter name, which in this case, is the property name in tblProperties.

• as text declares Property contains character data.

• => is Power Query's "goes to" symbol indicating our parameter is sent to our function.

let

Indicates our procedure's start

From = Excel.CurrentWorkbook(){[Name="tblProperties"]}[Content],

Get the current Excel workbook, find the object named tblProperties and put its contents in From..

Result = From{[Property = Property]}[Value]

Find the row in From where the Property column's value equals our Property parameter and put that row's Value column value in Result.

in

Indicates our procedure's end and that what follows is what will be returned.

Result

Returns what was placed in field Result

Create Query in PQ to use Function's Results

Below is a sample query using fnProperty. It references qryResults which we have not included in this post but a similar query can be found in the sample workbook which can be downloaded from its reference link at the bottom of this page.

Code Explanation

From = Excel.CurrentWorkbook(){[Name="qryResults"]}[Content],

Sets From to the content in table qryResults located in our current Excel Workbook

NOTE! qryResults is in the sample workbook. Download it.

WhereYears = Table.SelectRows(From, each [Year] >= fnProperty("From") and

[Year] <= fnProperty("To") )

Sets WhereYears to From's rows where From's Year column values are greater than or equal to tblProperties From value and less than or equal to tblProperties To value.

Summary

Microsoft will eventually integrate parameters with Excel, but until then we can simplify Power Query parameters by standardizing our approach on tblProperties and one of these functions.

FAQ:

References