Search code examples
excelpowerbipowerquerym

Power Query / M - Can I control order of operations to support two OR options

I'm trying to conditionally replace a column if both of these columns are either null or had blank space. I would like to know if this is possible for future use of this logic with other conditions. I don't know how to force the two OR statements simultaneously as parenthesis would in a math function or SQL.

Psuedo code

  • replace column a with not listed,
    • if column a is null or blank and column b is null or blank.

I was trying the following, but it made everything that had a null [NOT LISTED].

I'm' looking for IF (column a is X OR Y) AND (column b is X OR Y) then replace column a with [NOT LISTED] Parenthesis in this language are for functions unlike SQL. Is there some character that is the equivalent or way to force the order of operations?

Table.ReplaceValue(#"Previous step",
  each [Country],
  each if [Country"] = null or Text.Trim([Country]) = "" and 
         [#"Org Country"] = null or Text.Trim([#"Org Country"]) = ""
     then "[NOT LISTED]"
         else null

   Replacer.ReplaceValue,
   {"Country"}
)
Solution

  • Try this

    x=  Table.ReplaceValue(#"PriorStepNameHere",  each [a],  each if ([a]=null or [a]="") and ([b]=null or [b]="")  then "Not Listed" else [a] ,Replacer.ReplaceValue,{"a"})

    enter image description here

    if a and b are blank or null, make a into "not listed" otherwise leave a alone