Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vlialko
Regular Visitor

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:

vlialko_0-1661420242146.png

So far so good

vlialko_1-1661420303902.png

now I want to see the same info in Excel, using this method:

vlialko_2-1661420372264.png

 so far so good too

vlialko_3-1661420529825.png

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:

 

vlialko_4-1661420851481.png

 

 

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 ????

 

vlialko_0-1661421158114.png

 

 

Thank you in advance for your help or ideas 🙂

1 ACCEPTED SOLUTION

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. 

 

vlialko_0-1661443503040.png

 

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. 

 

 

View solution in original post

9 REPLIES 9
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

vlialko_0-1661427656870.png

 

My windows settings are set to Swededn too

vlialko_1-1661427727728.png

 SharePoint in the web-browser presents corect dates (including system columns like Created, Modfified)

vlialko_2-1661427816233.png

 

My Power BI Desktop settings are also set to Swededn too

vlialko_3-1661427963933.png

 

So what should I align???

 

And why, the same excel instance on the same PC is presenting correct date, if data is retrieved via 

vlialko_4-1661428070591.png

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:

BA_Pete_0-1661431953154.png

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




I have tested with changing the languge too, but the problem is till there

 

vlialko_0-1661433290272.png

 Actually, before posting on this forum I have already tested playing with regional settins, and had no sucess 😞 Now I repeat my steps with you, just to have more pedagociall problem introduction to the uses on this forum. 

 

Ok, if we eliminate Regional settings as the root cause, then how should I think/reason, as what shuld be my next logical step ??? I mean I have a lis of M functions, 

https://docs.microsoft.com/en-us/powerquery-m/datetimezone-functions

 

My Power BI Report will be published on PBI Webservices, having users/visitors from all arround the World (from different Time zones).

 

In My DataModel I have DateTime, which I cannot explain as it is shifted by 2 hours, but's let's accept it as it is for a while. So what shoud I try to do with that DateTime value now?

 

Lets say:

dateTime1 = "2022-08-25 14:00:00" //this is for some reasons wrong

dateTime2 = f...(dateTime1)  //now we have "2022-08-25 16:00:00" and this is correct

 

what function/functions do I need to apply?

 

 

Thanks

 

 

 

 

 

 

 

 

 

 

 

Ok. In terms of the initial problem, I think that needs to be fixed, or at least fully understood, before you can move onto broader date/time/timezone requirements for the report(s). If you don't understand what is causing that issue, any further work could be meaningless. For my part, I'm still of the mind that it's an issue with regional settings, possibly in SharePoint where different objects can have different regional/timezone settings. This isn't something I can really help further with, I'm afraid, it's just a case of going through each system/object and exhausting all regional setting options.

 

Once fixed/understood, you should have the proper basis to decide whether you need to make further date/time/zone considerations for a global audience. This is hugely dependent on the type of data you're reporting and what your end-users' expectations are in terms of the dates/times that they see, so, again, is really quite specific to your scenario.

 

If you do need to go down the route of normalising your timezones then the DateTimeZone functions that you have, and this good article by Miguel Escobar, should give you the best start at thinking through and implementing what needs to be done:

https://www.thepoweruser.com/2019/10/21/handling-different-time-zones-in-power-bi-power-query/ 

 

Sorry I can't be more more helpful, but this type of decision is so very specific to your own scneario, there's no 'one-size-fits-all' type of solution.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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. 

 

vlialko_0-1661443503040.png

 

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. 

 

 

facing the same issue and did everything you have done but still my time is -4 hours. I have so many date columns that I can't use work around to add more columns. 

This is very old problem and MS is unable to provide a solution. 

 

I think the only thing that wasn't covered in the initial discussion above was browser language.

The way I see it now, many months on, is as follows:

 

Windows language/region (set correctly to your region) >> PBI Desktop language/region (set to pass-through using Windows/default/application) >> PBI Service (set to Default Browser Language) >> Browser (set correctly to your region).

 

I think the overall control that the browser has over how the final values are presented, regardless of how previous values have been set in the chain above, is often completely overlooked.

Unfortunately, this also effects every end-user of reports so, if each user's browser is not set up as you want your reports to display, then you've got a large task on your hands!

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Thanks, so I inserted and extra column in Excel and added Time(4,0,0) to correct and it worked. I hid the original column just not to confuse the users. 

My time is +4UTC so its fine now but it is realy awakward. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors