Forum Discussion

fox252's avatar
fox252
Frequent Visitor
4 years ago

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

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. A...
Need Help
Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

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's avatar
    fox252
    Frequent Visitor
    4 years ago

    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.