Hi,
I want to filter a PQ table from the contents of a separate Excel table. I can do this by creating a list from the Excel table and using either List.Contains or a Right-Outer merge. So far, so good.
However, I'd like to be able to filter the Excel table via its filter button and have the filtered values (only) populate the list. However, the PQ list always contains every member of the table, regardless of the applied filter.
I could use a helper column in the table to include or exclude each row, and use that when creating the PQ list, but I wondered if there's a simpler way to achieve what I want, without that.
Many thanks in advance.
Pieter
How about uploading a sample data worksheet and then showing a mocked up solution. We only need 8-15 records.
Hi,
I've attached a sample as suggested. The orange table is the original list of orders; the blue table is the filter list, which I want to form the basis of my list, the (incorrect) result is shown in the green table. The purple tables use a helper column to produce the result I want (result with helper).
Hope this helps!
Hi Pieter,
You can automate the helper column so that you don't need data validation with Yes,No on every row. Use SUBTOTAL to count the number of fruit on each row, ignoring hidden rows. The formula would be:
=SUBTOTAL(103,[@Name])
Or if you prefer, you could use AGGREGATE like this:
=AGGREGATE(3,5,[@Name])
When you filter to show only Peaches, the rows for Apples and Pears will become 0 in the helper column. In stead of filtering for rows with Yes in PQ, now filter out the zeros and you are all set.
The 'grey' tables in the attached file demonstrate this.
Riny
Hi Riny,
That's perfect - many thanks indeed - and a great way to bring together PQ and Excel tables. Perhaps the developers might introduce an "Include/Ignore Hidden Rows" sometime in the future, but this does exactly what I need.
Pieter
