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 >...
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Just came across this thread when having the same issues. I seem to have sort of worked it out, I have a Date/Time column in SP that comes in as text to Desktop and it is jumping ahead 6 hours. Note this is only for local time I believe.

 

  1. Open Power Query
  2. Change Data Type to Date/Time/Timezone
  3. Change Date & Time Column, Time to Local Time
  4. Change Data Type to Date/Time to remove the Timezone

 

No idea why this is doing it, but it gets me the correct time now. Now, if only there were more visuals that worked with time...

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi

    I just followed your steps unrly and it does seem to work, thanks.

  • Ricsta's avatar
    Ricsta
    Frequent Visitor
    7 years ago

    I have the same issue, and have had it for months now. I too thought I had found the solution in the 4 steps pointed out by unrly above, but as stated this is only for local time. As soon as I publish, the problem reappears. Eventually I had to go for the solution of adding a couple of hours. However, now I am in a position where I am querying a list that is integrated with a third application (MS Project) which by default sets the time in the DateTime to actual times and not to 00:00 like SharePoint-lists do when opting to not include time in DateTime-columns of lists. 

     

    The result of this: 

    When updating the date in MSP, the date is set correctly (think it still deducts an hour, but with all Time-variables being set between 07:00-17:00 by default depending on whether it is Start- or Finish-date this is not a big problem). However, if I update the same row in the list-version the Time-variables uses SharePoint-defaults (00:00) instead of MSP-defaults. In other words, if I update in MSP I get one result, but if I update in the list-function I get another. This is problematic as it is intended for different users, some preferring to work in MSP and syncronizing to the SharePoint-list and others preferring to work directly in the SharePoint-list itself. 

     

    I realize that I can use the same solution as long as the Time-variables are not altered (as 07:00 becomes 05:00 and is still within the same Date), but it is surely not a flawless option. Another application might have another default Time-variable, some users might have the need to set the Time-variable to 23:00 or 01:00, etc. 

     

    A proper solution to this problem would surely be welcomed.