Where has Matt been?
Before teaching you how to pass an Excel parameter to Power Query, I’ll have a few words. Regular readers of my blog may have noticed I have been missing in action over the last month. I have been super busy with the PUG World Tour, delivering a lot of training, finalising my new book Supercharge Power BI, and also taking a couple of weeks vacation.
Side Story: Power BI Training Has Never Been More Popular
I have been delivering Power Pivot/Power BI training for almost 4 years now and the demand has never been greater. My last 5 live training courses in Australia have all had record numbers of participants, and my current semester of live online training (Supercharge Power BI) has 34 active participants. I’m super excited what the future holds for this fantastic product, and it just keeps getting better and better.
Pass an Excel Parameter to Power Query
I was delivering training yesterday in Sydney and one of my students (Stephane) asked me how to pass a parameter from Excel to Power Query. I have written previous articles about this principle and power query filters at PowerPivotPro.com but thought it would be good to blog about how simple it is to do this.
Sample Scenario – Extract Invoices for Selected Date
I am going to demonstrate the concept using the Adventure Works database. In the example below, I have set up a single cell in Excel that contains a date which is then passed to Power Query. When I refresh the query in Excel, the results table is updated with all the orders from the database for the selected date. Note: There are other ways to do this, such as loading all records into Power Pivot and extracting them from there on demand. However there may be people that want to do it this way for what ever reason.
Set up a Parameter Table
The first thing to do is set up a single cell table that contains the date of the orders to extract. Then click in the cell (1 below), select data (2), from Table/Range (3). Note this is Excel 2016; the UI is slightly different for Excel 2010/2013.
The next step is super simple and is the secret sauce to this process. As you probably realise, the data loaded is a table with a single column and a single row (see below). The table therefore has a single cell that contains a single date. It is not possible to use the date in this tabular format, so it needs to be changed before the date can be used in another query. To do this, simply right click on the cell (1 below) and then select drill down (2).
After completing this step, the table is removed and the actual value is returned by this query (see below).
Now that this is a value (the scalar value shown in 1 above), this query name (2 above) can be directly used inside another query.
Extract the Required Data for a Random Date
The next thing is to write a query to extract the required data from the database. First set up the query as required, then apply a Power Query filter from the drop down box (1 below) for any random date (2 below).
This creates a query step as shown below – the query step directly references the date selected above. Note: the formula bar needs to be turned on to see this (View\Formula Bar).
It is now very simple. Just manually edit the hard coded date in the code above with the name of the query that returns the date coming from Excel.
Now close and load. Set the DateSelected query so it doesn’t load and the Sales query so it loads to the original spreadsheet below the date parameter table.
It’s not easy to find how to set a query to not load in Excel after the initial load settings have been configured. Simply right click on the query (in the query pane), select load to, set do not load.
To refresh and extract a different date, simply change the date in the parameter table (1 below), then right click in the results table (2) and then click refresh (3).
The query then runs and the new data is returned to Excel.
A Final Word
This is the most simple parameter table you can create – a single value. Once you understand the principle, you can let your imagination run wild with other things you can do here. If you want to fast track your learning so you can leverage the power of Power Query filters and all about this tool, take a look at my self paced online Power Query training course. This training has over 7 hours of video explaining concepts like this. Once you know how, you will be able to save time and do fantastic things you never dreamed possible.