Forum Discussion
Helper IWorking on Inconsistent Dates in Power Query
Hi, I am working on the below inconsistent date formats in power query and looking for a solution.
The below table contains May month date but not in the consistent. Hence while changing the data type, affecting the dates.
| created |
| 2021-09-05 10:15:45.0000000 |
| 2021-10-05 17:32:15.0000000 |
| 2021-10-05 17:58:15.0000000 |
| 16-05-2021 12:45:31 |
| 17-05-2021 17:31:12 |
| 18-05-2021 15:32:55 |
| 19-05-2021 08:54:23 |
| 20-05-2021 09:56:26 |
Please have a check on the first three rows. Instead of considering 9th May, 10th May, it is considered as 5th Sep, 5th Oct respectively which yeilds wrong output.
is there any solution to fix this Date formatting?
@amitchandak , @Greg_Deckler @lbendlin , @DesktopOwl @Goodlytics4U @help Ahmedx @Ahmedx @lbendlin : Please help.
5 Replies
ppm1Solution Sage
You could also use a custom column (or custom transform) with this expression.
= if Text.Contains([DateTime], ".") then DateTime.FromText([DateTime], [Format="yyyy-dd-MM HH:mm:ss.fffffff"]) else DateTime.FromText([DateTime], [Format="dd-MM-yyyy HH:mm:ss"])
Pat
gssarathkumarHi ppm1 ,
Acutually your solution helps, however i observed the source data also contains few rows in the below format. when i apply the same logic, it throws error as it doesn't contain the suffix.
Here is the sample data: Can you please help fixing this issue?
DateTime 2022-08-29 06:55:41 2022-10-31 06:05:43 - ppm1
You can first do a step to extract the text before delimiter of "." (to get rid of the .000) and then use this try ... otherwise expression
= try DateTime.FromText([created], [Format="yyyy-dd-MM HH:mm:ss"]) otherwise DateTime.FromText([created], [Format="dd-MM-yyyy HH:mm:ss"])
Pat
- brokencornets
Helper IV
First, split column by delimiter (space)
Then select Add Column > Column From Examples
Type in the correct date format for the first couple - you'll notice that it autopopulates but it's wrong for the second date format. But if you then type in a couple of examples for the new date format Power BI should be smart enough to work out the pattern.
Once you've done that you can concatenate back to date/time if you prefer.
- Ahmedx
Super User
pls try this
try Date.FromText( Text.Split(Text.From( [Column1])," "){0},[Format = "yyyy-dd-MM", Culture = "en-EN"]) otherwise Date.FromText( Text.Split(Text.From( [Column1])," "){0},[Format = "dd-MM-yyyy", Culture = "en-EN"])
