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:
This represents the 11th October 3pm in UTC -1 timeszone.
Steps to convert DateTime to ISO 8601
If I enter:
into the formula bar, it will be converted to :
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:
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"
Enjoy & stay queryious 😉