How to add a user interaction into Power Query

Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals brought to you by CPA Australia.

Neale Blackwood CPA:
About the variable power query function
Welcome to the Excel Tips podcast. My name is Neale Blackwood, and in this episode we're going to look at how we can add interaction, user interaction, into a Power Query.

Now, Power Query is the best way to import data into Excel. So you can import data from CSV files, PDFs, pretty much all the databases out there, and it allows you to bring all of the data together in one place in Excel so that you can then create your reports. Unfortunately, one of the limitations of Power Query is that it doesn't interact with the user. So if you want to make a change to a Power Query or adjust it, you've got to edit the query, which you don't really want people getting into the queries and changing it. You'd rather them change, for example, an entry on a spreadsheet and have that entry update the query. That's what I'm going to explain how to do in this podcast.

So, we need to create four separate queries, and there's going to be two types of queries within those four. Also, I'm going to explain an idea of creating staging queries. Now, a staging query brings all the data that you want from whatever data set you're working with. It brings all the data into Excel, but it doesn't put it in a table on a sheet. This is a special type of query called a connection-only query. The idea behind it is that you, in this staging query, you bring all of the data in, you do all of the data cleansing that may be required. Now this includes things like fixing a date, maybe populating blanks, maybe removing spaces from codes. These are all typical errors that you get in real-life data, and Power Query can solve most of those issues.

In the staging query, you bring all of the data, all of the columns into this connection-only query, and that staging query then becomes the source of the other queries that you're going to use. So in our case, we've got a sales data CSV file, and that has been imported into the Excel spreadsheet using a staging query. What we want to do with this sales data is we want to limit what comes into our output table based on state. So we want the user to select a particular state, and then that is the only data that will come in from the sales data CSV. We'll only bring in, for example, New South Wales data into the spreadsheet.

So as I mentioned, the staging query brings in all of the data as a connection-only query, and we're going to use what's called a reference to that staging query to create a sales data query, which is going to filter by the state column and then output into a spreadsheet and a table only the filtered rows.

Implementing the query function
Okay, so the mechanics of it is that you right-click the query in the task pane that appears on the right-hand side of the screen, and one of the options is called reference. So what that does is that brings the output of the staging query, and that becomes the input of the next query when you use reference. So we can then use a reference to the complete data and then filter by the state column, and that becomes our output. That query is actually going to create a table in the spreadsheet. So that is the second type of query. So the data is displayed on a sheet in Excel.

The connection-only, you don't see the data in the spreadsheet. The connection-only query, the data is saved basically to the memory, to the RAM on your computer. So it's in there, but you can't see it. So the sales data query is going to produce the table that we are ultimately after. But the problem is that you have to manually select the state in that query to output a particular state.

Now that's where we want to add the interaction. So what we're going to do is we are also going to create another query that's going to give us a list of all of the states that are within the staging query. So again, you just use that reference technique, and you take the staging query. You delete all of the other columns except the state column. Then there's a built-in option in Power Query to remove duplicates.

So that gives us a list of unique states, and we can output that. So again, that's an output to a table in the spreadsheet. Once we've got that, we can give that particular table reference a range name. So range names and table names are very similar. So the tables that are output by Power Query come with an automatic name of them. It's basically the same as the query name.

Using a range name for a table
We can also use a range name to refer to a formatted table. When you do that, it has the advantage that you can use that range name in a Data Validation drop-down. So we can create a dropdown list based on the states that are in the CSV data.

So if a new state is added in the data, then it will appear in our listing. The shortcut to open up the Data Validation is you press the Alt key, the D key, and then the L key, and that will open up the Data Validation dialogue. To create a drop-down, we use the list option, and in the Source box, we use that range name. So whatever range name you created for the list, you can put that into the Data Validation dialogue into the Source box.

So that gives us a cell that the user can use a drop-down to select a particular state. Now we need to use a range name again. So whatever cell that is, you give that cell a name. Now in the case of the example we have, I have used State Filter as the name for that particular cell.

Using a query to read the variable input
We can then create another query. So we've got a staging query that brings in all the data. We've got a sales data query, which brings in the filtered data and actually displays it. Then we've got a state query, which gives us our list of states. The fourth query is going to capture whatever's in that particular input cell, the drop-down cell. So that cell is going to contain the state that we want to filter by.

So all you do is select that particular cell, click on the data ribbon, and there's an option to create from table or range. So you just click that. When that opens, pretty much all you need is the source step that will appear on the right-hand side. So there's two extra steps that are automatically added. You can actually delete those, and it just leaves the source step. Then you right-click the value that's displayed on the left-hand side of the screen. In the case of the video, we did New South Wales, and there is a drill-down option. So when you drill down, that gives us a value.

So in this case, NSW for New South Wales. So the query is returning a single value. So that query's name works out to be the same as the range name that we use, which was State Filter. And so, once you've created that fourth query, all you need to do is go back to the filter query that we did that we called Sales Data. And where you go to the last step in the queries, there is a filter command in the formula bar. Now the formula bar in Power Query can sometimes be hidden. If it is, all you need to do is click on the view ribbon tab in Power Query and just tick the formula bar option.

Okay. So in that formula bar for the last step, it'll say equals New South Wales in quotation marks. So all you do is remove the quotation marks and New South Wales, and you replace it with State Filter. So that's the query name, and that represents whatever is in that cell that we named State Filter as well. So that's it. The system is ready to use. You just need to close and load to close down the queries that you've edited and created.

Refreshing the power query
So you can make a change in the cell on the spreadsheet. Now the thing is, Power Queries do require a refresh, so they're like pivot tables. The command to force a refresh is Ctrl-Alt-F5. So you make a change to the dropdown list, you press Ctrl-Alt-F5, and when you go look at the data, it will be filtered by whichever state you have chosen.

Recap and use cases
So this is an example of how you can combine queries and capture entries that are in range names, and then use them together to create interaction with Power Queries. This allows you to then delegate the Power Query because the user doesn't have to go in and edit the query. They can just change cells on the spreadsheet to update the entries within the Power Query.

So Power Query, really, really powerful. Range names are also really, really powerful. Combining them makes interaction much easier. If you want to check out how to do it and all of the mechanics involved, just check out the companion video on the INTHEBLACK website, and you can see exactly how it's done. I hope you found that useful. Thanks for listening.

Jacquelline Blondell:
If you're enjoying this podcast, you should check out our in-depth business and finance show, INTHEBLACK. Search for INTHEBLACK on your favourite podcast app today.

Garreth Hanley:
Thanks for listening to Excel Tips, a CPA Australia podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing this episode with colleagues, clients, or anyone else looking for the best Excel tips and tricks. To find out more about our other podcasts and CPA Australia, check out the show notes for this episode. We hope you can join us next time for another episode of Excel Tips.