Forum Discussion
Date format
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")
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")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.
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
