SharePoint Date Data getting changed in Power BI

[Anonymous]

Dear All,

I've been facing issue with date value from sharepoint. When Using the sharepoint list as source and loading in to power bi, Dates are getting changed while loading itself. For example, if a date is 07/01/2019 in SharePoint, its getting loaded into Power BI Edit Query as 06/30/2019. This is happening for all the dates in power bi. Please hepl me on this .Have given the issue and the known details below,

Source - SharePoint

Column Datatype in SharePoint - Date

Edit Query Power BI

The same data is getting changed in both the fields Due_date & VR Date. 

Please do let me know what might the root cause for the issue. I have tried changing the Timezone, Adding TimeZone, Adding hours but seems nothing to work. I am not able to understand why the dates is getting changed in power bi. Kindly help.

@Zubair_Muhammad , @Cmcmahan , @jdbuchanan71

[Anonymous]

Hi Everyone,

I faced the same issue, and I did not have access to change the API version as suggested by @Anonymous . I found a simple way to match the dates (Assuming you do not need exact timestamps). Apply a "End of Day" transformation in the power editor:

This deafaults it to 12AM of the original date. I hope this helps someone out there.

[scottpaterson]

Thanks for this ! Fixed this issue for me...

[vanessabonilla]

Hi,
This solution  from @Anonymous helped to show the correct date.
However, if I use the Date.IsInCurrentDay formula it is not considered the "End of Day" date, but the previous one.

[Anonymous]

Hi @DouweMeer ,

I could get the acutal value from the Edit Queries from the expanded table columns "FieldValueAsText". This hold all the actual data as text and i was able to get the date without getting changed. But the issue is when i close and load / refresh the data it is taking too much time to load/refresh the data. 

Is there any issue on using that column or what might be the issue. 

I am not able to make any changes to the sharepoint page though it might work but need a solution or cause for why this is happening. Please let me know.

[DouweMeer]

Can't tell. What you could try is create a support ticket with Power BI. Perhaps they can check on your performance issue.

[Anonymous · Accepted solution]

Hi @DouweMeer ,

this issue is due to the Version Change in the API for accessing the Data from the Sharepoint to Power BI. Have raised an ticked and got it solved. 

Based on the solution provided, i changed the API version for the sharepoint source in Advanced editor from 15 to 14. This took in the actual data from sharepoint instead of converting into Text and changing the date into localetime. 

Adding this solution for any queries related to this kind of data load issue from sharepoint.

Regards,

Praveen

[Chandra_maha]

Hi,

I am facing the same issue. Could you please let me know where to add this source information with steps. This would be really helpful.

[naresh_nsn]

Hello 
Changing the API might solve my problems of Date from Sharepoint to Power BI but after editing the API Version from 15 to 14, showing many errors and breaking my other subsequent steps
Any solution for that?? Please help

[ocin33]

Hello,

I have the same issue with dates but I am Power BI begineer and do not know where I can change the API code that is presented here.
Would you be so kind and tell me where and how I can do it?

Regards,

Mat

[DouweMeer]

Try to format them in Sharepoint as a number and transfer them to Power BI as a number as well... Only after import change the number to a date. There should be now difference in numeric value of a date. 

[Anonymous]

Hi @DouweMeer ,

Is there any other solution apart from this. As the data is been used by many other reports and i cannot change the datetype direclty in the sharepoint. It is been maintained by seperate team.

Please let me know for any another solutions too. 

[DouweMeer]

First things that pops into my mind was something we experienced recently. This is the problem of the date format in Excel. You can store dates as a number, or text. If you store things as a text and then do an import, you might get fuzzy results. 

What you could check is whether the format of your source is indeed text. If so, treat it as a text value. Then create a new column that 'calculates' the date based on the text value found in the first column.