Forum Discussion
Filter by more than one value while treating null as "All"
This is similar to an approach I tried, issue it is too broad in what is allowed in.
E.g. The highlighted should not have been allowed thru the filter as per the [FilterTbl] {Asset Type}="CS" AND {Tran Type}="Sell"
Here's my code for the output. Note I had masked some of the column names in my original post to be more friendly but the actual names are shown below.
let
Source = Transactions_Transformed,
#"Filtered Rows" = Table.SelectRows(Source, each List.Contains(FilterTbl[Asset Type],[instrumentType]) or List.Contains(FilterTbl[Tran Type],[transactionPurposeCode]))
in
#"Filtered Rows"
P.s. As a bonus.. I did not know I could use List.Contains on a table column as opposed to having to be list.
Here is your solution uploaded to Onedrive
https://1drv.ms/x/s!Akd5y6ruJhvhuUel50CnktEXHQSC?e=CFs5OS
I created 3 different filtertables for your 3 criteria.
1. To have the condition where both columns are needed.
2 & 3 - Each having all rows condition from 2 different columns