Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
HenryH
Regular Visitor

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 45

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. 

I have tried that but did not work for me. For now, i am using alternative solution of adding one extra day to the actual date but i don't want to rely on this in long term.

Anonymous
Not applicable

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

Anonymous
Not applicable

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.

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 🙂

 

alltasksIT
New Member

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

cmoosbrugger
Regular Visitor

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.

Anonymous
Not applicable

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.

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. 

Anonymous
Not applicable

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 regards

Russell

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.

Anonymous
Not applicable

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

Guggymom
New Member

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:

 

 

 

Hi JamesCook,

 

I have tried your workaround, but it isn't working for me. My workaround was to create calculated columns in sharepoint that retrieve the day, month and year and build the date together in power BI.

Hi Fyouri,

 

I checked again and have made an update to step 3, the datatype on the new column should have been datetime, not date.

 

Proof it works on my side is that the original column contains unique values (I set this on the sharepoint list) However when imported, we get duplicates on dates where UTC+1 kicks in in UK late March. Then, when we return to UTC, we get a missing day. The new column contains no duplicates or missing columns enabling it to create a many to one relationship with other tables in the model.

 

Give it a try. I'd be interested to know if it works for you.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.