cancel
Showing results for 
Search instead for 
Did you mean: 

Dates Incorect Import from SharePoint

Hi,

We are building a simple table report from a SharePoint Online list. The dates in the PowerBI column are one day before the dates in the SharePoint list (e.g 2 Apr 19 (SP) = 1 Apr 19 (PBi).

 

Some help would be appreciated.

 

Chris

Status: Delivered
Comments
v-qiuyu-msft
Community Support

Hi @Chrisstott

 

I'm not able to reproduce the issue on my side. 

What's your timezone settings for SharePoint online and OS, and regional settings for Power BI desktop?

 

Best Regards,
Qiuyun Yu 

v-qiuyu-msft
Community Support
Status changed to: Needs Info
 
Chrisstott
Regular Visitor

Thanks for the reply. In answer to your question:

SharePoint Online is UTC (London)

Power BI Desktop is English (United Kingdom)

 

Status:

The error is happening at the end of Mar and start of Apr. It seems to be the time portion of the date that is creating the problem

I have done two things to resolve in SharePoint

  1. Created a calcuated column to add 1 day to date column if after 30 Mar 19 
  2. Removed time  with TEXT() function

This has worked, but cannot be the system solution??

v-qiuyu-msft
Community Support

Hi @Chrisstott

 

Is your date column within the SharePoint list also has time value appended? 

 

Based on my research, the date time display in the Power BI desktop is using UTC zone. In your scenario, as London uses DST UTC+1 starts from 31 Mar 2019, if you type 02/04/2019 00:30 in SharePoint list, it's in UTC+1 format, when we retrieve data in Power BI desktop, it will display in UTC format then it displays 4/1/2019 11:30:00 PM. 

 

Best Regards,
Qiuyun Yu 

Chrisstott
Regular Visitor

Hi,

 

The date column is set to Date Only

Chrisstott
Regular Visitor

I have enclosed the example from the SP List and PowerBI Data set.

 

SharePoint List.JPGPower BI Data Set.JPG

v-qiuyu-msft
Community Support

Hi @Chrisstott

 

I would suggest you create a support ticket to get help. 

 

Support Ticket.gif

 

Best Regards,
Qiuyun Yu 

v-qiuyu-msft
Community Support
Status changed to: Delivered