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

ericOnline's avatar
ericOnline
Post Patron
7 years ago

Hello v-danhe-msft. Thank you for the link. I believe I read through this post (and a few others on the topic) yesterday before posting. 

 

The post begins with "...Assuming currently we have the Time and UTC column in a table, where [Time] stores the actual UTC time, [UTC] stores the changed hour count between client and UTC."

 

I don't have a dedicated table with a Time and UTC column. The data table I'm operating on does have a Time column in UTC format, but it is amongst 12 other columns.

 

Do I need to create a dedicated TimeTable to make the translations work?

 

Thank you

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

     

  • ericOnline's avatar
    ericOnline
    Post Patron
    7 years ago

    v-danhe-msft, this is an excellect solution and worked very well. I'd recommend this for anyone looking to do TimeZone conversions in their data! (from my novice point of view :) )