- [Neil] Hey everybody, Neil Malek from Knack Training here, bringing another Everyday Office video. And in today's video, I'm going to show you
a couple of techniques that leverage Power Query and macros as well as data validation to turn the data that
you see on the right, which is literally every client we have ever had in this business, into what we see on the left, a filtered result from that data that shows me only the sets of information that I'm looking for and allows me to, with the drop down menu, choose to pick up on a
different set of clients and push on this button to refresh the table and get a different filtered result from this larger data set on the right. The beginning of all this is that we have a gigantic
data set somewhere. This is a CSV file somewhere that's being generated out of a database and it has hundreds of thousands or even millions of customers in it. And so we don't really
want to open this up and re access it every time. So I'm going to go ahead
and close this down and instead go into Microsoft Excel and use power query to pull in the relevant information. I do that by going to the data tab at the top of the screen, getting the data from a CSV file in this example, but it could be just about anywhere, and grabbing the data out
of the customer dataset. Now when this loads up, I do want a filtered result of these, so I'm going to go to transform data down here at the bottom, and do any configurations I need to do. For example, zip codes should always be handled as text instead of numbers. And right here on the
region drop down menu, I'm going to filter it down by literally anything
that you can imagine. I'll go with mountain here for example. Then click okay. Once this is done, what's important is just to take a second and look up at the formula bar, and notice that it's written out how the query worked. You see here it said SelectRows, and it said for every region, we wanna make sure the region is Mountain. That's going to be key in the very near future. What I'll do now is click on close and load, close and load two, and load that directly
into the page we have here, and click okay. Now what I'd like to have is a dynamic drop down menu that has all the possible regions in it. To make that happen, I'm gonna go over here to the resources tab, and I'm going to duplicate the query that I already have. So I'll go over here to customer dataset, right click on that, and choose to duplicate it. So this is going to the exact same dataset but this time around, I'm not going to filter by region. I'm going to uncheck those options here and just make sure that all the regions show up. I'm going to right click
on the region column and tell it to remove every other column other than the region column, with Remove Other Columns. And then finally, with
the remove rows feature, tell it to remove any duplicates. And this gives me a unique list of all the regions that are currently in my client list. As we expand from the
United States into Canada, it's very likely that we will have different regions in our client list, and I don't wanna have
to stay on top of that. So once again, I click on close and load, close and load two, and I load this to an existing worksheet, and to the resources tab. And this will create a table with my list of potential regions. To use this in a drop down menu, I have to create a named range from it. I highlight from Mountain down to West right here. Go to the formulas tab at the top of the screen, and click on this button for Define Name. This needs to be a named range. I will call this range, let's call it AllRegions, just like that. And what I'll do now is I'll come over here to the report tab and create a little picker on the side. So let's say right about here I want to have something that says what region would you like to select? And in this cell, I'll put in a drop down menu. To create drop down menus in Excel, we click on the data tab
at the top of the screen, click on the data validation button, and tell it that we want to allow there to be a list of possible options. And this is where we created
the named range previously. I click on the source box, hit the F3 key on my keyboard, and you can see there the AllRegions name that I just created has been selected. I click okay, click okay, and I'm now able to
choose a different region from the drop down menu, like Midwest for example. Now here's the key. For Midwest to affect this query, we have to put it into power query and it has to be a parameter. It has to be something that another query can use. To put this into power query, we could do a couple of different things. We could turn this into a table, or we could simply create a named range out of cell K2. So I'll click on cell K2 here, click on the formulas tab at the top of the screen, click on define name, and again, give it a good name. I'll go ahead and call it FilterRegion, and hit okay. So now, this named range
called FilterRegion is something that we need
power query to know about. We go to the data tab
at the top of the screen and we pull in the data from a table or range, of course in this case it's a range not a table. This loads it up, and you can see power
query do a few steps here. What you need to do is back up. Right here it says it changed the type and it promoted the headers, we don't want any of that stuff. What we want is for it to pull in simply Midwest right here. And you can see that Midwest is being given the name FilterRegion. Now we do want it to match up with what
we're trying to filter by, which is to say it has
to be the type Text, because in our original customer dataset, the region is again text. So here's my filter region, it's now been identified as text, and you can see that it's
still a table in power query. Here's the last thing we have to do. Right click where it says Midwest, and tell it that you want to drill down. You see how it just turns
into just the word Midwest? Boom, we did it. This is now something that can be passed into other queries as a parameter. You can see the difference over here on the left. It says "Yeah there's this
thing called FilterRegion, "it's just some text." So before we use that, let's click on the home tab up here at the top of the screen, click close and load, and just load this as a connection because we don't actually need to write the word Midwest anywhere. I'm just gonna click close and load, load it as a connection only, and hit okay. And now if we return back
into that customer dataset by double clicking on it, what we're doing is we're
identifying the step where we used the filter. Where it's filtered
rows here on the right, and we can see that it's listed as simply "Mountain" in quotation marks
right up here at the top. Remember, I called mine, FilterRegion, and as soon as I start typing in F-I-L-T, you see that FilterRegion is an option. I'll hit the tab key here to put that in. And when I hit enter, what should happen is it should filter by the Midwest region. I hit enter, and sure enough, it filtered it by the Midwest region. Click close and load, and that's the key right there. We have now selected Midwest and filtered the table by Midwest. I can now choose to select
Northeast for example, and this is the last thing that I want to do here. Right now, you've chosen
something different, and nothing's happened. Well, you can come over here right click on the data and tell it to refresh the data, and you'll see that it picks up on the region being Northeast. But I would love it if we could have a macro that would automatically or with a button press allow us to update this. So we do that in basically
the easiest way to do this is to go to the developer tab at the top of the screen and record a macro, and we're literally only going to record the following thing. Refresh the query. So I click on record macro, I call this RefreshQuery. I'm not going to assign it a shortcut key, but I am going to save
it into this workbook because it's pertinent to this workbook. I hit okay, and all I need to do now is go to the query tab or the table tab and wherever you go, tell it to refresh the query or tell it to refresh all the queries, depending on how you like to do this. I'll click on refresh all here on the table design tab. And now I go back to the developer tab and tell it to stop the recording process. Now how would you like this to work? You could assign that macro to every time cell K2 changes, update the query over there on the left, but I'll just make it
a simple button press. By going to the developer
tab at the top of the screen and going to insert, I can insert a button control. Go ahead and click and drag to draw that button right there at the bottom. And choose the macro RefreshQuery and hit okay. Now let's just go ahead
and rename this button. I'll say something like RefreshTable. And now watch it work. I switch from Northeast to Mid-South, I click on Refresh Table, and it filters the table by Mid-South. And this works beautifully
if you have pivot tables based on this data or pretty much anything else.
