Forum Discussion

robtops's avatar
robtops
New Member
9 years ago
Solved

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 b...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

robtops's avatar
robtops
New Member
9 years ago

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's avatar
BhaveshPatel
Super User
9 years ago

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's avatar
    robtops
    New Member
    9 years ago

    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's avatar
      BhaveshPatel
      Super User
      9 years ago

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

       

       

      Thanks & Regards,

      Bhavesh

      • robtops's avatar
        robtops
        New Member
        9 years ago

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