Forum Discussion
Power Query If date is between 2 other dates with null values Problems with Null Values
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
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
10 Replies
Can you please elaborate your scenario what are you trying to do using powerquery.
Thanks & Regards,
Bhavesh
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?
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
