Filtering / Removing Filter in Power Query

[chrisjbrogan]

I have created a dataflow to get my dataset.

I have loaded the data into a query in excel, I then want the user to be able to change filters from drop down list(s) in excel. These filters apply to the dataset before numerous other steps to get the final output.

I am having some success with List.Contains to filter down to the specific user driven filters - my issue is I cannot get my head around trying to unfilter/show all again.

I have created a consolidated P&L, it then is grouped to show granular split of entities.

I want the user to be able to filter to an entity - or other dimensions - then refresh. This is what I am having relative success with.

I cannot figure out however a good way to bypass these or return back to a consolidated unfiltered view.

[chrisjbrogan]

I tried this and again it works for selecting one but trying to get back to all give this error:

#"Filtered Rows2" = #"Table.SelectRows"(#"Grouped Rows2", each if [CONSOL ENTITY] = FILTER_CONSOL_ENTITY then each true else each List.Contains({FILTER_CONSOL_ENTITY}, [CONSOL ENTITY])),

Expression.Error: We cannot convert a value of type Function to type Logical.
Details:
Value=[Function]
Type=[Type]

[v-stephen-msft · Accepted solution]

Hi @chrisjbrogan ，

The error message is pointing you to the problem:
It looks like the issue is with the logic in your Table.SelectRows function. The error message indicates that there’s a type mismatch, specifically that a function is being used where a logical value is expected.

You may try this:

#"Filtered Rows2" = Table.SelectRows(#"Grouped Rows2", each 
    if [CONSOL ENTITY] = FILTER_CONSOL_ENTITY then true 
    else List.Contains(FILTER_CONSOL_ENTITY, [CONSOL ENTITY])
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[chrisjbrogan]

I have created a list and then data validation in excel so the user can pick from the drop down. When they do so the following is great for refreshing and showing the result:

= Table.SelectRows(#"Grouped Rows2", each List.Contains({FILTER_CONSOL_ENTITY}, [CONSOL ENTITY]))

My issue as above is how do you get back to an unfiltered refresh?