How to force Power BI service to use Local timezone

user_3800000 0
Viewed 9390

I'm currently exporting data from Dynamics. There are a lot of date fields I need to export. By default they are all UTC timezone. I want to report on New Zealand timezone. I tried to approaches in Power Query:

1) use DateTimeZone.ToLocal: =Table.TransformColumns(#"dataset", {{"**UTC Date**", **DateTimeZone.ToLocal**, type datetimezone}}) 

2) use a specific timezone ("en-NZ"): =Table.TransformColumnTypes(#"dataset", {{"UTC Date", type datetime}}, **"en-NZ"**)

Both approaches work OK in Power BI desktop report, However once I published to Power BI service and after several refreshes (initially it was NZ time), the time turn back to UTC time.

I don't want to create extra columns in DAX and really want to try use Power Query. Is there any way to work it out?

microsoft-dynamics powerbi-desktop powerbi powerquery
2 Answers

I have faced the same issue a while back and came up with the following solution.

You can simply use a combination of RemoveZone and Addzone power query function to achieve this. Example below shows how to get Indian Standard Time(IST = +5:30)

= DateTime.AddZone(DateTimeZone.RemoveZone(DateTimeZone.UtcNow()),5,30)

Note that I used DateTimeZone.UtcNow() to always get the Universal standard DateTime and then convert this to the Indian time. You can use your own timezone values

Add the above code in place of DateTimeZone.ToLocal in your code.

Documentation to DateTime.AddZone

user_69082820

Power BI:

Use this Power Query function to convert times from UTC to Local Time, here its mountain time, but you can set to Whatever New Zealand Time is

let 
    ConvertDateColumnstoMountainTime = (sourcetable as table) as table => 
    let
        TargetColumnList = Table.ColumnsOfType(sourcetable, {type datetime, type nullable datetime, type datetimezone, type nullable datetimezone}),  
        AdjustTimeZones = Table.TransformColumns(sourcetable, List.Transform(TargetColumnList,
            (name) => {name, (date) => if date <> null
                then DateTime.From(DateTimeZone.RemoveZone(date) + #duration(0,CalculateUTCOffset(date,null),0,0))
                else null})),
        //Above returns type as text. Need it explicitly as Date. 
        AdjustColumnTypes = Table.TransformColumnTypes(AdjustTimeZones,List.Transform(TargetColumnList,
            (name) => {name, type datetime}))
    in AdjustColumnTypes

in ConvertDateColumnstoMountainTime
user_60851930
Related