Time error

Hi Anonymous 

As tested, your [ACCIDENT_DATE] is formatted as dd/mm/yyyy, but power bi would recorgnize it as mm/dd/yyyy, it would show error when converting it into date type.

Before any change type step, if it did before, please delete the part what changes the [[ACCIDENT_DATE] to "date" type, just keep it in text type.

Please split the [ACCIDENT_DATE] by "/" delimiter,

then rename "ACCIDENT_DATE.1"->"day", "ACCIDENT_DATE.2"-> "month", "ACCIDENT_DATE.3"-> "year",

click on "year" first and "ctrl"+click on "month" secondly, then "ctrl"+click on "day" thirdly, select "Merge column" by "/",

Change the new date column into "date" type,

let
    Source = Csv.Document(File.Contents("Crashes_Last_Five_Years.csv"),[Delimiter=",", Columns=63, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers",".",":",Replacer.ReplaceText,{"ACCIDENT_TIME"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"ACCIDENT_TIME", type time}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "ACCIDENT_DATE", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"ACCIDENT_DATE.1", "ACCIDENT_DATE.2", "ACCIDENT_DATE.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"OBJECTID", Int64.Type}, {"ACCIDENT_NO", type text}, {"ABS_CODE", type text}, {"ACCIDENT_STATUS", type text}, {"ACCIDENT_DATE.1", Int64.Type}, {"ACCIDENT_DATE.2", Int64.Type}, {"ACCIDENT_DATE.3", Int64.Type}, {"ALCOHOLTIME", type text}, {"ACCIDENT_TYPE", type text}, {"DAY_OF_WEEK", type text}, {"DCA_CODE", type text}, {"HIT_RUN_FLAG", type text}, {"LIGHT_CONDITION", type text}, {"POLICE_ATTEND", type text}, {"ROAD_GEOMETRY", type text}, {"SEVERITY", type text}, {"SPEED_ZONE", type text}, {"RUN_OFFROAD", type text}, {"NODE_ID", Int64.Type}, {"LONGITUDE", type number}, {"LATITUDE", type number}, {"NODE_TYPE", type text}, {"LGA_NAME", type text}, {"REGION_NAME", type text}, {"VICGRID_X", type number}, {"VICGRID_Y", type number}, {"TOTAL_PERSONS", Int64.Type}, {"INJ_OR_FATAL", Int64.Type}, {"FATALITY", Int64.Type}, {"SERIOUSINJURY", Int64.Type}, {"OTHERINJURY", Int64.Type}, {"NONINJURED", Int64.Type}, {"MALES", Int64.Type}, {"FEMALES", Int64.Type}, {"BICYCLIST", Int64.Type}, {"PASSENGER", Int64.Type}, {"DRIVER", Int64.Type}, {"PEDESTRIAN", Int64.Type}, {"PILLION", Int64.Type}, {"MOTORIST", Int64.Type}, {"UNKNOWN", Int64.Type}, {"PED_CYCLIST_5_12", Int64.Type}, {"PED_CYCLIST_13_18", Int64.Type}, {"OLD_PEDESTRIAN", Int64.Type}, {"OLD_DRIVER", Int64.Type}, {"YOUNG_DRIVER", Int64.Type}, {"ALCOHOL_RELATED", type text}, {"UNLICENCSED", Int64.Type}, {"NO_OF_VEHICLES", Int64.Type}, {"HEAVYVEHICLE", Int64.Type}, {"PASSENGERVEHICLE", Int64.Type}, {"MOTORCYCLE", Int64.Type}, {"PUBLICVEHICLE", Int64.Type}, {"DEG_URBAN_NAME", type text}, {"DEG_URBAN_ALL", type text}, {"LGA_NAME_ALL", type text}, {"REGION_NAME_ALL", type text}, {"SRNS", type text}, {"SRNS_ALL", type text}, {"RMA", type text}, {"RMA_ALL", type text}, {"DIVIDED", type text}, {"DIVIDED_ALL", type text}, {"STAT_DIV_NAME", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"ACCIDENT_DATE.1", "day"}, {"ACCIDENT_DATE.2", "month"}, {"ACCIDENT_DATE.3", "year"}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Renamed Columns", {{"year", type text}, {"month", type text}, {"day", type text}}, "en-US"),{"year", "month", "day"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"new date"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Merged Columns",{{"new date", type date}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type2", "date time", each Text.Combine({Text.From([new date], "en-US"), Text.From([ACCIDENT_TIME], "en-US")}, " "), type text),
    #"Changed Type3" = Table.TransformColumnTypes(#"Inserted Merged Column",{{"date time", type datetime}})
in
    #"Changed Type3"

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.