Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
Unbelievable!
Thanks. I'll amend the rest of my reports which use this method.
Can you post the code that changes the type please?
= 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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
105 | |
79 | |
69 | |
62 |
User | Count |
---|---|
142 | |
105 | |
103 | |
85 | |
70 |