Salesforce Data Import to PowerBI - Time Stamp Issue

[Anonymous]

Hello,

I'm importing data from our Salesforce enviroment into PowerBI using Salesforce Objects data connector in PowerBI. For some unknown reason, the Time Stamp for Appointment Start Time is changing by 4 or 5 hours for each row. when imported into PowerBI. 

Where you -5:00 in the time zone stamp, the Appointment Start Time is 5 hours ahead of the actual time the appointment started. Where you -4:00 in the time zone stamp, the Appointment Start Time is 4 hours ahead of the actual time the appointment started. All time should be done using EST and I checked all time zones settings to make sure it is correct. Does anyone have any idea why this is happening and how to fix it? Please see the example data below. Appreciate the help in advance

Thanks, Kory 

[edhans · Accepted solution]

Simply change it from the Date/Time/Timezone format to Date/Time. It will adjust the time based on the TZ offset.

becomes

Note that you might need two steps to do this. If you went from text to date/time/timezone, you cannot change that to date/time, you need an additional step that change d/t/tz to d/t.

See this code for a full example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY3NNA3MjAyVDC1MjS1MjBQCPBV0DUAsZRidUDylhBpEytjAzTpWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetimezone}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Column1", type datetime}})
in
    #"Changed Type1"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

If you need more help, please post data using this info. We cannot use images for source data.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

Check the reference below:

https://www.poweredsolutions.co/2019/10/21/handling-different-time-zones-in-power-bi-power-query/

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[edhans · Accepted solution]

Simply change it from the Date/Time/Timezone format to Date/Time. It will adjust the time based on the TZ offset.

becomes

Note that you might need two steps to do this. If you went from text to date/time/timezone, you cannot change that to date/time, you need an additional step that change d/t/tz to d/t.

See this code for a full example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY3NNA3MjAyVDC1MjS1MjBQCPBV0DUAsZRidUDylhBpEytjAzTpWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetimezone}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Column1", type datetime}})
in
    #"Changed Type1"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

If you need more help, please post data using this info. We cannot use images for source data.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.