Forum Discussion
SharePoint date differs from Power BI date
Hi,
I have imported data from a SharePoint list into Power BI including one column containing a date.
The date in Power BI differs 1 day from the date in SharePoint.
e.g. Sharepoint: 1/6/2016 > Power BI: 1/5/2016
Is this related to time zone differences and how can I fix this except for adding a new column adding one day?
Kind regards,
Henry
45 Replies
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.
- Open Power Query
- Change Data Type to Date/Time/Timezone
- Change Date & Time Column, Time to Local Time
- 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...
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.
this fixes the error
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.
Having the same issue!
When are they fixing this?? Or is it because of incorrect regional time settings within Power BI/Sharepoint?
We add items in the sharepoint list with current dates (probably at midnight), but an hour gets subtracted when importing the dataset in Power BI. So "Dec 1st - 00:00" becomes "Nov 30th - 23:00".
Irritating when doing monthly analysis.. A workaround is to create calculated columns in the sharepoint list with year, month and day - BUT it can't be used as a timestamp-filter within Power BI.
This issue was resolved, yet since this month update it started again. could you please have a look ?
i check with the API 14 and it works. but this is just a work around.
I know this isn't strictly related, however there are similarities to what I've just encountered while working on an SPFx solution where we're duplicating SharePoint Events that have a recurrence set.
There appears to be a mismatch between the server generated timestamp (which appears to be UTC) and the FieldValuesAsX (X = Text or HTML). I believe that the base DateTime properties persist to maintain back end continuity, and the SharePoint Regional settings dictate what is seen within FieldValuesAsX.
Here's what I saw within the console.
odata.type:"SP.Data.EventsListItem"
EndDate:"2018-08-11T05:00:00Z" EventDate:"2018-08-10T21:00:00Z" FieldValuesAsText: EndDate:"11/08/2018 5:00 p.m." EventDate:"11/08/2018 9:00 a.m."
odata.type:"SP.FieldStringValues"I don't know whether this will help anyone here, however this enabled me to track down my non-PowerBI issue that is similar...
I'll see myself out now :)
Hi
A workaround for this rather than creating a column to reformat the column is to change the ApiVersion to 14 (mine is currently 15) in the query that is used to pull the SharePoint list containing the dates. I noticed that the date columns using Api 15 was being pulled in as text, using 14 the values are pulled in as date and are not adjusted for time zone/day light saving.
I found this work around when using a column containing multiple choice data. I'm not sure as yet what impact changing the Api will have on other aspects of the query. Happy for others to comment that this is a good idea or not, but it seems to work!
Kind regardsRussell
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.
HI!
Thanks for the workaround! We have a SP list with Lease Renewal dates and they were coming through as 11:00pm on 31/03/2016 rather than at 00:00 on 01/04/2016 (just one instance of several)
This is clearly a bug with the later API. Has it been reported anywhere so I can add my twopenneth?
Thanks.
Ian Watkins
HI Heny
I am using Power BI Desktop.
similar issue
In the query editor -
right click date column
I changed Type to date/time/timezone
then I did a 2nd step - change type - using localte where I picked English (Aus)
So everything looks great in Power BI desktop - until I publish the report to the Power BI service where the date issues appears again
This seems to work fine! not too difficult but requires work on each date column.
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.
Changing the API version from 15 to 14 worked for me. Deleted all steps except source after changing and redid what I had done.
Question is, what ramifications does changing the api version have?
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.
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
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?
Is the date being stored as a date-time data type in the underlying SharePoint list and in Power BI?
Is there any point in your query where the date is cast to an integer or other numeric type and cast back to a date type? SQL Server's date time types have a numeric representation where the date-time represented by 0 is one day more recent than that of Tabular.
In SharePoint the date is indeed being stored as a "Date and Time".
In Power BI, in the initial Query result, the date is already changed to one day earlier.
I did find out that dates that are stored by SharePoint itself (created date, modified date, etc.) are correct in Power BI.
However, they are also of type "Date and Time", so I don't see what's different.
Ive just noticed this same thing but even my modified and created dates are wrong. Did you ever figure this out?
I am having this exact same problem too, except the data sourc is Salesforce. In NYC, the data appears exactly right, but in the UK, everything comes in a day earlier. Even when I reformat the date in powerquery as date/time/timezone, it make no difference. We are working with case filing dates so this cannot be off by even one day. Can anyone help with an answer?
I'm having the same issue and I think it is timezone based. I have a list with every row the first day of the month:
1/1/2014
1/2/2014
1/3/2014
1/4/2014
In power BI this comes out as
31/12/2013 23:00:00
31/01/2014 23:00:00
28/02/2014 23:00:00
31/03/2014 22:00:00 -->this is probably due to daylight saving time.
Anyone got a solution for this?
Hi Fyouri,
I had the same problem, dates in the summer were changing to the previous day because an hour was being taken off due to daylight saving. I found the answer in this article, a similar issue the guy was having with a CRM.
You set the imported column to data type timezone and create a new calculated column based on it with a data type date.
Here are the steps:
1. In Query Editor, rename the offending SharePoint list date column rawDate or similar
= ( Table.RenameColumns(#"Changed Type",{{"Date", "rawDate"}})
2. Set the data type of this column to be Date/Time/Timezone
= Table.TransformColumnTypes(#"Renamed Columns",{{"rawDate", type datetimezone}})
3. Create a new calculated column that looks at rawDate, call it Date and set its data type to Date
= Table.TransformColumnTypes(#"Added Custom",{{"Date", type datetime}})
The result looks like this. You can see in row 1 the original date was 2/8/2015, the calculated one is 3/8/2015:
is there any news on this item? I got the exact same issue and it is annoying.
how can i change the api from 15 back to 14? the funny fact is it was working yesterday and the days before and just with today (I actually do notknow if there was an update or not) it does not work anymore.
Ni, I've noticed the same yesterday. I'm not sure if it was there before, but strange behaviour... Changing the API to 14 could work, but then I have to recreate my entire data table...
I would be happy about a solution too.
In any case, my column in SharePoint is set to Date only, I'm not sure why it comes over with a time to PowerBI in the first place.
You should just be able to edit the M code for the read from the SharePoint list and probably the first few lines of code without having to completely rewrite the query.
IIRC when I had to do this for another problem I started off a new query against the same List, changed the API to 14 and then expanded the data out until it was in the same structure as the original query. Then cut and paste the M code from the temporary query into the M code of the old query, probably adjust a few #stepname bits and off you go.
If I've misunderstood your problem, then please excuse my ramblings :-)
You understood right, I think. But with the different API, somehow some column names are also different, for example gets an extra "0" to the end, so I think at the end of the day, it will be easier to make it again step by step.
But this basic issue should be still solved, as this is just a workaround.
