Forum Discussion
Convert text to dates in a column with mixed date types
Hi Anonymous ,
Do you have another column identify the date column type? For example the date 2/1, If can be first day of february or the second day of january if you do not have any other column can identify the format.
If you do have one, we can create a custom column depends on it easily in Power Query Editor.
if [Type] = "MMDD" then Date.FromText([Date]) else Date.FromText([Date],"fr-SN")
BTW, pbix as attached.Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Skilled SharerIs this what you try to achieve? You can use the custom column option and create the rules needed to do the transformation displayed in the tables. Hope this helps.
saraMissBIResolver I
Hello,
Have you tried to use a calculated column with Format.
Please have a look at the documentation link https://docs.microsoft.com/en-us/dax/custom-date-and-time-formats-for-the-format-function
I hope it would help
Hi, thanks for your suggestion and apologize for the delayed response.
If I understand your screen shot correctly, I would create an if-then statement for each day, i.e. up to 365 if-thens?
- natabird3
its definitely not a pretty way but is a quick way to get what you need. Maybe there is a way to add an if statement so that if number is between 1-31 and 1-12 or something like that but not sure to be honest as the way the data comes in power bi is usually in a different format and so having it to flip just the day and month is more difficult then if you had the year I would say.
v-lid-msftCommunity Support
Hi Anonymous ,
We just need to create one if-else formula if you have another column for each rows, such as following tables:
ID Format Date 1 MMDD 1/2/2019 2 DDMM 1/2/2019 3 DDMM 31/1/2019 4 MMDD 12/1/2019 5 MMDD 10/1/2019 We can create a custom column using formula based on the Format Column. The format column could also be the country or other value that can identify the way you want to format the date.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.