Forum Discussion
Problem with DateTime value if read from SharePoint
Hello, hopfully someone can help me to understand/resolve my problem. In this explanation I will use Excel Power Query, but the same problem I get in Power BI Desckop too. I use Excel just to make a better comparison.
Data Entry
Data Entry in my case starts in SharePoint, where users is allowed to select DateTime value. For example:
So far so good
now I want to see the same info in Excel, using this method:
so far so good too
But if I use the same PC and the same Excel instance, but export the data via Power Query, and I read the SharePoint List values starightforward without any Date manipulations, for example like this:
let
Source = SharePoint.Tables("https://home.XXXXXXXXX", [ApiVersion = 15]),
#"ee9430b7-490a-40d0-81b1-5fd6314508d1" = Source{[Id="ee9430b7-490a-40d0-81b1-5fd6314508d1"]}[Items],
#"Renamed Columns" = Table.RenameColumns(#"ee9430b7-490a-40d0-81b1-5fd6314508d1",{{"ID", "ID.1"}})
in
#"Renamed Columns"
then I get DateTime value shifted by 2 hours backwords, why ????
Thank you in advance for your help or ideas 🙂
I found a solution (workaround) by myself. Maybe it is not the best way, but seams to be working.
What I do is I create calculated column in the SahePoint, explicitly forcing datetime value to be converted into decimal datatype at the SharePoint site.
Later, in Power Query, I just convert the correctly-converted-decimal-number into DateTime datatype, and I get what I need in the correct way.
9 Replies
Hi vlialko ,
It looks like you need to align your regions across all of your systems (SP, Excel, PBI) to ensure they're all working on the same timezone.
You can also account for this in your code using DateTimeZone functions:
https://docs.microsoft.com/en-us/powerquery-m/datetimezone-functions
Pete
Hi Pete,
thank you for the quick response. I suspect too that it has to do something with Regions, but to me my reginal settings looks like are correcltly set up. For example:
SharePoint Site Settings are set to Swededn
My windows settings are set to Swededn too
SharePoint in the web-browser presents corect dates (including system columns like Created, Modfified)
My Power BI Desktop settings are also set to Swededn too
So what should I align???
And why, the same excel instance on the same PC is presenting correct date, if data is retrieved via
but shifts backwards if data is fetched via Excel-PowerQuery??? Regional setings are the same, as it is the same PC, and even the same Excel, isn't it?
Thx.
Hi vlialko ,
It should be that simple, but unfortunately never is.
Check these regional settings and see if there's anything obvious there:
Failing that, I think you'll have to manually adjust values in your code with the DateTimeZone functions, which is generally best practice anyway to account for global scalability.
Pete
