Forum Discussion
Problems with date from Sharepoint list
Hi,
I have problems with dates from a Sharepoint list. It imports some the dates wrong. For example, the date in the list is 1.1.2025, but Power BI imports is as 1.1.2025. Do you have any suggestions what I can do about this?
Thank you in advance!
Hi sw123,
Thank you for reaching out to the Microsoft Fabric Forum Community.
Thank you Cookistador pankajnamekar25, for your insights.
Thank you for the clarification! The issue where "1.1.2025" in SharePoint appears as "31.12.2024" in Power BI is likely due to time zone adjustments during the data import process. SharePoint stores dates in UTC, while Power BI may convert them to your local time zone, causing a shift in the displayed date.
- To verify how Power BI is interpreting the date, open Power Query (Transform Data) and review the values in your date column at the earliest applied step, such as "Source" or "Navigation." If the date appears as "2025-01-01T00:00:00Z", it means that SharePoint is storing it in UTC time, and Power BI may be adjusting it based on your local time zone, causing the shift.
- If the date is stored in UTC, you can correct it by selecting your date column in Power Query, adding a Custom Column, and using the formula:
- DateTimeZone.ToLocal(DateTime.AddZone([YourDateColumn], 0))
- This ensures Power BI first recognizes the date as UTC before converting it to your local time zone. If the result still includes a time component (e.g., "2025-01-01 00:00"), go to Transform → Date Only to retain only the date.
- Check your date column settings in SharePoint by navigating to List Settings and selecting the column. Verify whether it is set to "Date Only" or "Date and Time." If it's set to "Date and Time," SharePoint may be applying a default time (e.g., 00:00 UTC), which Power BI then adjusts based on your local time zone, causing a date shift. If possible, change the column type to "Date Only" to prevent unintended time-based adjustments.
- If your organization has different regional settings in SharePoint, go to Site Settings > Regional Settings and check the Time Zone configuration. If it is set to UTC while your local time zone is UTC+1 (for example), this could cause a one-day shift when Power BI imports the data. Adjusting the time zone in SharePoint to match your local settings may help resolve this issue.
- Apply the necessary changes in Power Query, then refresh the dataset in Power BI. Finally, verify that the date now appears correctly as "1.1.2025" to ensure the issue is resolved.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
9 Replies
Hi sw123,
Thank you for reaching out to the Microsoft Fabric Forum Community.
Thank you Cookistador pankajnamekar25, for your insights.
Thank you for the clarification! The issue where "1.1.2025" in SharePoint appears as "31.12.2024" in Power BI is likely due to time zone adjustments during the data import process. SharePoint stores dates in UTC, while Power BI may convert them to your local time zone, causing a shift in the displayed date.
- To verify how Power BI is interpreting the date, open Power Query (Transform Data) and review the values in your date column at the earliest applied step, such as "Source" or "Navigation." If the date appears as "2025-01-01T00:00:00Z", it means that SharePoint is storing it in UTC time, and Power BI may be adjusting it based on your local time zone, causing the shift.
- If the date is stored in UTC, you can correct it by selecting your date column in Power Query, adding a Custom Column, and using the formula:
- DateTimeZone.ToLocal(DateTime.AddZone([YourDateColumn], 0))
- This ensures Power BI first recognizes the date as UTC before converting it to your local time zone. If the result still includes a time component (e.g., "2025-01-01 00:00"), go to Transform → Date Only to retain only the date.
- Check your date column settings in SharePoint by navigating to List Settings and selecting the column. Verify whether it is set to "Date Only" or "Date and Time." If it's set to "Date and Time," SharePoint may be applying a default time (e.g., 00:00 UTC), which Power BI then adjusts based on your local time zone, causing a date shift. If possible, change the column type to "Date Only" to prevent unintended time-based adjustments.
- If your organization has different regional settings in SharePoint, go to Site Settings > Regional Settings and check the Time Zone configuration. If it is set to UTC while your local time zone is UTC+1 (for example), this could cause a one-day shift when Power BI imports the data. Adjusting the time zone in SharePoint to match your local settings may help resolve this issue.
- Apply the necessary changes in Power Query, then refresh the dataset in Power BI. Finally, verify that the date now appears correctly as "1.1.2025" to ensure the issue is resolved.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
Thank you! This did the trick 😀
Hello sw123
You can try
Select the date column in Power Query.
On the Home tab, click on Data Type and set it to Date
Last option as shown in image
Thanks,
Pankaj Namekar | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
I do not see the difference, but maybe a conflict between the date format in your sharepoint and in your system settings
In power query, try to use Using local
And here, you can select another date format (for example mm/dd/yyyy or dd/mm/yyyy)
Hi,
I do not have this Using locale?
You can select it with this way, select the column with the date and click on the small icon (which represent the data type)
and then select the date format
But it looks fine with the screen shot you shared
If you want to switch the data type, you have to click on close apply, and select the column in Power BI desktop and on the top in column tools select Format
Hi sw123,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
