Expression.Error in power query

[cherylakrols94]

Hi,

I have created a custom column in order to filter out rows based on the criteria of 2 columns: Current Location + Next Location.
If either of these columns contain the text "ICO", then the return value should read "keep", if not then "omit". Here is my syntax:

if Text.Contains( [Current Location] , "ICO") or Text.Contains( [Next Location] , "ICO") then "keep" else "omit"

The values returning "keep" read just fine, but when neither of the columns contain the text "ICO", then the return value "omit" is reading the following error:

Expression.Error: We cannot convert the value null to type Logical.
Details:
Value=
Type=[Type]

It is true that the value of these rows in the next location column read null.

I have also already tried to concatenate these 2 columns instead using the following syntax:

Text.Combine ( [Current Location], " ", [Next Location])

but I then receive the following error:

Expression.Error: 3 arguments were passed to a function which expects between 1 and 2.
Details:
Pattern=
Arguments=[List]

So what am I to do?

[ferryv]

Have you tried converting null values to a string "null", then using the lf statement and finally changing the temp string "null" back to null as a workaround?

For instance:

let
    Source = Table.FromRecords({
    [#"Current Location" = "ICO", #"Next Location" = "ICO"],
    [#"Current Location" = "ICO", #"Next Location" = "Other"],
    [#"Current Location" = "Other", #"Next Location" = "ICO"],
    [#"Current Location" = "Other", #"Next Location" = "Other"],
    [#"Current Location" = "ICO", #"Next Location" = null],
    [#"Current Location" = null, #"Next Location" = "ICO"],
    [#"Current Location" = "Other", #"Next Location" = null],
    [#"Current Location" = null, #"Next Location" = "Other"],
    [#"Current Location" = null, #"Next Location" = null]
}),
#"Convert to String" = Table.ReplaceValue(Source,null,"null",Replacer.ReplaceValue,{"Current Location","Next Location"}),
KeepOrOmit = Table.AddColumn(#"Convert to String","Keep or Omit", 
                        each if Text.Contains([Current Location], "ICO")  or  
                                Text.Contains([Next Location], "ICO")
                        then "keep" 
                        else "omit"),
#"Change back to null" = Table.ReplaceValue(KeepOrOmit,"null",null,Replacer.ReplaceValue,{"Current Location","Next Location"})

                                
in
    #"Change back to null"

[slorin · Accepted solution]

Hi @cherylakrols94 

if Text.Contains( [Current Location] , "ICO") or Text.Contains( [Next Location] , "ICO") ?? false then "keep" else "omit"

Text.Combine ( {[Current Location], " ", [Next Location]})

 Stéphane

[cherylakrols94]

i was just missing the extra brackets then!  thank you for your help!