I've attached an image of parameters I am using in an excel spreadsheet. I cannot attach the actual spreadsheet for privacy reasons unfortunately. I am using these parameters to filter down a large query from a SQL database. Is there a way to enter or select multiple values for the parameter rather than just having to input them each one by one? For example, I'd like to be able to choose both 2018 and 2019 for the Year rather than having to type them in and look at them separately.
I appreciate any insight on this or even if you feel there may be a good way to at least begin to approach this, I'd be happy to hear your thoughts. (i.e. Using VBA or Power Query Advanced Editor to code a solution). Thanks in advance!
Hi Nick,
Welcome to our forum.
If you want to filter by multiple year parameters you need to create a list containing those years, then you can use the List.Contains function in your Fact Table query and use this formula:
= Table.SelectRows(#"Name of last step", each List.Contains(YearParameterTable , [YearColumn]))
Hope that points you in the right direction. If you get stuck, please create a mock up sample file with a small amount of dummy data and your query attempt and we'll help you troubleshoot.
Mynda
Hi Mynda,
Thank you for the suggestion! I'll work on creating a dummy file and test that out.
Nick
Hello,
I've created a dummy file, which is attached. Ideally, I'd like to be able to filter on multiple values in each cell of the Parameter table (on the Query with Param Sheet). I did create lists as you proposed, and I created connections to those lists. If this is likely the only way to filter by multiple values, that's fine too.
The only part of your formula I was getting stuck on was the [YearColumn]. I'm not sure what to put there. Do I need to rename the list columns and create connections for those?
Thank you,
Nick
Hi Nick,
Thanks for sharing your file. When you first asked the question I didn't realise you wanted to filter multiple fields based on multiple parameters, I thought you only wanted to filter the Year field on multiple parameters.
In the attached file I've inserted the filters for the 4 different fields based on your parameter tables. Note: you need to remove the items from the parameter tables on the Lists sheet that you want filtered out. You cannot simply filter these tables in the Excel worksheet, as Power Query will still include items that are in rows filtered in the Excel worksheet.
Hope that makes sense.
Mynda
Thanks so much Mynda! That's very helpful.
Hello again,
Bear with me as I am a fairly new user of Power Query. I really appreciate the help with creating the list parameters, but I'd like to go a step further. If the parameter lists are left blank, I'd like the query to return all values for that parameter. For example, if I were to clear out the values for the Year list, I'd like the query to only filter by the remaining parameters. Is there a way to do this?
Thank you,
Nick
