Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am trying to use today's date to subtract from a column in power query that is a Due Date. There could be a null value in the Due Date column.
My formula is if Date.From(DateTime.LocalNow()) - [DueDate] <=0 then "OnTime" else Date.From(DateTime.LocalNow()) - [DueDate]
It says No Syntax Errors have been detected but the results in the column are errors. The error says Expression.Error: We cannot apply operator < to types Number and Duration.
Details:
Operator=<
Left=0
Right=847.00:00:00
Solved! Go to Solution.
Hi @dferry ,
Try this instead:
if [DueDate] = null then null
else if Date.From(DateTime.LocalNow()) < [DueDate] then "OnTime"
else Text.From(
Number.From(
Duration.Days(Date.From(DateTime.LocalNow()) - [DueDate])
)
)
You may need to fiddle with the final escape clause to get the exact output format you want, but the first two conditions should solve your isue.
Pete
Proud to be a Datanaut!
Hi @dferry ,
Try this instead:
if [DueDate] = null then null
else if Date.From(DateTime.LocalNow()) < [DueDate] then "OnTime"
else Text.From(
Number.From(
Duration.Days(Date.From(DateTime.LocalNow()) - [DueDate])
)
)
You may need to fiddle with the final escape clause to get the exact output format you want, but the first two conditions should solve your isue.
Pete
Proud to be a Datanaut!
Kudos Pete. That worked. Thank you very much.
Doug