Hi Paula,

Great question. You can certainly use Excel for filtering like this. However, one of the main benefits of using Power Query to get your data is to limit the amount of data you bring into Excel in the first place. We do this by filtering out data we don't need. In large data sets this can make a huge difference to file size and performance.

By applying the filters in Power Query using Parameter Tables enables users without Power Query knowledge can filter the data through an easy to use table.

Another use for Parameter Tables is to enable the query to access a dynamic file path, as shown in session 7.03.

Kind regards,

Mynda

Hi Mynda,

I want to reduce the data I am pulling in in a query by filtering using the results of another query. The results of the first query will be in a table or list form. How do I do that? Can I use a list or table as a parameter table?

The data I am using is from Salesforce which has a "TimePeriod" table. Instead of actual dates it gives each date a unique text ID. I want to use this "TimePeriod" text ID column to pull out only entries with this unique ID from other tables in Salesforce. Therefore using the results of the "TimePeriod" query (which gives a unique list of ID's) as a reference table to filter the data that I am pulling in in other queries. Does that make sense?

Regards,

Amanda

Hi Amanda,

Please watch session 7.01 Parameters Tables for Filtering, and see if you can use that technique for your scenario.

Note: since your TimePeriod is text you'll want to use a similar technique to the one I use for the 'Dates Between', but in your case you will apply a Text filter that 'Begins with' and 'Ends with'.

If you run into trouble please start a new thread and provide some sample data so we can give you a specific solution.

Cheers,

Mynda

Hi Mynda et al,

I really liked this technique, but is there a good way of providing users with an "All" option in the dropdowns?

I've been thinking of:
A) Faking totals into the source somehow, which would be quite tiresome and probably cause trouble.
B) Creating a separate table with totals, linking that into the parameters dropdown query somehow (bit tedious) and then coding an IF THEN ELSE into the M code somehow. Seems like a long way around though.
C) A separate "use filter" parameters table, but the result would be quite ungainly for the user, and again the M code would probably end up a bit contrived.

Your ideas or experience in this would be appreciated!

Thanks, Charles.

Hi Charles,

If you want users to toggle easily between filters then I'd use Slicers in a PivotTable. The idea with Power Query is to limit the data you bring into your model/workbook. If you want all the data and be able to switch views then this should be done at the report level with PivotTables and Slicers.

Kind regards,

Mynda