Forum Discussion
How to replace null values in datetime values
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?
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.
5 Replies
Anonymous , date column should not give that issue. It should be some boolean column.
amitchandak Could you please advise me how to add boolean column?
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.
