Urgent Help Req - dynamic conversion of dateTime to local time zone

[smpa01]

My time zone is EST. I am fetching files from a sharepoint folder and building a dataflow. The file[Date modified] (type datetime) has huge role to play later in

       incremental refresh + Analysis through DAX (filter context, calendar table etc.). This is the value from sharepoint

Long story short, I need to utilize each file's [Date Modified] field and I want to have the value of that field exactly in EST.

When I build data flow, the [Date Modified] value that is reflected within dataFlow is in UTC.

I wrote this to convert the automatically converted [Date Modified] value to EST through this

let 
   a = [Date Modified],
   b = DateTimeZone.SwitchZone(DateTime.AddZone(a,0),-5),
   c = DateTimeZone.From(b);
   d = DateTimeZone.RemoveZone(c);
   e = DateTime.From(d)
in
  e

It worked well, till Daylight saving happened couple of days ago. So `-5` does not work anymore as all the [dateModified] are all 1 hour behind. It should be `-4` now.

Now, this dataflow is in incremental refresh -> powering dataset (which is also in incemental refresh). Is there any way to author the above function so that it dynamically knows when the daylight saving happens so that it switches back from `-5` to `-4` and vice versa, Is there any built-in- syntax available within PQ's that can get me out of trouble for this dataflow issue.

I have multuple dataflows (10+) which utilizes this and now, I need to change this syntax for each of them -> trigger a full refresh -> setting the incremental refresh back again-> and the same for Dataset.

It is too much to do for a large project like this. Is there any way to achieve this dynamically rather than me babysiting this function as per daylight saving and vice versa.

I am either looking for a dynamic PQ synatx or altering any method of workspace so that it prevents datflow to automatically convert datetime to utc.

Thank you in advance.

@ImkeF @AlexisOlson @CNENFRNL @otravers @bcdobbs @GilbertQ 

[smpa01 · Accepted solution]

Calling javascript to rescue for DST proofing

let 
   src=Web.Page(
        "<script>
            var offset= new Date().getTimezoneOffset();
            document.write(offset);
        </script>"){0}[Data]{0}[Children]{1}[Children][Text]{0}

in
src

[AlexisOlson · Accepted solution]

You should be able to get the local offset as I mentioned here:
https://community.powerbi.com/t5/Power-Query/Why-is-UTC-datetime-being-converted-to-local-datetime/m...

DateTimeZone.ZoneHours(DateTimeZone.LocalNow())

[smpa01]

This is great; returns 0 on df (client==power bi server; hence utc) and accurate offset on a dataset (client == actual client server; hence actual => EST in my case). To summarize, build a datset with Alexis method ( has an edge over js as does not need Web.Page to be configured within on prem gateway first in order to work) and recycle that ssas db elsewhere requiring offset treatment.

[smpa01 · Accepted solution]

Calling javascript to rescue for DST proofing

let 
   src=Web.Page(
        "<script>
            var offset= new Date().getTimezoneOffset();
            document.write(offset);
        </script>"){0}[Data]{0}[Children]{1}[Children][Text]{0}

in
src

[bcdobbs]

Never knew you could invoke JavaScript like that! Love it.

[smpa01]

yes, you can invoke js within pq as it has a browser inside (IE11 - so not the best of the browser). You can utilize (regex is a good use case) js but to a limited capacity. However, js works extremely slowly; (takes 1hr+ on a 40k dataset's regex manipulation on premium capacity..to simply put, it is not scalable). So I would not invoke unless I absolutely need it. In this case for instance, I will create a dataset with a single row table (select 1) from a sql db that is mapped to an on-prem gateway and invoke the above offset function on this table. Web.Page needs to be added to the same on-prem gateway in order for it to work and the sql table needs to be there so the scheduled refresh takes place. Then I will recycle this ssas db through all my df needs the offset treatment.