Power Query Custom Column: If number in columns a or b are less than 1 then 0 else 1

Hello,

I am attempting to add a custom logical column in an excel power query. The new column should be a 0 or 1 based on whether the number in columns a or b are less than or greater than 1. Attached below is a spreadsheet with some data to explain what I am trying to achieve. With the code below i am able to get the column I desire, however, when there is a null value in column A, and the corresponding cell in column b does have a value, the calculated cell in the custom column results in an error. I would like the formula to ignore cells that are null and calculate using the non-empty cell. For instance, cell in column a is null, cell in column b is 1.5 my code below throws an error, but i would like it to return a value of 1 for being greater than 1.

= if [Column a] < 1 or [column b] < 1 then 0 else 1

Thank you in advance.

Hi jekeith.

Here is my example:
PQ Column a OR Column b.xlsx
and explanation at:
https://www.excelguru.ca/blog/2014/0...n-power-query/

Try something like below...

= try if [Temp to Term] < 1 or [#"Full-Time to Term"] < 1 then 0 else 1 otherwise if [Temp to Term] >= 1 or [#"Full-Time to Term"] >= 1 then 1 else 0

0.JPG

=try ... otherwise

can be used like error handling in PowerQuery.

Alternately, you could test if field is numeric.

=if (Value.Is(Value.FromText([Temp to Term]), type number) and [Temp to Term] < 1) or (Value.Is(Value.FromText([#"Full-Time to Term"]), type number) and [#"Full-Time to Term"] < 1) then 0 else 1