Lookup based on start and end date/time

[tomislav]

HI, 

I have one table containing rules for conversion of local date/time to UTC time zone format. What I want to do is to convert local times in second table (from known time zone) into UTC time zone format, using rules of first table. Please advice how to do this in PQ? I tried creating  List of all dates/times (yyyy/mm/dd hh:mm:ss AM/PM format) within ranges (rules table), and then do the merge, but such method takes too long. Thanks

Tables 

[v-frfei-msft · Accepted solution]

Hi @tomislav ,

We can achieve that by DAX.

Column = 
VAR a =
    - CALCULATE (
        MAX ( RULES[OffsetFromUTC_ForLocalTime] ),
        FILTER (
            RULES,
            RULES[EndOfTimeZoneOffset] >= 'TABLE TO CONVERT'[CreatedDate]
                && RULES[StartOfTimeZoneOffset] <= 'TABLE TO CONVERT'[CreatedDate]
        )
    )
RETURN
    'TABLE TO CONVERT'[CreatedDate] + TIME ( a, 0, 0 )