Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
82 | |
65 | |
63 | |
56 |
User | Count |
---|---|
171 | |
111 | |
109 | |
72 | |
71 |