How do I convert date serial to date and also handle zero values

Old thread, but I'll post because this was the top result when I was looking for an answer to this, and I was a bit confused by the current responses.

First off, in the data model (DAX) to convert a serial number to date, just change the column to the date data type - no need to use functions.

For zero-handling, create a calculated column of date data type and use the following function:

 =IF([date serial number]=0,BLANK(),[date serial number])

Mixing different data types usually confuses DAX (i.e. you'll probably get an error) and should be avoided. For this reason, convert zeros to blanks, don't leave as zeros and mix with dates.

In the query editor (M), the basic formula snippet would be "if [date serial number] = 0 then null else Date.From([date serial number])" - the actual step would look like the following if you want to add a custom column:

= Table.AddColumn(#"Previous Step", "Corrected Dates", each if [date serial number] = 0 then null else Date.From([date serial number]))

Or if you want to just transform the serial column:

= Table.TransformColumns(#"Previous Step", {"date serial number", each if _ = 0 then null else Date.From(_)})