Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 usi...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous ,

 

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.