Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Power Query - custom column - date -2 and another condition

Hi,  I want to create a new column based on  the condition that hortlistcount is 0 and Duedate is less than two days from now or overdue If so, it should say 1 else 0.    So I made this: = Table...
Need Help
  • ibarrau's avatar
    ibarrau
    6 years ago

    You have to ask first for null in order to avoid the comparison between null and dates.

    if [DueDate] = null then 0 else
if [DueDate] <= Date.AddDays
(DateTime.Date(DateTime.LocalNow()), -2) and [ShortListCount] = 0 then 1
else 0

    Be carefull with quotes. If you type "1" the result will be a text 1 and not a number.

     

    Regards,

ibarrau's avatar
ibarrau
Super User
6 years ago

Hi, sure you can add and if [duedate] = null then ......... else [start the other condition]

 

You can decide if you want a value for this cases like 0 or 1 and add it there.

 

Regards,

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I'm still getting errors on that one.

I have this one now:

if [DueDate] <= Date.AddDays
(DateTime.Date(DateTime.LocalNow()), -2) and [ShortListCount] = 0 then "1"
else if [DueDate] = null then 0
else 0

get this error:

  • ibarrau's avatar
    ibarrau
    Super User
    6 years ago

    You have to ask first for null in order to avoid the comparison between null and dates.

    if [DueDate] = null then 0 else
if [DueDate] <= Date.AddDays
(DateTime.Date(DateTime.LocalNow()), -2) and [ShortListCount] = 0 then 1
else 0

    Be carefull with quotes. If you type "1" the result will be a text 1 and not a number.

     

    Regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    ibarrauthanks a lot!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    hi ibarrau . 

    Seems like the formula is a bit buggy. I wanted it to set the value to 1 if the duedate is three days from now or less and shortlist is 0. I now have a case where duedate is tomorrow and shortlist is 0, and the value is still 0.  

     

    Do you see any obvius reasons for it not to be 1?