Has there been any found solution to this? With the clocks going forward last Sunday, data I am pulling through is now on the incorrect day (instead of 01/04/2019, they read 31/03/2019 23:00:00). Changing the Locale etc doesnt seem to work. Note: This is with a simple: Query->From SharePoint List ->Link to list.
If I change the APIVersion from 15 to 14, it works, but this seems like a bad idea as I saw a post commenting that this causes issues with publishing.
Is there any way to completely fix this? It is clearly daylight saving messing things up
I have seen this issue posted many times with no clear resolution for it. At the moment, all I can think of is to add an hour to the dates to bring them to the correct date, but that doesn't seem reliable. Changing the Locale doesn't make a difference either. Only changing the API Version to 14 does.
Just came across this thread when having the same issues. I seem to have sort of worked it out, I have a Date/Time column in SP that comes in as text to Desktop and it is jumping ahead 6 hours. Note this is only for local time I believe.
Open Power Query
Change Data Type to Date/Time/Timezone
Change Date & Time Column, Time to Local Time
Change Data Type to Date/Time to remove the Timezone
No idea why this is doing it, but it gets me the correct time now. Now, if only there were more visuals that worked with time...
I have the same issue, and have had it for months now. I too thought I had found the solution in the 4 steps pointed out by unrly above, but as stated this is only for local time. As soon as I publish, the problem reappears. Eventually I had to go for the solution of adding a couple of hours. However, now I am in a position where I am querying a list that is integrated with a third application (MS Project) which by default sets the time in the DateTime to actual times and not to 00:00 like SharePoint-lists do when opting to not include time in DateTime-columns of lists.
The result of this:
When updating the date in MSP, the date is set correctly (think it still deducts an hour, but with all Time-variables being set between 07:00-17:00 by default depending on whether it is Start- or Finish-date this is not a big problem). However, if I update the same row in the list-version the Time-variables uses SharePoint-defaults (00:00) instead of MSP-defaults. In other words, if I update in MSP I get one result, but if I update in the list-function I get another. This is problematic as it is intended for different users, some preferring to work in MSP and syncronizing to the SharePoint-list and others preferring to work directly in the SharePoint-list itself.
I realize that I can use the same solution as long as the Time-variables are not altered (as 07:00 becomes 05:00 and is still within the same Date), but it is surely not a flawless option. Another application might have another default Time-variable, some users might have the need to set the Time-variable to 23:00 or 01:00, etc.
A proper solution to this problem would surely be welcomed.