Forum Discussion
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/...
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 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!