Forum Discussion

cherylakrols94's avatar
cherylakrols94
New Member
2 years ago
Solved

Expression.Error in power query

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 retu...
  • slorin's avatar
    slorin
    2 years ago

    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

ferryv's avatar
ferryv
Resolver II
2 years ago

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"