Issues when source date column contains Date and Serial date

[grilli79]

Hi all,

This is my first question to the forum, so be gentle...

I'm relatively new to Power BI and have a bunch of CSVs i'm consolidating into a report. Occasionally one of the date columns contains serial format (e.g., 45678) dates. At the same time, this column also contains dates (correctly) in the format dd/mm/yyyy. Example shown here.

In Query Editor, when i try to reformat the column to "Short Date", it doesn't do anything and i'm still presented with "Error - We couldn't parse the input provided as a Date value". In the CSV file, the serial is coming through as "General" format. But the same error happens if the serial is "Number". 

Replace Errors won't help, because I want to keep the underlying date, and don't want to replace it with a static value because there could be other reasons for errors in this column too, not just the serial date format. 

Any help would be appreciated!

Thanks

Adam

[Anonymous · Accepted solution]

Hi,
If no transformation works, you could create a new column by using:

#date(1900,1,1)+#duration([Column4]-2,0,0,0)

From this thread: https://community.powerbi.com/t5/Desktop/Date-serial-to-date-format/m-p/526307