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

  • HotChilli's avatar
    HotChilli
    Community Champion
    7 years ago

    I think you want to create durations from your data and just add(+) them to your datetime.

    yourdatetime + #duration()

    duration is (dd,hh,mm,ss) so a duration of 1 hour is (0,1,0,0)

     

    The datetimezone will create you a datetime (yy: mm: dd : hh : mm : ss) with an additional offset of hh : mm, which is not really what you want, I think.

    • ericOnline's avatar
      ericOnline
      Post Patron
      7 years ago

      Hi HotChilli. How is your suggested implemented?

       

      I have a table of 1800 rows. Depending on the ID# column of each record I want to programmatically add/remove hours to a DateTime column that is in UTC format.

       

      I'm new at PowerBI so need some more details.


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