Power Query Parameter Lists - Filter by All Values when Parameter is Left Blank

[nickben]

[FONT=&quot]Hello,[/FONT]
[FONT=&quot]Please see the attached file. I've built parameter lists in Power Query, 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?
[/FONT][FONT=&quot]
[/FONT]View attachment Parameters Using List Tables.xlsx

[FONT=&quot]Thank you,[/FONT]
[FONT=&quot]Nick[/FONT]

 

[Ken Puls]

Hi Nick,

The trick is to - instead of applying a simple filter - check if you should or not. Then apply a filter if you should, or just refer to the previous step if you shouldn't, like this:

    #"Filtered Rows1" =
        if
            List.Distinct(Year){0} = null  and List.Count(List.Distinct(Year)) = 1
        then
            #"Changed Type"
        else
            Table.SelectRows(
                #"Changed Type",
                each List.Contains(
                    Year,
                    [Year]
                )
            ),

 

[Ken Puls]

I've updated the code above to make it more robust. It will now check if there is only one item in the filtered list AND if that item = null.

Example (with updated code for all tests) is attached.

 

[mikemck]

I'm feeling pretty stupid. No matter what I filter , it just keeps loading the same 700 rows and nothing gets filtered.

What am I doing wrong here?

 

[Ken Puls]

Are you removing items from the List Parameters worksheet? (The Filter Lists section threw me a loop, as those tables aren't connected to the Power Query.)

If I delete all values from Years (except one) on the List Parameters worksheet, then refresh, it seems to work nicely.

 

[mikemck]

Ah, I did not realize the values needed to be deleted from the list instead of filtering. The filter buttons threw me off.
I've got a long ways to go, but I'm making progress.

Thank you very much.

 

[Ken Puls]

Ah... I didn't realize you were trying to filter. Okay, we can make that work, but it is going to take a little trick.

We need to add an "Include" column to each table (don't worry, you can hide it afterwards).

For Years, the formula will look like this:

=AGGREGATE(3,3,[@Year])

Don't worry that all columns show up as 1. What it is doing is showing a 1 for visible rows, and a 0 for those that are hidden.

Now go into Power Query and for each of your Lists, add a step that filters to where the Include column = 1. (You'll have to set a manual filter if all rows are showing.)

One thing though... the lists either need to be stacked vertically or on different worksheets. If they are beside each other on the same sheet, one table's filter will hide another table's rows and cause an issue.

Updated file attached.

 

[nickben]

Thank you so much Ken! Your first solution is just what I was looking for, and I appreciate you also sharing the Filtered Tables solution. I will definitely be able to work well with both of these. Really appreciate it!

Best,

Nick