Solved: Date format - Page 2

GuerauFF · ‎04-09-2020

Hi there!

I am having some issues with date formatting... it's a nightmare!

So, what I have is a column where I have both dates as "dd/mm/YYYY hh:mm:ss" and others as "dd/mm/YYYY hh:mm:ss AM/PM".

When I change the column format to date/hour I have no issue with the ones that are with the first format but powerquery identifies as error the ones that are with the second format. Some images to ilustrate it:

Does anyone know how to solve this? Thanks!

MFelix · ‎04-14-2020

Hi @GuerauFF ,

Try the following code:

if Text.End([DAte], 2) = "AM" or Text.End([DAte], 2) = "PM"
  then Text.PadStart(Text.AfterDelimiter(Text.BeforeDelimiter([DAte], "/", 1), "/", 0), 2, "0")
     & "/"
     & Text.PadStart(Text.BeforeDelimiter([DAte], "/", 0), 2, "0")
     & "/"
     & Text.AfterDelimiter([DAte], "/", 1)
  else Text.PadStart(Text.AfterDelimiter(Text.BeforeDelimiter([DAte], "/", 1), "/", 0), 2, "0")
     & "/"
     & Text.PadStart(Text.BeforeDelimiter([DAte], "/", 0), 2, "0")
     & "/"
     & Text.AfterDelimiter(Text.BeforeDelimiter([DAte], " ", 0), "/", 1)
     & " "
     & (if Number.FromText(Text.BeforeDelimiter(Text.AfterDelimiter([DAte], " ", 0), ":", 0)) < 12
      then Text.BeforeDelimiter(Text.AfterDelimiter([DAte], " ", 0), ":", 0)
      else Number.ToText(
        Number.FromText(Text.BeforeDelimiter(Text.AfterDelimiter([DAte], " ", 0), ":", 0)) - 12
      ))
     & ":"
     & Text.AfterDelimiter([DAte], ":", 0)
     & (if Number.FromText(Text.BeforeDelimiter(Text.AfterDelimiter([DAte], " ", 0), ":", 0)) < 12
      then " AM"
      else " PM")

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

View solution in original post