future date column blank

[zraopingm]

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

[zraopingm]

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

[v-yiruan-msft · Accepted solution]

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]

thank you Rena

that was what I was looking for an answer

Zoey

[zraopingm]

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

[v-yiruan-msft]

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]

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

[v-yiruan-msft]

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