Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
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"])
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
Hi @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 |
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
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |