Filter by Parameter and Dynamic Drop-Downs with Power Query

Filter by Parameter and Dynamic Drop-Downs with Power Query | Everyday Office

Рет қаралды 44,998

4 жыл бұрын

Power Query is Excel's new ETL (Extract, Transform, and Load) tool. It's used to pull data in from many different sources, then 'transform' the data in any ways necessary before 'loading' it into your spreadsheet. As you're transforming the data, you may want to provide Power Query some variable information - this can be a 'parameter.' In this exercise, we create a dynamic drop-down menu from the data, then use the user-selected option as a parameter to filter the data. With a simple macro to refresh the data, you now have an easy-to-filter subset of the larger dataset you're bringing in.
Everyday Office is the media arm of Knack Training, a software and professional development training and consulting company headquartered in Orlando, FL. More content can be found on:
- www.knacktraining.com/blog/
- www.knacktraining.com/podcast/
Check the playlists on the Everyday Office channel for more tutorials in the areas you are having the most trouble with.

Пікірлер: 44

@quinnjohnson1307 4 жыл бұрын

Great job on this video! You really saved me and helped me understand something that was super confusing!

@Ednut19 2 жыл бұрын

Excellent video! You’ve simply explained what could have been a complicated topic. Thanks

@Thor_Underdunk_Caballerial 3 жыл бұрын

This is incredibly helpful, Thank You.

@arturgarncarz3631 Жыл бұрын

Just found your channel. It's a true gem! Regards from Poland

@vsrblr 2 жыл бұрын

fantastic job, nice demonstration

@setortransporte9624 Жыл бұрын

Very, very, very good! Thanks very much, helped me a lot.

@SeemaSharma-lj3cf 9 ай бұрын

EXTREMELY HELPFULL, thanks for sharing it 👌👍

@Luciano_mp 2 жыл бұрын

Great...thank you!

@AlQur2anAlKareem Жыл бұрын

Thank you So much!!

@yamenyehia1831 4 ай бұрын

Thank you for this informations

@tatsuya_tech 2 жыл бұрын

Its work. Thanks you so much.. hatur nuhun pisan mang.

@Alwahashi-tech 9 ай бұрын

جزاك الله خير

@khalidmajeed2886 Жыл бұрын

great sir

@fennecfennec5234 9 ай бұрын

brilliant

@SKEDURINRAJA 2 жыл бұрын

Great

@MarjiEmad 2 жыл бұрын

It is a great one, but what if I want to filter on 2 or more regions?

@mirrrvelll5164 3 жыл бұрын

Amazing video, but one thing what I experimented. there on 6:38 where you changed 123ABC data type to text - is not actually neccessary, you can jsut continue use drill down to the value, it will give you text anyways... I already used it couple of times in other examples that I had =) all in all, super amazing thing with PQ, so helpful to automate almost everything.

@Hurmali1 10 күн бұрын

Looks like the slicer function??

@davidp3228 2 жыл бұрын

For those asking how to get the whole table if criteria is "" or empty the below worked for me. L3Filter is my named range. = Table.SelectRows(#"Removed Columns", if L3Filter is null then each true else each ([L3 Name] = L3Filter))

@cabezon667 Жыл бұрын

#"Removed Columns"?

@brendasteven9339 10 ай бұрын

I have a question about the named range for Region (starting at 3:55 in the video). What if that list varies? For example, next time you run the report there are now 15 regions instead of 10? Your named regions only goes to row 10. The new 5 regions would not show in the drop down list.

@zarkotripunovic7507 4 жыл бұрын

Great video! I have one question: how can we get whole table if criteria is "" or empty, just like when using advanced filter in spreadsheet?

@KnackTraining 4 жыл бұрын

Good question! You'd have to add a little logic to make that happen. Added it to the 'future videos' list.

@zarkotripunovic7507 4 жыл бұрын

@@KnackTraining I figured it out but not 100% what I was looking for. This is my code: #"Filter1" = Table.SelectRows(#"Filtered Rows", each if BarCodeBoce="Svi" then not Text.StartsWith([BarCode broj],"Svi") else Text.Contains([BarCode broj], BarCodeBoce)) "BarCodeBoce" is my variable, while "Svi" is word that 100% won't be in the name of "BarCode Broj"

@davidivanov9605 2 жыл бұрын

Very helpful video, one question though. Is it possible to have more than 1 values as a criteria, for example Northeast and Mid-South together?

@vacilando86 Жыл бұрын

Yes,It is. You can create one more than parameters in Power query just like creating "Region" parameter

@TheRedTbone 5 ай бұрын

Surely it is quicker to read the csv in with power query as in the video, format, then load the whole table, then user a slicer for region? I know the table is large but excel handles millions of rows but its a lot simpler and deals with any new region.

@kennethvela2784 2 жыл бұрын

For this, I just use the filter in the table

@JenMayB Жыл бұрын

How do I get it to clear the filter so that all data returns if I want it.

@rakshitharamesh3161 3 жыл бұрын

What if my data source is external and it keeps changing every day so in your case what if the region column gets updated with new data or may be removed. Thanks!

@samanthasalazar9221 3 жыл бұрын

Let me know if you have this figured out! TIA

@arne.munther Жыл бұрын

Couldn't this be done with Slicers ?

@baule2206 4 жыл бұрын

I have the same question, how i can see the whole table if the creteria is empty?

@KnackTraining 4 жыл бұрын

Good question - you'd have to add a logical function to translate it. Added to the future video list!

@zohaibazam7105 3 жыл бұрын

Is it possible to show all data if our parameters field is blank. I mean if region Column is blank it must show up all the regions.

@KnackTraining 3 жыл бұрын

You'll need to build a logical test into the query - we're on it!

@valapo80800 Жыл бұрын

Macro via Worksheet_Change?

@zzzzzzzzzzzzzzzz9 3 жыл бұрын

I'm trying to understand the benefits of using parameters. Could you not just dump the data into a piviot or table and use slicers? What's the difference

@KnackTraining 3 жыл бұрын

If your data has 10k records, no big deal. However, your dataset might be HUGE - and now you're only pulling what you need. There are a lot of potential performance gains by filtering on the query

@zzzzzzzzzzzzzzzz9 3 жыл бұрын

@@KnackTraining would you consider between 100k and 400k, and growing, large?

@KnackTraining 3 жыл бұрын

Absolutely - Excel CAN deal with that data, but you're going to start experiencing the lag around that point.

@subhamsingh455 3 жыл бұрын

Sir, how can i filter clients name in power query with a list of limited clients kept in other table/sheet. For example, if there is 100 clients in power query table, i want only interested 10 particulars clients with their data, then how can i keep filter in power query. Thank you sir....

@KnackTraining 3 жыл бұрын

Have two queries in Power Query - one of them the limited client list. Then, use Merge Queries as New, and choose an INNER join. This will keep only the matches. ANTI join would keep the non-matches.

@subhamsingh455 3 жыл бұрын

@@KnackTraining Thank you... sir