Filter by more than one value while treating null as "All"

[fox252]

Hello,

I'm almost a year into my Power Query journey and this is the first time I have not been able to Google a solution or inspiration for a solution. Help please!

My tool lives in Excel. At a high level, I have a transactions file (JSON) that is dropped to a Onedrive folder daily and referenced by PQ. I also have a table in Excel that I would like to filter by. There are many approaches out there using Merge, List.Contains and Parameters but my question has a wrikle to it. 

[Transaction File] contains many colunms including the two columns I want to filter by

[Filter Table] 

My desired return would be the [Transaction File] filtered for rows where both {Security Type} and {Transaction Code} match the [Filter Table]. All rows where {Transaction Code}=DVCA regardless of {Security Type}. All rows where {Security Type}="ETF" regardless of {Transaction Code}.. Etc.. It's as of I want to treat null as retun all. 

If both were always populated in the filter table I could create a key and it would be quite simple. I've tried an approach where I create lists, filter the transactions multiple times by those lists depending on scenario (I.e. Both values populated, Tran only, Sec only) and then appending the results into an single table as an ouput. Not ideal.. There has to be a better way..

Thank you,

[Vijay_A_Verma]

Use this statement (replace #"Changed Step" with your previous step)

= Table.SelectRows(#"Changed Type", each List.Contains(FilterTbl[Security Type],[Security Type]) or List.Contains(FilterTbl[Transaction Code],[Transaction Code]))

Complete Query is below

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Security Type", type text}, {"Transaction Code", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each List.Contains(FilterTbl[Security Type],[Security Type]) or List.Contains(FilterTbl[Transaction Code],[Transaction Code]))
in
    #"Filtered Rows"

Excel file containing solution @ https://1drv.ms/x/s!Akd5y6ruJhvhuUTGzsBEMcfYV-Mo?e=pKTcSc 

[fox252]

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. 

[Vijay_A_Verma]

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