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

[Anonymous]

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?

[ibarrau]

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]

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]

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,

[Anonymous]

@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]

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]

@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]

@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]

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]

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 · Accepted solution]

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]

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?

[Anonymous]

@ibarrauthanks a lot!