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

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

  • BhaveshPatel's avatar
    BhaveshPatel
    Super User
    9 years ago

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

     

     

    Thank you for your appreciation.

     

    Regards,

    Bhavesh

  • robtops's avatar
    robtops
    New Member
    9 years ago

    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?

    • BhaveshPatel's avatar
      BhaveshPatel
      Super User
      9 years ago

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

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      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