Forum Discussion

Dayna's avatar
Dayna
Helper V
4 years ago
Solved

Power Query - Filtering with brackets (and / or combined filter)

Hello,

 

I'm struggling a little with the syntax on this one, within Power Query, how do you filter using a combined search or and / or? 

 

What I want to do is:

 

Filter records where curr1 = GBP and curr2 = EUR... OR... curr1 = EUR and curr2 = GBP.

 

I tried this but it throws an error:

= Table.SelectRows(#"Exchange Rate1", each ([exr_curr1] = "GBP" and [exr_curr2] = "EUR") or (each [exr_curr1] = "EUR" and [exr_curr2] = "GBP"))

 

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

 

Many thanks for any help you can offer.

Dayna

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Create a conditional column with your conditions and use that column to filter the data.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcncKUNJRcg0NUorViQbTCF5eaU4OkAtSAuJClMJ4EKVgXiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Curr1 = _t, Curr2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Curr1", type text}, {"Curr2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Filter", each if [Curr1]  =  "GBP" and [Curr2]  = "EUR" then "Filter"
else if [Curr1]  = "EUR"  and [Curr2]  =  "GBP" then "Filter" else null)
in
    #"Added Custom"