Please note javascript is required for full website functionality.
MVP

Blog

Power Query: Interactive Query

10 April 2019

Welcome to our Power Query blog.  This week, I look at how to make a functional query more interactive.

Last week, I created a query that could receive date parameters. 

This week, I amend my query so that it’s more flexible for users by allowing the parameters to come from the worksheet. The first step is to put the dates into the worksheet:

I don’t need to worry about making this a Table, as when I extract this information using ‘From Table’ in the ‘Get & Transform’ section on the ‘Data’ tab, Power Query will automatically convert it to a Table.  Having said that, it’s a good practice to convert to a Table first, as you get to choose the name of the Table before importing.  

Having said that, let’s pretend I ignored my advice:

I confirm the defaults to extract the information to Power Query.

Now I need to connect these parameters to the query I created last week, ‘Expenses_Folder’.  To do this, I create a new custom column from the ‘Add Column’ tab:

Since ‘Expenses_Folder’ is a function, I can use the M code 

= Expenses_Folder([datefrom], [dateto]) 

which calls my original query as a function, using the dates in the columns in my current query.

The content of my new column is a Table.  I can use the ‘Expand’ icon next to the title of the column to view the contents of that Table.

As usual, I don’t want to use the original column name as a prefix (that would make for very long column names!).  I select all columns in the Table and choose to expand.

All the data from my original expenses information is shown where the date falls into the range I have specified.  I remove the datefrom and dateto columns and save the query by using ‘Close & Load’ from the ‘File’ tab.

I have renamed my new query ‘Interactive Expenses_Folder’. 

If I go back to the original data worksheet, I can change the dates and see what happens to my query.

Once ‘Interactive Expense_Folder’ is refreshed, I can see that the expenses from May are displayed.

This is a quick and easy way to reduce the amount of data that I have to deal with.

 

Come back next time for more ways to use Power Query!

Newsletter