Understanding datetimezone type with dataflows

[jhagerman]

Hello all, I have researched this extensively but I just do not understand what is going on and what is the best fix.

I have a dataflow using a query to a sql server database. My sql query is using "at time zone 'US Eastern Standard Time'" and it is including the correct timezone offset (-4). When I view this data in the dataflow, I also see the correct offset of -4 (the data type is datetimezone). When I view this column within power query of the report using this dataflow, I see the correct -4 offset (datatype is still datetimezone). However, when I create a table visual, the offset gets chopped off and the time isn't actually adjusted at all. What am I doing wrong? Every layer seems to contain the correct timezone information, but power bi is just not applying it in the visual. I understand there are functions to change the timezone, but I seem to already have the offset created correctly, so that doesnt' make sense to me that I should use one of those functions. If someone could point me in the right direction (happy to do my own research, but I'm stuck!) I'd so much appreciate it. Thank you!

Examples:

Dataflow

Power query

Report

Long story short: I want the report to display 8:45-4 = 6/2/2021 4:45:10 AM

[jhagerman · Accepted solution]

Thank you so much for responding @v-eqin-msft ! I did see that function but was concerned about dst if I have to put the offset in manually, since sometimes my timezone is -5 and sometimes it is -4. That is why I chose to do the conversion on the sql server with 'at time zone'.

I ended up calling MS Premier Support for this one and we landed on the following solution late yesterday should anyone else be struggling with this issue:

SQL query must convert to UTC then to my timezone to get the time correct plus the offset which we will truncate by converting to datetime.

select convert(datetime, CreatedDate at time zone 'UTC' at time zone 'US Eastern Standard Time') as CreatedDate from table

Now Power BI has the time correct and no timezone, so I use the datetime type in the dataflow. This query should adjust for dst for me from the sql side.

[v-eqin-msft]

Hi @jhagerman ,

Please try the following transform steps:

1. Convert [CreateDate] to Date/Time Type 

2. Add a custom column using the formula below:

=DateTimeZone.SwitchZone(DateTime.AddZone([CreateDate], 0),+8 ,0)

3.Convert [Custom] to Date/Time type 

4. Delete the original column, and you could rename the Custom column.

The final output is shown below:

Here is the whole M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtM30jcyMDJUsLAyMbUyNFCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CreateDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CreateDate", type datetime}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"CreateDate", type datetimezone}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"CreateDate", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each DateTimeZone.SwitchZone(DateTime.AddZone([CreateDate], 0),+8 ,0)),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type datetime}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"CreateDate"})
in
    #"Removed Columns"

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.