Forum Discussion
Sharepoint List Date/Time Incorrect in PowerBi but Regional Site Settings are correct
The date time between Sharepoint and Powerbi is incorrect, is there any way to fix this without editing each column in Powerbi with a date add + 2 hours formula?
Can I change a regional setting somewhere? PowerBi Service, PowerBi Desktop, SharePoint, O365 Admin center?
There are many date / time columns that would need to be adjusted individually which I am dreading.
When I go to the SharePoint List: We have the date 01/03/2024
PowerBi pulls the date with the correct Regional Time Zone But the incorrect date:
I have checked the SharePoint Site Regional Settings:
I have checked the PowerBi About information:
PowerBI Desktop:
Thank you kindly
To fix I just went to the advanced editor and added an applied step per column that altered the time per column.
#"Inspection planned" and #"Inspection actual".
let Source = SharePoint.Tables("https://company.sharepoint.com/sites/Transport", [Implementation=null, ApiVersion=15]), #"idstuff" = Source{[Id="idstuff"]}[Items], #"Renamed Columns" = Table.RenameColumns(#"idstuff",{{"ID", "ID.1"}}), #"Removed Duplicates" = Table.Distinct(#"Renamed Columns", {"Title"}), #"Inspection planned" = Table.TransformColumns(#"Removed Duplicates",{"Inspection planned", each _ + #duration(0,2,0,0)}), #"Inspection actual" = Table.TransformColumns(#"Inspection planned",{"Inspection actual", each _ + #duration(0,2,0,0)}) in #"Inspection actual"
4 Replies
- christinepayton
Most Valuable Professional
When Power BI connects to a SP source, the datetimes will be in UTC, which can shift dates depending on which timezone you're in. Your screenshot doesn't look like how datetime fields usually come through for me, did you perhaps already convert it to datetimetimezone?
Hi christinepayton Yes, from other posts in the community I saw that you can use datetimetimezone to see if it is pulling through the correct timezone. I changed the Data Type to datetimetimezone which reflected the correct +2 hours timezone however the date was 2 hours behind the saved date in the Sharepoint Online List.
I understand Sharepoint pulls UCT time however why does it then show the datetimetimezone as +2 when it's not.Now I can't use "local" Data Type to amend the time as PowerBi already thinks it's South African timezone. Any recommendations?
Thank you
- christinepayton
I don't think it "knows" it's in UTC if that makes sense... I think it's stored in datetime, happens to be in UTC, but since there's not an explicit timezone flag on that, when you convert it to date time timezone it's not picking the right thing because it doesn't "know". Or that's my interpretation. I'm not a DBA so my understanding of what happens behind the scenes is limited. 🙂
I think what you could do is apply the UTC timezone when you convert it, with DateTime.AddZone, and then change the zone to what you want it to be?
https://learn.microsoft.com/en-us/powerquery-m/datetime-addzone
To fix I just went to the advanced editor and added an applied step per column that altered the time per column.
#"Inspection planned" and #"Inspection actual".
let Source = SharePoint.Tables("https://company.sharepoint.com/sites/Transport", [Implementation=null, ApiVersion=15]), #"idstuff" = Source{[Id="idstuff"]}[Items], #"Renamed Columns" = Table.RenameColumns(#"idstuff",{{"ID", "ID.1"}}), #"Removed Duplicates" = Table.Distinct(#"Renamed Columns", {"Title"}), #"Inspection planned" = Table.TransformColumns(#"Removed Duplicates",{"Inspection planned", each _ + #duration(0,2,0,0)}), #"Inspection actual" = Table.TransformColumns(#"Inspection planned",{"Inspection actual", each _ + #duration(0,2,0,0)}) in #"Inspection actual"
