Results 1 to 8 of 8

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

  1. #1
    Neophyte nickben's Avatar
    Join Date
    Sep 2020
    Posts
    2
    Articles
    0
    Excel Version
    2019

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,
    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?

    Parameters Using List Tables.xlsx

    Thank you,
    Nick

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,401
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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:

    Code:
        #"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, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,401
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    Seeker mikemck's Avatar
    Join Date
    Jan 2019
    Posts
    17
    Articles
    0
    Excel Version
    2016
    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?

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,401
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6
    Seeker mikemck's Avatar
    Join Date
    Jan 2019
    Posts
    17
    Articles
    0
    Excel Version
    2016
    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.

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,401
    Articles
    45
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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:
    Code:
    =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.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Neophyte nickben's Avatar
    Join Date
    Sep 2020
    Posts
    2
    Articles
    0
    Excel Version
    2019
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •