Time error

[Anonymous]

I've two different columns for time and date. PowerBI detects the date format, but when I try to change time type to (Time, Date/time) it throws an error.

Error:

 DataFormat.Error: We couldn't parse the input provided as a DateTimeZone value.
Details:
02.30.00

Any help would be kindly appreciated 🙂 Thanks

[v-juanli-msft]

Hi @Anonymous 

As tested, once imported, it shows as text type for column "ACCIDENT_TIME",

then i replace the "." with ":" for this column, then it can be changed into time type without problem.

If you convert it to datetime, it would show as 12/30/1899 3:00:00 AM.

To get datetime column, please merge [ACCIDENT_DATE] and [ACCIDENT_TIME] with "space", then change it into datetime type.

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.

[Anonymous]

Thanks, Maggie, it worked out.

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please share a simple data example for me to test and analyze.

Best Regards

Maggie

[Anonymous]

Hi Maggie,
 Thanks for the follow-up, I couldn't resolve the time error. 

I'll attach you the link of the dataset, which can help you see why error follows.

Link: https://vicroadsopendata-vicroadsmaps.opendata.arcgis.com/datasets/c2a69622ebad42e7baaa8167daa72127_...

Let me know if you need anything else.

Thanks,

 Vishal

[amitchandak]

@Anonymous , refer if this can help

https://community.powerbi.com/t5/Desktop/Date-format-error/td-p/130510

[Anonymous]

I was unable to perform and transform it. 

[v-juanli-msft]

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.