Forum Discussion
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:
Does anyone know how to solve this? Thanks!
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")
12 Replies
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.
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!
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:
And after the format change:
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")
You may refer to the post below.
