Parameterized Power Query

Beyond Excel's (BXL's) Methodology:

Parameterize Power Queries

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. I use either named ranges or tables to hold parameters:

Named Ranges

If you prefer names, use Excel's Name Manager to create a one or more names where each name refers to a cell holding a parameter.

Tables

If you prefer tables, place an ID, name, or short description in the table's first column and the value to return in the second. 1 with I use what I call Property Tables which are Excel tables having at least two columns. I call tables structured like this, Property Tables. Property Tables may have many other columns but to be a property table, the first column must contain an ID, name, or description that we can search for and the second column must contain the value we want.

Need help creating a table? See Creating Tables in the references below.

Create Function to Read Parameters

Beyond Excel provides two functions for retrieving parameters from Excel: fncGetNameValue and fncLookup. These two functions are provided within this site. We only need one. The one you want depends on whether you prefer working with tables or not.

fncGetNameValue(Name)

If you prefer names use fncGetNameValue. fncGetNameValue gets the first value it finds in the named range's RefersTo setting. If we have a named range called "Periods" we could get its value like so:

Periods = fncGetNameValue("Periods")

fncLookup(TableName, Find)

I prefer tables so I use fncLookup which acts very much like VLOOKUP(Find, TableName, 2, False). fncLookup has two parameters: the table's name and what to search on. Example:

Department = fncLookup("tblProperties", "Department")

Create Query in PQ to use Function's Results

Below is a sample query using fncLookup. The query also references tblData (not shown but can be found in example workbook in references) which we want to filter using parameters stored in tblProperties (shown below). By changing values in tblProperties and clicking on Data > Refresh All we can refilter tblData to our hearts content.