Powerquery, If function with dates

[ElliotP]

Evening,

A little backstory. The data I'm pulling is for an australian business operating in AEST but the Point of Sale provider (Square) from their API provides timestamped datetime values in UTC. As far as my understanding goes there isn't a simple way to convert from UTC to AEST (difference of +10hrs).

When I try to change the datetime column type from text to datetime while having the Australian Locale, some values error out. When I change my Locale to United States, they convert easily.

After much research, I've found the best way to convert this datetime value from UTC to AEST is through either a simple duration change or by a two setp Datetime.Add Zone and DateTime.ToLocal with an offset. In regards to the second option this doesn't work easily as my locale of powerbi is set to United States due to the aforementioned error issue.

So, I'm using the duration solution to convert my UTC value to AEST.

My current powerquery code is:

[list_created_at] + #duration(0, +10, 0, 0)

This fails for daylight savings which is where I feel the IF function comes in most handy; but I'm not sure of the powerquery code as my powerquery with dates is a little foggy and I can't seem to find the sweet spot or a guide.

So, daylight savings in australia recently was between the 2nd of October 2016 and the 2nd of April 2017.

My current code as so it adds one hr to the time value during this period;

if (([Custom] > "02/09/2016 T00:00:00 AM" < "02/04/2017 00:00:00 AM") + #duration(0, +1, 0, 0))

Feelings on how to write this if function?

I'm pulling my data from an sql table where the datetime value is held as a NVARCHAR type as it was deserialized from json.

[MarcelBeug]

My recommendation would be to create Excel files with datetime switches for each time zone you require,

save these in a folder, combine them and use a function to convert UTC time to local time for a particular time zone.

Edit: this approach can also be adapted to convert a datetime in one time zone to a datetime in another time zone.

So far the summary, now the details.

The following query will create datetime switches between parameter StartDate and Parameter EndDate (so you need to define these parameters first) for the time zone on your local computer (so for multiple time zones, you need to adjust your time zone each time).

You can use 1 Excel file with the query below and save the result each time with a filename that reflects the time zone.

Don't refresh the results when your computer is set to another time zone!

let
    Source = {Number.From(StartDate)+1..Number.From(EndDate)+1},
    UTC1Numbers = Table.FromList(Source, Splitter.SplitByNothing(), {"UTC1"}, null, ExtraValues.Error),
    UTC2Numbers = Table.AddColumn(UTC1Numbers, "UTC2", each [UTC1] - 1),
    UTCDateTime = Table.TransformColumnTypes(UTC2Numbers,{{"UTC1", type datetime}, {"UTC2", type datetime}}),
    UTC1DateTimeZone = Table.AddColumn(UTCDateTime, "UTC1+Zone", each DateTime.AddZone([UTC1],0,0)),
    UTC2DateTimeZone = Table.AddColumn(UTC1DateTimeZone, "UTC2+Zone", each DateTime.AddZone([UTC2],0,0)),
    Local1 = Table.AddColumn(UTC2DateTimeZone, "Local1", each DateTime.From([#"UTC1+Zone"])),
    Local2 = Table.AddColumn(Local1, "Local2", each DateTime.From([#"UTC2+Zone"])),
    Keep = Table.AddColumn(Local2, "Keep", each if [UTC2]=DateTime.From(StartDate) then true else ([Local1]-[UTC1])<>([Local2]-[UTC2])),
    RemovedColumns = Table.RemoveColumns(Keep,{"UTC2", "UTC1+Zone", "UTC2+Zone", "Local1", "Local2"}),
    FilteredDates = Table.SelectRows(RemovedColumns, each [Keep]),
    DrillDownToQuarters = Table.AddColumn(FilteredDates, "UTC", each {(Int64.From([UTC1])-1)*96..Int64.From([UTC1])*96}),
    RemovedColumns2 = Table.RemoveColumns(DrillDownToQuarters,{"UTC1", "Keep"}),
    Expanded = Table.ExpandListColumn(RemovedColumns2, "UTC"),
    DividedBy96 = Table.TransformColumns(Expanded, {{"UTC", each _ / 96, type number}}),
    UTCLess1Quarter = Table.AddColumn(DividedBy96, "UTCLess1Quarter", each [UTC] - 1 / 96, type number),
    QuartersToDateTime = Table.TransformColumnTypes(UTCLess1Quarter,{{"UTC", type datetime}, {"UTCLess1Quarter", type datetime}}),
    UTCWithZone = Table.AddColumn(QuartersToDateTime, "UTCWithZone", each DateTime.AddZone([UTC],0,0)),
    UTCLess1QuarterWithZone = Table.AddColumn(UTCWithZone, "UTCLess1QuarterWithZone", each DateTime.AddZone([UTCLess1Quarter],0,0)),
    Lokaal = Table.AddColumn(UTCLess1QuarterWithZone, "Local", each DateTime.From([UTCWithZone]), type datetime),
    LokaalMinKwartier = Table.AddColumn(Lokaal, "LocalLess1Quarter", each DateTime.From([UTCLess1QuarterWithZone])),
    Keep2 = Table.AddColumn(LokaalMinKwartier, "Keep2", each if [UTC]=DateTime.From(StartDate) then true else ([Local]-[UTC])<>([LocalLess1Quarter]-[UTCLess1Quarter])),
    RemovedColumns3 = Table.RemoveColumns(Keep2,{"UTCLess1Quarter", "UTCWithZone", "UTCLess1QuarterWithZone", "LocalLess1Quarter"}),
    DSTSwitches = Table.SelectRows(RemovedColumns3, each [Keep2]),
    RemovedColumns4 = Table.RemoveColumns(DSTSwitches,{"Keep2"})
in
    RemovedColumns4 

With WIndows time zone (UTC+10:00) Canberra, Melbourne Sydney, StartDate 1/1/2015 and EndDate 12/31/2019, this looks like:

Now you can combine the files from the folder to the following result, e.g. for 2 zones (I removed ".xlsx" from the filenames and renamed this column to TimeZone):

I called this table DateTimeTables.

Now you need a function to convert UTC datetimes to local datetimes for a particular timezone:

(DateTimeZone as text, DateTimeUTC as datetime) as datetime =>
let
    Source = DateTimeTables,
    Filtered = Table.Last(Table.SelectRows(Source, each [TimeZone] = DateTimeZone and [UTC] <= DateTimeUTC)),
    Local = DateTimeUTC + (Filtered[Local] - Filtered[UTC])
in
    Local

I named the function fnUTCtoLocal and created the following example query:

let
    Source = List.DateTimes(#datetime(2015,1,1,0,0,0),20,#duration(90,3,10,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "UTC"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"UTC", type datetime}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "LocalAustralia", each fnUTCtoLocal("(UTC+1000) Canberra, Melbourne, Sydney", [UTC]))
in
    #"Invoked Custom Function"

resulting in:

Now you only need to watch out for any Windows updates if governments decide to change their time zone policy:

https://blogs.technet.microsoft.com/dst2007/

[ElliotP]

This is amazing. Thank you so much for the response, this is truly incredible.

After reading through I'm not sure how to create the original excel file with the different time zone columns and the switches.

I should be using this excel file in conjunction with my date table and not my data table? As the data in my data table is inconsistent in terms of it's sporadic, some every few seconds, some every few minutes, hours without any rows,etc

[MarcelBeug]

For the table with time switches, you only need to run the query code I supplied, but first you need to create parameters StartDate and EndDate as illustrated by this video: