Forum Discussion

GuerauFF's avatar
GuerauFF
Icon for Helper II rankHelper II
6 years ago
Solved

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/...
General Comment
Need Help
Tips and Tricks
  • MFelix's avatar
    MFelix
    6 years ago

    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")
MFelix's avatar
MFelix
Icon for Super User rankSuper User
6 years ago

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.

 

GuerauFF's avatar
GuerauFF
Icon for Helper II rankHelper II
6 years ago

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?

 

 

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!

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    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?