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 >...
Guggymom's avatar
Guggymom
New Member
10 years ago

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'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:

     

     

     

    • Fyouri's avatar
      Fyouri
      Frequent Visitor
      10 years ago

      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.

      • JamesCook's avatar
        JamesCook
        Regular Visitor
        9 years ago

        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.