PDA

View Full Version : Dynamic Filter in Power Query



Sgligori
2016-04-11, 06:18 PM
I created an Excel dashboard for end users to see the details of their particular category. The data is based off of a table with millions of rows in Power Query.

Is there a way I could include a filter for end users to select their category and have power query filter for only that data to bring over into their report?

Brian Smith
2016-04-12, 05:02 AM
Please see attached for one way to do this.

The yellow cell is a drop down that is linked to the filter in the query. All that I did was to create that simple parameters table and drill down to the value that is selected. Then that query becomes the filter value in the "ThingsTable" query. So to the user they would just need to select the drop down and refresh.

Brian Smith
2016-04-12, 05:05 AM
That did not seem to post the attachment - let's try again....

Sgligori
2016-04-12, 03:38 PM
This is EXACTLY what I wanted! THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!



That did not seem to post the attachment - let's try again....

Brian Smith
2016-04-12, 03:57 PM
You're welcome. Glad it is helpful. Chris Webb (I think) had a neat way of doing this with a combination of slicers and using the aggregate function to determine when a row in a table was visible. You could also link the query to power pivot and apply slicers tof it.

jewelmary
2019-10-18, 07:56 AM
Hi Brain,

I also have a similar usecase where i have a master list of all companies records and i need to filter out top (n) applications of each company. Please bear in mind that the support cap varies for each company. Can we accomplish this using power query. I am new to power query.Kindly advise

Regards
Jewel

AliGW
2019-10-18, 02:44 PM
Please start your own thread.

RonaldLieuw
2019-10-19, 05:55 PM
Never mind, I found the solution. I needed to refresh the table!

Brian, hi.
It this functionality tied to an Excel version? I am using 2010 and the filter in the yellow cell does not work!
I