Parameterized Power Query
Post date: Apr 12, 2018 9:16:44 PM
Power Query (PQ) is now THE tool for importing data into Excel. It replaces MS Query. PQ has many advantages over MS Query, such as combining data from multiple systems. But MS Query has one important feature missing in PQ: parameters (integrated with spreadsheets). This post provides a simple interim solution until Microsoft adds this feature.
Watch this video for a 5 minute overview of this process then come back for more detail
Code with sample data can be downloaded. See references at bottom
For the purpose of illustration handling errors has been ignored in this post but not in the sample workbook.
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:
Need help creating a table? See Creating Tables in the references below.
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.
Power Query is case sensitive so pay close attention to upper and lower case in everything except comments.
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.
Excel.CurrentWorkbook() is a table with two columns: Content and Name. As far as I can tell, Content is always a 'Table' data type but it considers Excel's tables and all defined names as tables too. Power Query creates column names, starting with the name "Column1" for each name's values based on the shape of its values. For example, if a name has just one value, it will have one column named "Column1". If a name covers a cell range of 3 columns and 3 rows its power query table will have three columns: "Column1", "Column2", and "Column3" and three rows: {0}, {1}, and {2}.
All code lines must end with a comma except the last code line before in
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.
Result is a single value, not a table.
Power Query starts counting rows with zero as the first row; thus, From{0} is From's first row.
in
Indicates our procedure's end and that what follows is what will be returned.
The code line before in must not end with a comma.
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..
The code line before in must not end with a comma.
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.
The code line before in must not end with a comma.
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:
Q.
A.
Why do you not use Power Query's default naming such as "Source" and #"Select Rows"?
We can name these results whatever we like. I'm writing this primarily for developers and many developers are familiar with SQL so I'm using SQL like words to help make this feel more familiar.
