SharePoint date differs from Power BI date

[dmouser]

Hello.  I'm having this problem as well.  Appears hours are showing 4 hours in the future.  Admittedly I am using an older version of desktop, but I do not have the ability to update/change.

I tried the workaround, but can't get through Step 3.  I'd like to address in the query editor so as not to affect/redo my formulas within the workbook itself.

[dannn777]

this fixes the error

[CoRouff]

Power BI Desktop

Version : 2.61.5192.601 64-bit (août 2018)

I have the same issue and the lastest suggestion did not fix it.

Can anyone from the Power BI team react on this issue ?

Its quite annoying and a real struggle to get it working as intended without massive workarounds.

[pds]

This issue still persists.  None of the solutions work.

As a work around creating a calculated column in the SharePoint:

=DAY([Column with date])&"/"&MONTH([Column with date])&"/"&YEAR([Column with date])

Then I changed the format of the column in PowerBI to Date and worked on from there.

This is a real pain as it throws out month end calculations.

[Anonymous]

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

[Anonymous]

Bumping this.

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.

Any help?

[Anonymous]

Last bump.

I have just resorted to adding an hour to the date before I convert from datetime to date.

[unrly]

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.

1. Open Power Query
2. Change Data Type to Date/Time/Timezone
3. Change Date & Time Column, Time to Local Time
4. 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...

[Anonymous]

Hi

I just followed your steps unrly and it does seem to work, thanks.

[Ricsta]

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.