If Less Than or Greater Than

[Factotum]

This is probably a simple fix, but I'm really new to Power Query and can't find an answer. I'm trying to compare square footage between two systems, but want to ignore any potential rounding differences less than -10 or greater than +10.

My original formula was [#"Sqaure Foot A"] = [#"Square Foot B"]
The result comes back TRUE or FALSE, which is the format I want

I've tried this:

if [#"Square Foot A"] - [#"Square Foot B"] > -10 and [#"Square Foot A"] - [#"Square Foot B"] < 10 then "TRUE" else "FALSE"

This one seems to work until I load it to a table. Then I get the error: "We cannot convert the value null to type logical."

Any thoughts on what I'm missing?

 

[sandy666]

post a sample of source data

 

[sandy666]

[SIZE=1]if [src1]-[src2]>-10 and [src1]-[src2]<10 then true else false[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]src1[/td][td=bgcolor:#5B9BD5]src2[/td][td][/td][td=bgcolor:#70AD47]src1[/td][td=bgcolor:#70AD47]src2[/td][td=bgcolor:#70AD47]Custom[/td][td=bgcolor:#70AD47]Custom.2[/td][td][/td][td]src1[/td][td]src2[/td][td]Custom[/td][td]Custom.2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

6​

[/td][td=bgcolor:#DDEBF7]

9​

[/td][td][/td][td=bgcolor:#E2EFDA]

6​

[/td][td=bgcolor:#E2EFDA]

9​

[/td][td=bgcolor:#E2EFDA]

TRUE​

[/td][td=bgcolor:#E2EFDA]

-3​

[/td][td][/td][td]

6​

[/td][td]

9​

[/td][td]

TRUE​

[/td][td]

-3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

17​

[/td][td]

6​

[/td][td][/td][td]

17​

[/td][td]

6​

[/td][td]

FALSE​

[/td][td]

11​

[/td][td][/td][td]

17​

[/td][td]

6​

[/td][td]

FALSE​

[/td][td]

11​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

3​

[/td][td=bgcolor:#DDEBF7]

20​

[/td][td][/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]

20​

[/td][td=bgcolor:#E2EFDA]

FALSE​

[/td][td=bgcolor:#E2EFDA]

-17​

[/td][td][/td][td]

3​

[/td][td]

20​

[/td][td]

FALSE​

[/td][td]

-17​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]

5​

[/td][td][/td][td][/td][td]

5​

[/td][td][/td][td][/td][td][/td][td]null[/td][td]

5​

[/td][td]Error[/td][td]null[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

5​

[/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]

5​

[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td][/td][td]

5​

[/td][td]null[/td][td]Error[/td][td]null[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]null[/td][td]null[/td][td]Error[/td][td]null[/td][/tr]
[/table]

and here is a whole description of the Error

[SIZE=1]Expression.Error: We cannot convert the value null to type Logical.
Details:
    [B][COLOR="#FF0000"]Value=[/COLOR][/B]
    Type=Type[/SIZE]

 

[theBardd]

Try this

= Table.AddColumn(Source, "Equal(ish)", each Number.Abs([Square Foot A]-[Square Foot B])<10)

 

[Factotum]

Sandy - thank you for adding the sample data. It was on my list to do, but I kept getting sidetracked.

Bardd - you nailed it! Thank you for your help - your solution works perfectly!