Forum Discussion

gssarathkumar's avatar
gssarathkumar
Helper I
2 years ago

Working 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 ty...
Need Help
ppm1's avatar
ppm1
Solution Sage
2 years ago

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's avatar
    gssarathkumar
    Helper I
    2 years ago

    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's avatar
      ppm1
      Solution Sage
      2 years ago

      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