Expression Error: We cannot apply operator < to types Number and Duration

[Anonymous]

Hi there, I am getting the following error on the below query:

Express.Error: We cannot apply operator < to types Number and Duration.

Details:

Operator=<

Left=35

Right=571.00:00:00

Custom Column Query used:

= if ([Vulnerability_Test_Date] - [Vulnerability_Published_Date] >=35) and [Severity] = "Critical" then "Yes" else
if ([Vulnerability_Test_Date] - [Vulnerability_Published_Date] >=65) and [Severity] = "High" then "Yes" else if
([Vulnerability_Test_Date] - [Vulnerability_Published_Date] >=95) and [Severity] = "Medium" then "Yes" else "No"

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try to solve this problem with calculated columns.

Column =
IF (
    [Vulnerability_Test_Date] - [Vulnerability_Published_Date] >= 35
        && [Severity] = "Critical",
    "Yes",
    IF (
        [Vulnerability_Test_Date] - [Vulnerability_Published_Date] >= 65
            && [Severity] = "High",
        "Yes",
        IF (
            [Vulnerability_Test_Date] - [Vulnerability_Published_Date] >= 95
                && [Severity] = "Medium",
            "Yes",
            "No"
        )
    )
)

If you try to solve it in Power query custom column, you need to do type conversion.

if (Number.From([Vulnerability_Test_Date] - [Vulnerability_Published_Date]) >=35) and [Severity] = "Critical" then "Yes" else
if (Number.From([Vulnerability_Test_Date] - [Vulnerability_Published_Date])>=65) and [Severity] = "High" then "Yes" else if
(Number.From([Vulnerability_Test_Date] - [Vulnerability_Published_Date])>=95) and [Severity] = "Medium" then "Yes" else "No"

This is the relevant document, hope to help you:

https://docs.microsoft.com/powerquery-m/type-conversion 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bcdobbs]

Try and cast the difference as an integer.

INT ( [Vulnerability_Test_Date] - [Vulnerability_Published_Date] )>=95

You might want to store the difference in a variable first as well .