Forum Discussion

zraopingm's avatar
zraopingm
Helper IV
2 years ago
Solved

future date column blank

Hello,

for field like "DATE CLOSED" (date type) for a job, if the closed date is in the future ( so it is blank), how can i fixed the blank records when transforming data?

thank you

Zoey

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi zraopingm ,

    Based on the screenshot you’ve provided, it appears that there are no error messages being reported. It’s worth noting that the column [Date Closed] does contain null values. However, these null values should not impact the data loading process. You should be able to proceed with your intended operations on the data. If you encounter any issues or have further questions, please don’t hesitate to reach out. Thank you.

    Best Regards

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi zraopingm ,

    You can add a custom column in Power Query Editor:

    let
    Source = YourDataSource,
  #"Added Custom" = Table.AddColumn(
    #"Changed Type", 
    "Custom", 
    each 
      if ([DATE CLOSED] = null or [DATE CLOSED] > Date.From(DateTime.LocalNow())) then
        "Future Date"
      else
        [DATE CLOSED]
  )
in
    #"Added Custom"

    Best Regards

    • zraopingm's avatar
      zraopingm
      Helper IV
      2 years ago

      Thank you Rena,

      I guess i can always accept all errors and leave the blank as they are. and it seems working as well, but i don;t know if there is any potential subsequent risk. Talking of blank cells, most times, i replace every column manually with "na"... simply to remove errors.. replacing blanks with "na" does not bring good value most cases, shall I just simply accept all errors? 

      Thank you

      Zoey

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi zraopingm ,

        Could you kindly provide a screenshot illustrating the error? Additionally, sharing the specific code associated with the query that is causing the error would be greatly beneficial. This information will significantly aid in identifying a potential solution. Your assistance is greatly appreciated.

        Best Regards

  • zraopingm's avatar
    zraopingm
    Helper IV
    2 years ago

    hi Rena,

    thank you. sorry for the late response. 

    below is an image:

    column date closed, some project will be closed in the future date, therefore it is blank. 

    in this case, can i just simply accept all errors?

    thank you

    Zoey

     

    • zraopingm's avatar
      zraopingm
      Helper IV
      2 years ago

      another related example  in accepting all errors is: 

      see below image:

      column AKA, some jobs simply do not have a AKA name, i always replace blank with "na", but is it necessary? can i just simply accept all errors ?

      thank you

      Zoey

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi zraopingm ,

      Based on the screenshot you’ve provided, it appears that there are no error messages being reported. It’s worth noting that the column [Date Closed] does contain null values. However, these null values should not impact the data loading process. You should be able to proceed with your intended operations on the data. If you encounter any issues or have further questions, please don’t hesitate to reach out. Thank you.

      Best Regards

      • zraopingm's avatar
        zraopingm
        Helper IV
        2 years ago

        thank you Rena

        that was what I was looking for an answer

        Zoey