Power Query If date is between 2 other dates with null values Problems with Null Values

[robtops]

I've been searching all over the place for a solution to what I though should be pretty simple but still can't get it to work. In Oracle my query looks like:

Case when first_reminder_expected_date between NVL(Entry_date, '30-Dec-9999') and NVL(Exit_Date,'31-DEC-9999')

I'm sure I'm doing something pretty basic wrong but can't seem to figure it out.  All the date columns contain nulls so the formula needs to deal with this

Any advice on how to achieve the same result in Power Query would be much appreciated Thanks

Rob

[BhaveshPatel]

Can you please elaborate your scenario what are you trying to do using powerquery.

Thanks & Regards,

Bhavesh

[robtops]

I'm trying to create a flag which will tell me if the field 'First_Reminder_Expected_Date' is between the other two fields 'Entry_Date' and Exit_Date.  This will be used a in a slicer / filter in a dashboard I'm creating

Do you need any more info?

[BhaveshPatel]

You can create a conditional column in PowerQuery.

R

To deal with the null values there are quite a few workarounds such as you can use replace values option or fill down option as well to deal with the null values.

Thanks & Regards,

Bhavesh

[robtops]

Thanks very much.  I really wanted to be able to to combine it in other if statements as well if possible e.g

if first_reminder_expected_date between Entry_date and Exit_Date

and ColumnA = "yes"

then "A" else "B"

is this possible in one query or do i need to do it in 2 stages?

[BhaveshPatel]

Yes you can do it in the same query as long as Column A is in the same table.

Thanks & Regards,

Bhavesh

[robtops]

Thanks a lot for your help, hopefully I should be abel to progress with this now

[robtops]

Sorry i'm still getting a problem with Nulls.

I can't really replace the Nulls in all the columns as the indicate that something hasn't reached that stage.

Is there a way to deal with them in a formula e.g if null or NVL?

[v-shex-msft · Accepted solution]

Hi @robtops,

I agree with BhaveshPatel’s point of view, you can use Table.ReplaceValue function to achieve your requirement.
 

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Area table.xlsx"), null, true),
    Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet3_Sheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Entry_date", type date}, {"Exit_Date", type date}}),
    Custom=
let
    #"replace Entry_date" = Table.ReplaceValue(#"Changed Type",null,#date(9999, 12, 30),Replacer.ReplaceValue,{"Entry_date"}),
    #"replace Exit_Date" = Table.ReplaceValue(#"replace Entry_date",null,#date(9999, 12, 31),Replacer.ReplaceValue,{"Exit_Date"})
in
    #"replace Exit_Date"
in
Custom

Regards,
Xiaoxin Sheng

[BhaveshPatel]

You have to use replace values or fill down feature for replacing nulls.

[BhaveshPatel]

If you think the problem is solved, please mark this post as solution.

Thank you for your appreciation.

Regards,

Bhavesh