Often, when querying APIs it is required to enter date and time filters in ISO 8601 format . Today I show a quick way to convert DateTime to ISO 8601 string, based on an ordinary DateTime field according to the following pattern:
2020-10-11T15:00:00-01:00
This represents the 11th October 3pm in UTC -1 timeszone.
Steps to convert DateTime to ISO 8601
If I enter:
#datetime(2020,10,11,12,0,0)
into the formula bar, it will be converted to :
11/10/2020 12:00:00
Comparing to the desired ISO format the year, month and days are in the wrong order. So using the universal Text.From function will not return the correct result.
Fortunately, there are a couple of xxx.ToText function in Power Query that allow for dedicated formatting parameters in their 2nd arguments. For example, the function DateTime.ToText can actually return the string in the desired format if you pass a format string as the 2nd parameter:
DateTime.ToText with format parameters
The syntax for these format strings can also be found here.
Last step is to add the time-zone string ( & “-01:00”), as I’ve started from a DateTime value only:
DateTime.ToText(DateTime.From(#datetime(2020,10,11,12,0,0)), "yyyy-MM-ddThh:mm:ss") & "-01:00"
Edit 17th November 2020:
Ben Gribaudo just came up with a very elegant solution that uses the DateTimeZone.ToText – function. In there you can use the 2nd parameter for the format. A simple “o” does the magic. Funny, as this totally represents my facial expression when reading it 🙂
= DateTimeZone.ToText(#datetimezone(2020, 10, 11, 12, 0, 0, -1, 0), “o”)
= DateTimeZone.ToText(DateTimeZone.From(#datetime(2020,10,11,12,0,0)), “o”)
Enjoy & stay queryious 😉
Thanks Imke
I just converted one of my Excel reports into a web application. I am using the same data and the web tools expect an ISO date. When I faced the problem, I went back to PQ/Excel expecting to find an option. However, to my suprise, this was not the case!
This is Great blog post. . I am using the same data and the web tools expect an ISO date. Regularly using an APS Score Calculator keeps you aware of your academic standing. For those still in school, it can highlight areas for improvement to reach target scores.