How do I add missing TimeZone information to data in Power Query?

[elietech]

Ok, so I'm having some issues with timezone, and it is baking my noodle trying to sort it all out.  Here is the problem: 

I am retrieving JSON data from a clients REST API and loading it into by BI report.  This data contains various date/time fields that are all in ATLANTIC time, but they don't include any TIMEZONE information, so as a result, when I load into PowerBI, the data is considered by BI to be UTC time, and then it converts it to my timezone (CENTRAL) to display it. 

Is there something I can do in powerquery to add timezone information to the date/time data before power query tries to convert it and assumes that it is UTC?  To be clear, in an ideal situation, I would like to deal with everything in UTC...but I need to do something to this data first.    Then to complicate all of this, these reports will be primarily be consumed using the BI service, so everything in that assumes UTC, so that the ATLANTIC times will be assumed as UTC and if I let the auto corrections run wild, I think it will just make a further mess of things. 

Essentially, what I would like to do is just as the first stip in PowerQuery, append something to the field that will cause PowerBI to recognize that it is Atlantic Time (of course, then we gotta dealy with Daylight Savings time too...ugh...)

Some of the time formats I get from API (they aren't consistent):

dd mmm yyyy hh:mm

dd mmm yyyy

dd mmm yy

yy mmm dd

hh:mm

[Greg_Deckler]

Wait, are you doing this in Desktop or the Service? Because I'm not following that if it is in the Desktop. You are saying that when the refresh happens in the Service that the incoming time is deemed to be UTC time and then it gets displayed as CST time, correct?

[elietech]

Building in Desktop.

The data is given to me in Atlantic time.  But, there is no timezone information tagged to it.  So, when I try to format it with Date/Time/Timezone it assumes it is UTC time, and then converts it into my local time. 

IE:  Date/Time comes in as 15 May 2020 0800

If I format as Date/Time/Timezone I get:  15 May 2020 0300 (PowerBi assumes the original time is UTC, then when I format as Date/Time/TimeZone, it is converting it to display in my local TimeZone.  So, the time becomes 0300 (UTC-5)

If all the times I had worked like this, it would be no problem, I could just forgoe the Date/Time/Timezone conversion and use just the straight Date/Time type.  But this doesn't work, because I need to relate it to another dataset that IS in UTC.  Basically, I just to convert everything I am getting into UTC to just take all this time zone silliness out of the equation. 

So, it boils down to this: 

Client Data (DataSetA):  Date/Time data is LOCAL to customer(ATLANTIC) time, but the Date/Time data isn't marked as such: "25 FEB 2020 13:00"  (this data is local to the client and represents their operations, when things occur for them, local time gets stored in DB)

External Data (DataSetB):  Date/Time data is UTC time, and the Date/Time data IS marked as such:  "2020-01-01T00:00:00Z"  (This data is from an external source, and is stored with TIME in UTC...it's weather data)

So, before I go digging in the API to see if I can change the way the data is stored and retrieved on the clientts end, is there a way I could transform the DATE/TIME data that comes from DataSetA and relate the date/time accurately to DataSetB.

[Greg_Deckler]

@elietech I'll continue to look at this but one question I would have is whether or not you are CERTAIN it is Power BI switching the time around on you and not the API? The reason I ask is that I cannot replicate this behavior with a local file or database, etc. If I point Power BI to a file or Enter Data query, etc. with date time in them like what you displayed, Power BI just brings them in without converting them to UTC. 

So, before you go down a rabbit hole, are you certain that the API you are accessing is not checking the time zone of the incoming request and doing the timezone conversion or something like that? I don't know, I'm just asking because I cannot replicate the behavior you are describing.

I mean the easiest example I have is:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRV8E2sVDAyMDJQMLCwMjBQitVBFTU0wCpqDhaNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetime}})
in
    #"Changed Type"

[elietech]

Ok, so here is exactly what I'm seeing.  Perhaps I have been confusing the issue with my descriptions...I know I'm confusing myself. 

Raw Time Data from API.  This is just JSON data, the API isn't smart enough to give me data for my timezone, it is in the timezone of the clients system. 

25 FEB 2020 13:00

So then I cange it to type "Date/Time/Timezone" and I get this:

2020-02-25 1:00:00 PM -06:00

Still the same time, but the incorrect TimeZone is getting applied.  I believe that because the raw data doesn't include the timezone offset, it just assumes it to be local.  So it applies a -6 Hours timezone offset (well, -5...daylight savings), but not the timezone of the Raw Data.  The RAW data is -4 (well, technically -3 right now cause of daylight savings)

What I want to be able to do is somehow reformat the raw data to include a timezone offset indicative of what it represents.

I just don't know what format PowerBI is expecting it in to be able to discern the Timezone information out of it.

If I have this: 

25 FEB 2020 13:00

I want to transform it into something that PowerBI can understand a timezone from, or is this even possible...like this as an idea

2020-02-25T13:00:00-04:00

and then apply the Date/Time/Timezone Type to it. 

And maybe that's my answer, I just have to do alot of juggling with the text of the fields to reformat it and manually add that info...unless there is a builtin function that can do it for me. 

[elietech]

Well, couldn't figure out if there was a function to make this work or not, so I ended up just transforming the date / time column manually to get in the in the proper format, and add the Timezone offset for the clients source data. 

Basically, just EXTRACT by DELIMETER (each instance of a space), then rearanged the columns in Power Query, Merge columns back togeter, and add a custom column to determine UTC offset for ATLANTIC time based on Daylight savings or not. 

So, I changed transformed the original date/time format of this:

25 FEB 2020 13:00

To This: 

2020-02-25T13:00:00-04:00

Then converted the column to type Date/Time/Timezone.

And finally, added a custom column using this function:  

DateTimeZone.SwitchZone(date,0)   (date being the new date I transformed, and 0 representing the UTC Offset i desired.)

And all is right with the world again.  I can now relate it to other Date/Time columns that are in UTC and PowerBI takes care of all the timezone shennanigans automatically.

Thanks for the help...sometimes it's just good to talk things out to make the problem clearer.

[Greg_Deckler]

@elietech - OK, now I understand where we were seeing different results! I was just converting it to a DateTime and not a date/time/timezone. Thanks for explaining what you were doing and seeing. It's really good documentation and knowledge to have so I appreciate, learned some things today! 🙂

[elietech]

Me too!  Honestly, I've been working with this data for months, and it's never been an issue because we've only been relating it back onto itself.  But of course, requirements have changed and now there is a second external dataset, and need to relate it on time...well, not being in the same time zone, and missing timezone info from the first dataset, made it the mess that it was.  

[Greg_Deckler]

There are quite a few timzone functions for Power Query:

https://docs.microsoft.com/en-us/powerquery-m/datetimezone-functions