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

Re: SharePoint date differs from Power BI date

did you found a solution for this problem?

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

 

Ricsta Frequent Visitor
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). 

ianw1000 Regular Visitor
Regular Visitor

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.

nickdewitt Frequent Visitor
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
sorenblatt Frequent Visitor
Frequent Visitor

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. 

Lorence Frequent Visitor
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...

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors