As mentioned in these threads:
PowerBI has a bug since the March release when it comes to reading files from Sharepoint and using the date fields provided by Sharepoint. This not only affects the desktop but also the service.
Going back to API Version 14 seems to be the most selected work-around, which is odd as one day this version will become obsolete causing mayor issues.
I'm finding data typing in Power Query extremely important, and believe this issue with API Version 15 is exactly that: we expect a Date/Time/Timezone field in an unified timestamp format that we can then use in a global context, but the API reads a Date/Time field using the locale of the sharepoint site and returns it using the locale of the Power BI (or Power Query) user leading to errors when going from mm/dd/yyyy to dd/mm/yyyy (21st of February 2020 on sharepoint reads as 02/21/2020 and is returned to the local Power application in the dd/mm/yyyy local where it fails as month 21 is invalid, luckely the 8th of February 2020 does not return an error and can be correctly displayed as 08/02/2020 on local).
When passing the date/time issue, we still need to handle the timeshift as a result of the difference in timezone between the sharepoint location and the Power user.
My first work-around always is to try to have the same regional settings on the sharepoint and the Power community using these sharepoints... so when your company is based in Brazil, most European countries and mainly uses dd/mm/yyyy, ask your sharepoint adminstrators to set the Locale and Timezone in (Site Settings / Site Administration / Regional Settings ) to the company default.
Of course, through voting we should remind Microsoft Power team that they need to ensure they think Global/Cloud rather than Local/Office in every aspect and work with timestamps which can be handled both at Cloud and Local level.
Not sure whether we will get an API Version 16 return a timestamp in universal format!
How has this not been solved yet 😞
Norway uses English and Norwegian 50/50 on software in business environments, we cannot force either. Microsoft has to make the timestamp universal! 😞
When I change to API version 14 i dont get any data at all.
Still getting the same issue as of today. I can't also use the "Changing to API 14" as a solution since when i can't get the sharepoint list that I want when I changed it. And as eFeM135 mentioned, this version would be obsulete moving forward so using this a workaround would be trouble in th future.
For my issue, it's not just in a matter of changing the hrs. All of my dates are 1 day behind and when changing into the date and time, i noticed that PBI imported all of the data into a standard 12:00am time so changing to locale wasn't the solution. Hoepfully there's another workaround to this
The issue is back in the April 2020 desktop. Workaround is still either to downgrade SharePoint.Files to ApiVersion = 14 , or to use SharePoint.Contents instead (with extra steps required in Power Query)
Thanks @lbendlin , using Sharepoint.Contents works ! 🙂 Are there any downsides we should know about?