Excel Parameters in Power Query

Overview

For certain modelling situations, you may want to pass a collection of parameters from Excel to Power Query. In this article, we explain how to do this, passing the given parameter in, as a variable:

Extracting orders of particular date

Suppose you have the following Order data. You would like a single cell in Excel, which holds a date, to be passed into Power Query, so that when the query is refreshed, it updates to show only those orders of the passed in date.

The following steps explain how to do this:

Steps

1. Set up a Parameter as a table

  • Create a single cell table that contains the date of the orders to extract.
  • Click in the table ➔ click the From Table / Range icon under the Data tab, in Excel

The table should load into Power Query as a single celled table. The date cannot be used in this tabular format, so needs to be drilled down on. To do this:

  • Right click the cell, and choose Drill down

Now only the actual value should be returned, as a scalar value, like so:

Now that this is a value (the scalar value, can be directly used inside another query.

2. Extract the required data for any random date

Next, create a query to extract the required data from the database.

  • Load the underlying data into the Query Editor
  • Apply a filter from the drop-down box, for any random date, as shown:

This will create a query step that references the date selected above, with the following code.

  • Now, replace the hardcoded date in the code above, with the name of the Parameter query created before
  • Select Close & Load, so the query loads to the original spreadsheet.
  • To check that everything works, change the date in the Parameter table, and refresh the query table again.

The query should return orders with a date that match the parameter table!