Forum Discussion
Problem converting dates from text, mixed formats in one column.
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.
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.
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?
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!
