M transformation from GMT datetime to local datetime

[maclura]

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

[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

[AlexisOlson · Accepted solution]

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"

[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)

[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.