Problem converting dates from text, mixed formats in one column.

[David283]

I have a table with a mix of date formats in one column.  Some are in the excel serial number format, and others are in a date/time/timezone format.  please see image below.

I need to convert these into date only format.  I know how to use transfom and get the date only from the date/time/timezone ones, but I somehow need to fix the serial number ones first.  How can I go about this?

[edhans · Accepted solution]

I would fix them in a few steps. You can do this 99% with the user interface and simple and easy to follow formulas.

1. Create a new column with the following formula: if Text.Length([DateField]) = 5 then Number.FromText([DateField]) else null - this will return an integer.
2. Now convert that to a date.
3. Now another new column - if Text.End([DateField], 1) = "Z" then [DateField] else null - this will pull your time fields.
4. Convert that to datetime.
5. Now you have to get them to be both the same, either both date, datetime, or datetimezone. Convert them to the same, then one last column:
   1. if [FirstDate] = null then [SecondDate] else [FirstDate]
6. Delete the temp first and second date, and the original date field.

[David283]

You had me all the way till step 6 🙂

I did steps 1 - 5 and it worked great, thank you! 

But step 6...  This is a power BI report and more data will roll in every day.  I plan to try to figure out and fix my source, but in the mean time, do I need to leave the conversion formulas and columns in place so that these automaticly convert each day?

[edhans]

No. Tomorrow when new data comes in, it will rerun all of these steps. It is just deleting those columns before it gets loaded to Power BI. Look at the steps that are being created on the right side of the Power Query window. Think of that as a macro. It isn't like Excel where it is permenantly deleting those columns. It only deletes the columns after steps 1-5 are done again.

[David283]

Understood.

Marking solution, Thanks for the help!