Convert text date (March 21st 2020) to dd/mm/yyyy

Question

I've imported an excel sheet into PowerBI and I'm trying to trying to transform the date column from text format (March 21st 2020, 00:00:000) into date format (21/03/2020).

Unfortunately when I select "detect type" it only selects text, and selecting "Date" presents an error. I've removed the ", 00:00:000" from the columns but still receiving an error message.

Does anyone have any suggestions on how I can transform this column into dd/mm/yyyy?

Many thanks,

Answer 1

After removing the , 00:00:000, you can use a transformation like one of these to remove the st part in 21st so that March 21st 2020 --> March 21 2020, which can be automatically converted to a date.

This transformation strips all characters except space and numbers after the first space:

each Text.BeforeDelimiter(_, " ") & Text.Select(_, {" ", "0".."9"})

This one deletes the two characters to the left of the last space:

each Text.ReplaceRange(_, Text.PositionOf(_, " ", Occurrence.Last)-2, 2, "")

The full query (using the first option) would look like this:

let
    Source = <Your Data Source>,
    Remove00 = Table.TransformColumns(Source, {{"DateText", each Text.BeforeDelimiter(_, ","), type text}}),
    StripOrdinal = Table.TransformColumns(Remove00, {{"DateText", each Text.BeforeDelimiter(_," ") & Text.Select(_, {" ","0".."9"}), type text}}),
    TextToDate = Table.TransformColumnTypes(StripOrdinal,{{"DateText", type date}})
in
    TextToDate