Forum Discussion
Resolver IM transformation from GMT datetime to local datetime
Hi, this is my problem:
I have a table with a column listing time entries reported in the GMT timezone, and a column with the names of the actual timezones where they have been entered. I need to convert the GMT time entries in their local time.
I managed to create a table from the iata.org database reporting zone_name, time_start, gmt_offset. In this way I can add the gmt_offset to my GMT time entries to convert them in local time.
Now the challenge is that the gmt_offset changes over the time according to the local switching rules between solar and dst time and the iata.org database keeps trak of this in time_start.
So, what I did is to:
- LeftOuter join my table with the iata.org table using zone_name for matching
- Table.SelectRows(JoinedTable, each [time_start] <= [my GMT datetime])
- Table.Group(PreviousStep, {"my GMT datetime", "TimeEntry.Id"}, {{"max_time_start", each List.Max([time_start]), type nullable datetime}})
It should be OK, but now in my table I can see only 2 columns: "my GMT datetime" and "max_time_start", instead I would like to see all the remaining columns, especially the gmt_offset which I have to add to my "my GMT datetime" to get the local time.
Below my code
let
Source = Table.NestedJoin(#"Time Entries", {"Timezone"}, timezone, {"zone_name"}, "timezone", JoinKind.LeftOuter),
#"Expanded timezone" = Table.ExpandTableColumn(Source, "timezone", {"time_start", "gmt_offset"}, {"time_start", "gmt_offset"}),
#"Filtered Less Than" = Table.SelectRows(#"Expanded timezone", each [time_start] <= [my GMT datetime]),
#"Grouped Rows" = Table.Group(#"Filtered Less Than", {"my GMT datetime"}, {{"max_time_start", each List.Max([time_start]), type nullable datetime}})
in
#"Grouped Rows"
In addition I don't know if this is the best way to proceed. I think this is very inefficient considering the some million of rows of my table.
Thanks for any help
m
I think you're on the right track. Here's how I'd proceed:
let Source = Table.NestedJoin(#"Time Entires", {"time_entry_zone_name"}, timezone, {"zone_name"}, "timezone", JoinKind.LeftOuter), #"Expanded timezone" = Table.ExpandTableColumn(#"Merged Queries", "timezone", {"time_start"}, {"time_start"}), #"Filtered Rows" = Table.SelectRows(#"Expanded timezone", each ([time_start] < [time_entry_gmt_datetime])), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"time_entry_id", "time_entry_zone_name", "time_entry_gmt_datetime"}, {{"time_start", each List.Max([time_start]), type nullable datetime}}), /*---------------*/ /*New Steps Below*/ /*---------------*/ #"Merged Queries1" = Table.NestedJoin(#"Grouped Rows", {"time_entry_zone_name", "time_start"}, timezone, {"zone_name", "time_start"}, "timezone", JoinKind.LeftOuter), #"Expanded timezone1" = Table.ExpandTableColumn(#"Merged Queries1", "timezone", {"gmt_offset"}, {"gmt_offset"}), #"Added Custom" = Table.AddColumn(#"Expanded timezone1", "time_entry_local_datetime", each DateTimeZone.SwitchZone(DateTimeZone.From([time_entry_gmt_datetime]), [gmt_offset]/3600), type datetimezone) in #"Added Custom"
4 Replies
- AlexisOlson
Super User
I think you're on the right track. Here's how I'd proceed:
let Source = Table.NestedJoin(#"Time Entires", {"time_entry_zone_name"}, timezone, {"zone_name"}, "timezone", JoinKind.LeftOuter), #"Expanded timezone" = Table.ExpandTableColumn(#"Merged Queries", "timezone", {"time_start"}, {"time_start"}), #"Filtered Rows" = Table.SelectRows(#"Expanded timezone", each ([time_start] < [time_entry_gmt_datetime])), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"time_entry_id", "time_entry_zone_name", "time_entry_gmt_datetime"}, {{"time_start", each List.Max([time_start]), type nullable datetime}}), /*---------------*/ /*New Steps Below*/ /*---------------*/ #"Merged Queries1" = Table.NestedJoin(#"Grouped Rows", {"time_entry_zone_name", "time_start"}, timezone, {"zone_name", "time_start"}, "timezone", JoinKind.LeftOuter), #"Expanded timezone1" = Table.ExpandTableColumn(#"Merged Queries1", "timezone", {"gmt_offset"}, {"gmt_offset"}), #"Added Custom" = Table.AddColumn(#"Expanded timezone1", "time_entry_local_datetime", each DateTimeZone.SwitchZone(DateTimeZone.From([time_entry_gmt_datetime]), [gmt_offset]/3600), type datetimezone) in #"Added Custom" - AlexisOlson
Can you provide a sample file or at least some sample data to show what your various tables look like?
One approach would be to expand the table of local switching rules into essentially a cross-join of a calendar and list of locations so that you have the GMT offset for each date and location in a big lookup table. You'd then join this table with your entries table, expand the GMT offset column and use it to define a calculated column with the local time.
- maclura
Hi AlexisOlson ,
basically I have from one side the timezone table below
and on the other side the timeentry table below
the goal is to put on the same row for each time_entry-id the MAX ( time_start <= time_entry_gmt_datetime)
- maclura
Hi AlexisOlson ,
thank you so much for your support, I really appreciate it.
Your suggestion does work for the example I provided in my previous post, but doesn't solve my actual scenario.
In fact, my second table - which is a fact table - does include several other columns in addition to time_entry_id, time_entry_zone_name and time_entry_gmt_datetime, and when I try to expand them in the final transformation, I go back to the point [time_start] <= [time_entry_gmt_datetime], as if my grouping to find the maximum value of start_date was lost.
Anyway you deserve at least a kudos!
m
