cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Date format

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:

 

Date1.JPG

 

Date2.JPG

Does anyone know how to solve this? Thanks!

12 REPLIES 12
Highlighted

Hey @MFelix !

 

I swear it will be the last issue. This morning when I ran the formula I thought it was working properly and I moved to some other tasks, but now, I see that there's still one issue.

 

In the rows that initially have this format: dd/mm/yyyy hh:mm:ss, from the formula I am obtaining this format: mm/dd/yyyy hh:mm:ss AM/PM and I should be obtaining dd/mm/yyyy hh:mm:ss AM/PM. I guess I just have to change the order of some of the rows in the formula, not sure which ones though...

 

How would it be the formula considering this small change?

 

Thanks!

 

EDIT!!! Nevermind, I got it on my own. Thanks anyway!

Highlighted

Hi @GuerauFF ,

 

Do you have rows without AM/PM that have dd/mm/YYYY and others that have mm/dd/yyyy? or do they all have the dd/mm/yyyy?

 

In the screen you present that is not legible.

 

Try this measure:

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.AfterDelimiter(Text.BeforeDelimiter([DAte], " ", 0), "/", 1)
     & "/"
     & Text.PadStart(Text.BeforeDelimiter([DAte], "/", 0), 2, "0")
     & " "
     & (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





Highlighted

It was way easier than that, I just had to swap two rows.

 

The final code is the following one: 

 

 

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

 

Thanks for all your help @MFelix !

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors