Working on Inconsistent Dates in Power Query

[gssarathkumar]

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.

[Ahmedx]

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"])

[ppm1]

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

[gssarathkumar]

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

[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]

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.