Home > Net >  Expression.Error: We cannot convert the value (Function) to type Logical
Expression.Error: We cannot convert the value (Function) to type Logical

Time:12-01

I am trying to apply a user's manually input fields as a filter to data connection (called IKX).

Here is an example of the user input data:

BUYER CODE FILTER
104
800

I have a table that applies the code format of the power query filter:

BUYER CODE FILTER FORMAT FORMULA
[BUYER_CODE] = 104 IFERROR(IF(LEN(InputBuyerCodes[@[Buyer Code Filter]])>0, "[BUYER_CODE] = " &InputBuyerCodes[@[Buyer Code Filter]], ""),"")
or [BUYER_CODE] = 800 IFERROR(IF(LEN(InputBuyerCodes[@[Buyer Code Filter]])>0, " or [BUYER_CODE] = " &InputBuyerCodes[@[Buyer Code Filter]], ""),"")

Then a table that concats these fields:

CONCAT BUYER CODE FILTER FORMAT FORMULA
[BUYER_CODE] = 104 or [BUYER_CODE] = 800 CONCAT(Table2[Buyer Code Filter Format])

In power query I have a connection to the "CONCAT BUYER CODE FILTER FORMAT" table that drills down the table to just [BUYER_CODE] = 104 or [BUYER_CODE] = 800 called ConcatBuyerCodeFilterFormat

To apply the filter to the IKX data, I added the below step:
=Table.SelectRows(Source, each (ConcatBuyerCodeFilterFormat) )

and get the error
Expression.Error: We cannot convert the value "[BUYER_CODE] = 104 o..." to type Logical. Details: Value=[BUYER_CODE] = 104 or [BUYER_CODE] = 800 Type=[Type]

Any ideas on how to resolve?

CodePudding user response:

See if this helps you. It shows how to filter, with the filter definition in another variable

let Source =#table({"Column1"}, {{"a"}, {"b"}, {"c"}}),
crit="[Column1] = ""a""",
#"Filtered Rows" = Table.SelectRows(Source, Expression.Evaluate("each "&crit))
in #"Filtered Rows"

All that said, if you start off with the first filter table, why not just merge that against your larger data table and remove the rows with no match?

  • Related