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.AddColumn(#"Changed Type1", "red", each if [DueDate] = Date.AddDays
(Date.From(DateTime.FixedLocalNow()), -2) and [ShortListCount] = 0 then "1"
else 0)

And it gives me 0 every time. 

 

Any idea what I'm doing wrong?

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,

12 Replies

  • ibarrau's avatar
    ibarrau
    Super User
    6 years ago

    Hi. In the definition you mention a duedate is less than two days from now and the formula is duedate is exactly two days ago.

    Are you sure you are not looking for this:

    = Table.AddColumn(#"Changed Type1", "red", each if [DueDate] <= Date.AddDays
(Date.From(DateTime.FixedLocalNow()), -2) and [ShortListCount] = 0 then "1"
else 0)

    Be sure to show us a row that is not fulfilling this conditions in case you have 0 again.

     

    Regards,

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi ibarrau and thanks for answering.

      I tried that as well but then I get an error in all columns:

       

       

      This should trigger 1

       

      • ibarrau's avatar
        ibarrau
        Super User
        6 years ago

        Ok, let's use a Date for now instead Datetime.

        = Table.AddColumn(#"Changed Type1", "red", each if [DueDate] <= Date.AddDays
(DateTime.Date(DateTime.LocalNow()), -2) and [ShortListCount] = 0 then "1"
else 0)

        This should work.

         

        Regards,