Forum Discussion
SharePoint Date Data getting changed in Power BI
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
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
13 Replies
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.
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.
DouweMeerImpactful Individual
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.
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.
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.
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
