Forum Discussion

HenryH's avatar
HenryH
Regular Visitor
10 years ago

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 >...
Fyouri's avatar
Fyouri
Frequent Visitor
10 years ago

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's avatar
JamesCook
Regular Visitor
10 years ago

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: