Forum Discussion
SharePoint date differs from Power BI date
did you found a solution for this problem?
In Power Query, I enter the following, for a column called Date.
#"Replaced Value" = Table.ReplaceValue(<Source>,each [Date],each [Date]+#duration(0,1,0,0),Replacer.ReplaceValue,{"Date"}),For UK this will bring a 22/04 23:00 to 23/04 00:00, which I convert to Date 23/04. Outside Daylight saving, it would turn 23/04 00:00 to 23/04 01:00, which again would convert to 23/04. So for me this is enough.
There is also the suggestion which works of casting to date, then date time locale, then back to date (three separate steps).
I tried the date time locale alternative, which almost fooled me to think it was working, but as soon as I published the report online it reverted (I reckoned it used another source for the date time locale setting after being published).
We've had the bizarre situation where we have had a mix of correct dates and dates out by one hour within the same column on a List on SharePoint Online. Fortunately we also had the dates elsewhere so I could check them. I used this formula to correct them:
each if DateTime.Time([Lease date]) = #time(0,0,0) then [Lease date] else Date.AddDays ([Lease date], 1)
So if the Lease date is correct (ie with a time of 00:00:00), then it uses it otherwise it adds a day.
I've seen this behaviour on two seperate SharePoint lists in the last few weeks.
Ricsta , alltasksIT Did you ever find a solid solution when publishing your reports? I have the same issue, dates look fine in the Desktop after changing the locale to UK. But when I publish to the server, which is placed in UTC+2, the issue reappears.
My SharePoint site is Non-English and therefore has to be API-version 15. So changing this is not a solution.
Did you find the solution? I have the same problem. I was able to handle this change using -> change column format to Date/time/timezone> 'Locale' option > change it back to Date/time and It worked me but after report was published it is again back to what it was. Now, i have correct dates in the PBIX file but wrong dates on the pbulsihed report.
Changing the API Version to 14 instead of 15 worked for me. in all site non English ADN English it has worked very well.
Thank you!
