Forum Discussion

ericOnline's avatar
ericOnline
Post Patron
7 years ago
Solved

Convert utc to local time zone using Power Query

Hello,    Does anyone have a better example for how to add hours (DateTime.AddZone) to a date-time value? The docs are not clear on this: DateTime.AddZone(#datetime(2010, 5, 4, 6, 5, 5), 8) equals...
Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    Hi ericOnline,

    Based on my test, you could refer to below steps in query editor:

    Add custom column:

    if [ID]=1 then [Date]+#duration(0,2,0,0) else if [ID]=2 then[Date]+#duration(0,1,0,0) else null

    Result:

     

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

v-danhe-msft's avatar
v-danhe-msft
Microsoft Employee
7 years ago

Hi ericOnline,

Based on my test, you could refer to below steps in query editor:

Add custom column:

if [ID]=1 then [Date]+#duration(0,2,0,0) else if [ID]=2 then[Date]+#duration(0,1,0,0) else null

Result:

 

You could also download the pbix file to have a view.

 

Regards,

Daniel He

ericOnline's avatar
ericOnline
Post Patron
7 years ago

Aha! Interesting. This is the first time I've seen an IF statement used in M! (I'm very new). Thank you v-danhe-msft and HotChilli.

 

Hm. So taking this one step further: If I add a UTCOffset column to the data, how do I call this column in the query?

 

Example Table: 

 

| UTCTime  | UTCOffset  | AdjLocalTime    |

|------------|--------------|------------------|
| 12:00:00   |   -5              |   07:00:00          |

 

Example: 

 

If [ID]=1 
    then [Date] + #duration(0,[UTCOffset],0,0) 
else if [ID]=2 
    then [Date] + #duration(0,[UTCOffset],0,0) 
else null