cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HenryH Frequent Visitor
Frequent 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

35 REPLIES 35
greggyb New Contributor
New Contributor

Re: SharePoint date differs from Power BI date

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.

HenryH Frequent Visitor
Frequent Visitor

Re: SharePoint date differs from Power BI date

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.

kmarcum New Member
New Member

Re: SharePoint date differs from Power BI date

Ive just noticed this same thing but even my modified and created dates are wrong.  Did you ever figure this out?

HenryH Frequent Visitor
Frequent Visitor

Re: SharePoint date differs from Power BI date

Nope, didn't yet figure it out.

Have you tried creating or modifying something at different times during the day to make sure it's not a time zone issue?

Guggymom New Member
New Member

Re: SharePoint date differs from Power BI date

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?

Fyouri Frequent Visitor
Frequent Visitor

Re: SharePoint date differs from Power BI date

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?

 

 

 

JamesCook Frequent Visitor
Frequent Visitor

Re: SharePoint date differs from Power BI date

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:

 

 

 

Fyouri Frequent Visitor
Frequent Visitor

Re: SharePoint date differs from Power BI date

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.

RussellW Visitor
Visitor

Re: SharePoint date differs from Power BI date

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,346)