Forum Discussion
How do I add missing TimeZone information to data in Power Query?
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.
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.
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! 🙂
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.
