Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GuerauFF
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!

1 ACCEPTED SOLUTION

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

12 REPLIES 12
v-chuncz-msft
Community Support
Community Support

@GuerauFF 

 

You may refer to the post below.

https://community.powerbi.com/t5/Desktop/DataFormat-Error-We-couldn-t-parse-the-input-provided-as-a-...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MFelix
Super User
Super User

Hi @GuerauFF,

 

You issue is related with having different format for the months and day try to add the following column to the query and format as datetime:

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

DateTime.From ([DAte])

Should work as expected.

 


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



Hi @MFelix 

 

I tried it again and it's working, I guess I was making a mistake when putting the correct variable in the formula.

 

There's only one issue left, it seems like it's working for the dates with which I was having issues before, but it isn't working for the other ones that I had. Before changing the format:

AbansData.JPG

 

And after the format change:

PostData.JPG

 

Any idea how I could make it work for both date types?

Thanks for your help!

 

Guerau

 

 

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



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!

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



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 !

Yes!

 

It worked perfectly! Thank you very much @MFelix ! Awesome!

 

Will make sure to give you the biggest kudos! 🙂

Hi @GuerauFF ,

 

You should use the new column as you date column, so you must change the format on the column you just created and delete the initial column, then rename the new column to the name of the previous one.


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



Hi @MFelix ,

 

I can do that but it doesn't solve the problem. The issue I'm having is that with the initial formula it is working properly for the dates ending in PM/AM but it gives me the error message for the other ones.

I have tried to change the last line of the formula, putting instead of Datetime.From Text.Select but it isn't working either.

 

To sum it up, I think what I need to do is adapt the else clause of the formula that you gave me so the dates in the format dd/mm/yyyy hh:mm:ss are converted to the format dd/mm/yyyy hh:mm:ss PM/AM. And once that is done I will be able to format the new column as date and hour and it will be working properly.

 

The problem is that I don't know how to change the else clause so it does so...

 

Do you know what I mean? Any help with the edit of the else clause would be appreciated!

 

Guerau

Hi @MFelix !

 

Thanks for the answer, I have tried it and it doesn't work though... it gives me an error. 

 

When you say add it to the query you mean to add the column with PowerQuery or with the query editor from PowerBI?

 

Captura.JPG

 

I need to do it from Powerquery because afterwards I'm appending this query with some others, and I need the date format to be okay in that stage, that's the main point

 

Would greatly appreciate if you could help me out!

Hi @GuerauFF ,

 

This formula is to use on the Query editor.

 

What do you mean PowerQuery? - Excel? Power BI?

 

The formula works for both of them. What is the error you are getting?


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



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.