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,

Anonymous's avatar
Anonymous
Not applicable
6 years ago

ibarrau 

Still the same error, but I get 0 when the duedate is null

 

 

seems to me that id does not like the < sign when using dates?

ibarrau's avatar
ibarrau
Super User
6 years ago

Can you please show us the error text? because I just test it and I'm sure it is not a date problem this time.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    ibarrauah, I managed to get it to work. I messed up the "applied steps" a bit and it broke cause of that.

     

    So now I get both 0 and 1, but it still says that there are errors?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      ibarrau 

      Ok, it shows error when duedate is null. That's logic :) But is it a way to handle that? show 0 if duedate is null

      • 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,