Forum Discussion
Problem with DateTime value if read from SharePoint
I found a solution (workaround) by myself. Maybe it is not the best way, but seams to be working.
What I do is I create calculated column in the SahePoint, explicitly forcing datetime value to be converted into decimal datatype at the SharePoint site.
Later, in Power Query, I just convert the correctly-converted-decimal-number into DateTime datatype, and I get what I need in the correct way.
Ok. In terms of the initial problem, I think that needs to be fixed, or at least fully understood, before you can move onto broader date/time/timezone requirements for the report(s). If you don't understand what is causing that issue, any further work could be meaningless. For my part, I'm still of the mind that it's an issue with regional settings, possibly in SharePoint where different objects can have different regional/timezone settings. This isn't something I can really help further with, I'm afraid, it's just a case of going through each system/object and exhausting all regional setting options.
Once fixed/understood, you should have the proper basis to decide whether you need to make further date/time/zone considerations for a global audience. This is hugely dependent on the type of data you're reporting and what your end-users' expectations are in terms of the dates/times that they see, so, again, is really quite specific to your scenario.
If you do need to go down the route of normalising your timezones then the DateTimeZone functions that you have, and this good article by Miguel Escobar, should give you the best start at thinking through and implementing what needs to be done:
https://www.thepoweruser.com/2019/10/21/handling-different-time-zones-in-power-bi-power-query/
Sorry I can't be more more helpful, but this type of decision is so very specific to your own scneario, there's no 'one-size-fits-all' type of solution.
Pete
I found a solution (workaround) by myself. Maybe it is not the best way, but seams to be working.
What I do is I create calculated column in the SahePoint, explicitly forcing datetime value to be converted into decimal datatype at the SharePoint site.
Later, in Power Query, I just convert the correctly-converted-decimal-number into DateTime datatype, and I get what I need in the correct way.
facing the same issue and did everything you have done but still my time is -4 hours. I have so many date columns that I can't use work around to add more columns.
This is very old problem and MS is unable to provide a solution.I think the only thing that wasn't covered in the initial discussion above was browser language.
The way I see it now, many months on, is as follows:
Windows language/region (set correctly to your region) >> PBI Desktop language/region (set to pass-through using Windows/default/application) >> PBI Service (set to Default Browser Language) >> Browser (set correctly to your region).
I think the overall control that the browser has over how the final values are presented, regardless of how previous values have been set in the chain above, is often completely overlooked.
Unfortunately, this also effects every end-user of reports so, if each user's browser is not set up as you want your reports to display, then you've got a large task on your hands!
Pete
Thanks, so I inserted and extra column in Excel and added Time(4,0,0) to correct and it worked. I hid the original column just not to confuse the users.
My time is +4UTC so its fine now but it is realy awakward.
