cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jhagerman
Frequent Visitor

Understanding datetimezone type with dataflows

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

jhagerman_0-1624459058410.png

Power query

jhagerman_1-1624459154166.png

Report

jhagerman_2-1624459214251.png

 

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

1 ACCEPTED SOLUTION
jhagerman
Frequent Visitor

Thank you so much for responding @Eyelyn9 ! 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.

View solution in original post

2 REPLIES 2
jhagerman
Frequent Visitor

Thank you so much for responding @Eyelyn9 ! 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.

View solution in original post

Eyelyn9
Community Support
Community Support

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)

Add a custom column.PNG

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:

final date format.PNG

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.