Sharepoint Data, date is off by one day

Question

Yes another one of those off by one day issue ! So when I use sharepoint list as data source for Power Query some of my records have the date off by one day .

I have read I think all of the previous responses on this subject and I can't seem to resolve it. My issue is that some of my dates are correct and some are off, even though my column in sharepoint is a date only not a date/time field. so I can't do the custom column with one day off-set because it fixes some and then the others are wrong. There's gotta be some other way to solve this? It just seems nuts. I've tried the locale etc.. Any other ideas?

By the way if I export from Sharepoint and user Power Query then the dates are fine , it's only if start with Power Query and excel that the dates are off

marty_seelbach · Answer

I've recently had same issue. Checked all the Time settings were matching but without sucess.

Then found someone recommened the Get Data using the Online Services with SharePoint Online List and switch the Implementstion setting option from 1.0 to 2.0. This worked for me.

Instead of going through the Get Data process I just changed my code in Advanced Editor from

Source = SharePoint.Tables(p_SharePointRoot & p_SharePointSite2, [ApiVersion = 15]), to

Source = SharePoint.Tables(p_SharePointRoot & p_SharePointSite2, [Implementation="2.0", ViewMode="All"])

Note p_SharePointRoot & p_SharePointSite2 are just parameters for my SP locations you can just use the SP List url here too.

Posted response to help anyone encountering issue as it took me a little searching and tweaking files / platforms before resolving.

knp · Answer

I've had similar issues.
Even though it looks like a date only field, it may not be. Timezone info could still be causing problems.
For me, the issue was with the SharePoint site settings.