cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Re: SharePoint date differs from Power BI date

did you found a solution for this problem?

Highlighted
Anonymous
Not applicable

Re: SharePoint date differs from Power BI date

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).

 

Highlighted
Frequent Visitor

Re: SharePoint date differs from Power BI date

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). 

Highlighted
Helper II
Helper II

Re: SharePoint date differs from Power BI date

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.

Highlighted
Frequent Visitor

Re: SharePoint date differs from Power BI date

Yeah this is pretty bad, made worse by the fact that this has worked in the past the same on the service as it did on the desktop. Someone has seriously dropped the ball here, we don't seem to have control over what locale the service performs it's refresh in and even when you specify the locale as in the previous post, it still does not work the same on the service as it does on the desktop.

 

I'm in the uk, so eventually I just gave up, converted it to a float, rounded it then converted back to a date. That should work for anyone who is less than 12 hours away from UTC.

 

Stupid problem, should not be resurfacing on reports that I wrote years ago and applied the datetimezone conversion to appropriately.

Highlighted
Anonymous
Not applicable

Re: SharePoint date differs from Power BI date

@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. 

Highlighted
Frequent Visitor

Re: SharePoint date differs from Power BI date

This seems to work fine! not too difficult but requires work on each date column.

 

http://geekswithblogs.net/darrengosbell/archive/2019/02/01/power-bi-ndash-fixing-dates-from-sharepoi...

Highlighted
Frequent Visitor

Re: SharePoint date differs from Power BI date

I can confirm this worked for me!

Highlighted
Frequent Visitor

Re: SharePoint date differs from Power BI date

These solutions work only if you are not republishing back to a server/

 

For me, ive tried all of the above, it works for a little while then the server weirdly reverts back to the date -1.

 

The only solution ive now gone with is reverting back to api 14. Im hoping it works as ive reepublished to the server. So far so good.

Highlighted
Post Patron
Post Patron

Re: SharePoint date differs from Power BI date

Has anyone confirmed their locale is set correctly in Sharepoint for the entire site?

 

https://support.microsoft.com/en-gb/office/change-regional-settings-for-a-site-e9e189c7-16e3-45d3-a0...

 

And also in Power BI Service? (NOT power BI Desktop)

 

https://docs.microsoft.com/en-us/power-bi/supported-languages-countries-regions

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors