CODEWITHSUNDEEP
stringdatedatetimepowerbipowerquery
willd9
willd9

Reputation: 87

Convert datetime String format to Date format in Power BI

I have a column in a Power BI dataset which is Datetime values but in a String format. I've attempted to change the format to Date and Datetime but always receive the following error:

DataFormat.Error: We couldn't parse the input provided as a Date value. Details: 27MAY2021:15:42:29

All of the values in the column are as the format above, e.g. 04JAN2022:15:33:42 I want to convert all of the values in this column to Date or Datetime, but ideally 01/01/2022

Upvotes: 1

Views: 1513

Answers (1)

Peter
Peter

Reputation: 12325

Use this M-code to convert

let
    Source = Table.FromList({"27MAY2021:15:42:29", "04JAN2022:15:33:42"}, null, {"Date String"}),
    #"Changed Type" = Table.TransformColumnTypes(
        Source,{{"Date String", type text}}),
    #"Added Custom" = Table.AddColumn(
        #"Changed Type", "Date", 
        each DateTime.FromText([Date String], [Format="ddMMMyyyy:HH:mm:ss", Culture="en-US"]), type datetime)
in
    #"Added Custom"

enter image description here

Note that how the datetime is shown in your GUI depends on your regional settings.

Upvotes: 2

Related Questions