How to replace null values in datetime values

[Anonymous]

Hello, I know this issue has been raised so many times on this formu, I tried to work on that and use your solutions to my code, but with no success. During the refreshing I face this bug:
OLE DB or ODBC error: [Expression.Error] We cannot convert the value null to type Logical.. An unexpected exception occurred.
I assume, the problem is here:

I have the column with a date (data type: Date/Time) and with some rows this date is empty, which is fine, I cannot remove these ones. But how can I replace these values? I think that adding the new column does not solve my problem, because during the refreshing the issue will occur anyway. How to fix it?

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ，

Approve with @amitchandak , when there is null value in a data column, it won’t get error.

It seems that you used the null value in somewhere else?

Please try to replace these null values with 9999/12/31

Here is the M code:

let

    Source = Excel.Workbook(File.Contents("C:\xxx\New Microsoft Excel Worksheet.xlsx"), null, true),

    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type date}, {"Column2", type number}}),

    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,#date(9999, 12, 31),Replacer.ReplaceValue,{"Column1"})

in

    #"Replaced Value"

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This is works for this column, thank you

[Anonymous]

Maybe the problem is elsewhere, I don't know how to find exactly where the problem is. Could you please advise me?

[amitchandak]

@Anonymous , date column should not give that issue. It should be some boolean column.

[Anonymous]

@amitchandak Could you please advise me how to add boolean column?