ISO8601 - convert to date/time no longer working

[mdex]

I use worldtimeapi

= Json.Document(Web.Contents("http://worldtimeapi.org/api/timezone/Europe/London"))

which presents in ISO8601 format.

2022-03-30T12:41:22.719641+01:00

Up until this I was able to convert to date/time, but now I'm receiving an error. Is this due to the clocks changing in the UK?

DataFormat.Error: We couldn't parse the input provided as a DateTime value.
Details:
2022-03-30T12:41:22.784125+01:00

[HotChilli · Accepted solution]

I think it's the automatic transform from 'any' data type to 'datetime' that doesn't work.

If you edit the {"datetime",type datetime} part to be {"datetime", type datetimezone}, that should work without error.

From there, you can change the type to datetime, using the interface-> (add step if given the option, to make it more obvious).

I'm not sure if it used to work or whether there has been a change in the Power Query code but that should make it work.

[mdex]

Unbelievable!

Thanks. I'll amend the rest of my reports which use this method.

[HotChilli]

Can you post the code that changes the type please?

[mdex]

= Table.TransformColumnTypes(#"Converted to Table",{{"abbreviation", type text}, {"client_ip", type text}, {"datetime", type datetime}, {"day_of_week", Int64.Type}, {"day_of_year", Int64.Type}, {"dst", type logical}, {"dst_from", type any}, {"dst_offset", Int64.Type}, {"dst_until", type any}, {"raw_offset", Int64.Type}, {"timezone", type text}, {"unixtime", Int64.Type}, {"utc_datetime", type datetime}, {"utc_offset", type text}, {"week_number", Int64.Type}})

FULL CODE

let
    Source = Json.Document(Web.Contents("http://worldtimeapi.org/api/timezone/Europe/London")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"abbreviation", type text}, {"client_ip", type text}, {"datetime", type datetime}, {"day_of_week", Int64.Type}, {"day_of_year", Int64.Type}, {"dst", type logical}, {"dst_from", type any}, {"dst_offset", Int64.Type}, {"dst_until", type any}, {"raw_offset", Int64.Type}, {"timezone", type text}, {"unixtime", Int64.Type}, {"utc_datetime", type datetime}, {"utc_offset", type text}, {"week_number", Int64.Type}})
in
    #"Changed Type"