The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to Solution.
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"
remove the 2nd 'each'