Forum Discussion
Solved
Problem converting dates from text, mixed formats in one column.
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...
I would fix them in a few steps. You can do this 99% with the user interface and simple and easy to follow formulas.
- Create a new column with the following formula: if Text.Length([DateField]) = 5 then Number.FromText([DateField]) else null - this will return an integer.
- Now convert that to a date.
- Now another new column - if Text.End([DateField], 1) = "Z" then [DateField] else null - this will pull your time fields.
- Convert that to datetime.
- 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:
- if [FirstDate] = null then [SecondDate] else [FirstDate]
- Delete the temp first and second date, and the original date field.
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.
Understood.
Marking solution, Thanks for the help!