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

Salesforce Data Import to PowerBI - Time Stamp Issue

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

 

Kory09_0-1620941377177.jpeg

 

Thanks, Kory 

2 ACCEPTED SOLUTIONS
edhans
Super User III
Super User III

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

edhans_0-1620963944878.png

 

becomes

edhans_1-1620963980460.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

v-kelly-msft
Community Support
Community Support

Hi @Kory09 ,

 

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!

View solution in original post

2 REPLIES 2
v-kelly-msft
Community Support
Community Support

Hi @Kory09 ,

 

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!

View solution in original post

edhans
Super User III
Super User III

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

edhans_0-1620963944878.png

 

becomes

edhans_1-1620963980460.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors