Power Query vs. FILTER function

Craig,

Sample avail. here. You'll see I stored the drop-down values in a Table and created a Connection only to that Table. So the why is the following: if the search the Net on how to pass a value from an Excel workbook to a query, you'll find different ways to do this, and all are valid ones. They usually suggest doing this in 2 steps:

1. Define a name range at the workbook level - that you be one your drop-down to "capture" its value
2. In the query do something like (that's one way, as I said before there are others):

myParam = Table.FirstValue(Excel.CurrentWorkbook(){[Name="myDefinedName"]}[Content])

No problem this works

Now, if you have more that one value to pass to a query, storing those values in a Table has at least 3  benefits:

1. Faster than creating n Defined names + it's a structured approach + if you hide the sheet containing that Table you reduce the risks that someone deletes your Defined names
2. Easier to update your query if you later need to get additional values from your workbook for your queries
3. Most important one is efficiency as you read your Table (and if you read it more than one time you can even Buffer it as it's small one), instead of making n calls like Table.FirstValue... mentioned above

Hope this all makes sense

No idea what your level of expertise is re. Power Query. So suggestion is you look at the code of query QueryResult and let me know if you have questions. You will see that I made 1 parameter optional (ClientType)

Hi Craig

Given you're open to a non real-time solution (query must be refreshed. It can auto refresh every n minutes) if it was me I would opt for the query option. Main reason being that it returns a Table, and with that table you easily filter, sort…

You can do the same with FILTER but if you want to SORT the output you'll have to change the formula. Also, Conditional Formatting can be applied to a Table, not really on the output of a FILTER as the range the CF rules apply to isn't dynamically adjusted

That being said this all depends on what view(s) you need for your business and how often you need to change the criteria to generate these views

One thing I don't understand though. In your previous request you talked about drop-down lists. Except if I missed something I haven't seen any in the workbook you uploaded. Have you given up on that part or is this a non-implemented feature yet? (it is possible to use values from a spreadsheet as query parameters…)