Imported date from Sharepoint is converted wrongly

[Anonymous]

Hi all,

I have a dataset in Sharepoint that I have imported to PowerBI using Implemention 1.0.

The reason I'm using Implementation 1.0 instead of 2.0 is due to previous advice that Implementation 2.0 has problems with 5,000 records and above [https://community.fabric.microsoft.com/t5/Power-Query/Unable-to-delete-Sharepoint-ID-column/m-p/3405...

All my regional settings are set to UTC +8 hours.

Using 1.0, I have the following:

As can be seen, the datetime has been reduced by 8 hours in PowerBI (PBI), however, the timezone is still labelled as +08:00. It seems that PBI has imported the UTC +0 datetime as the UTC +8 datetime instead.

This is also kind of supported when I tried to convert the original datetime to local using DateTimeZone.SwitchZone as suggested by this post: https://www.thepoweruser.com/2019/10/21/handling-different-time-zones-in-power-bi-power-query/  from https://community.fabric.microsoft.com/t5/Service/SharePoint-Date-Data-getting-changed-in-Power-BI/m...

The result of the conversion is the same.

-------------------------------------------------------------------------------------------------------

I have tried a couple of solutions which work:

a) Using Implementation 2.0 when connecting to the dataset.

The concern with this solution is when there are more than 5,000 records (which I have).

b) I could simply add 8 hours to the datetime using [Date1] + #duration(0,8,0,0) (which is more straight forward).

My concern with this is whether this issue is recognised as a bug. If it is and microsoft resolves the bug later on, I would have erronously added 8 hours to the datetime. While it will probably not affect much in this particular case, it is quite possible that in other datasets in the future, it might move all my dates to one day forward instead of one day backwards now.

Strangely, I have another data set using Implemention 1.0 that imports the datetime correctly, so I'm not sue what triggers the right or wrong import.

My questions then are:

1) does microsoft recognise this as a bug which will be resolved? If so, would that mean that this is just a temporary issue and I should just wait for a fix to be rolled out?

2) is there a better way to resolve this issue other than adding time to it (since changing to local time doesn't seem to be effective)?

Any help or advice is greatly appreciated!

[PijushRoy]

Hi @Anonymous 

Did you try Locale option in Power Query

Appreciate your Kudos 👍 and mark as SOLUTION

[Anonymous]

Hi @PijushRoy ,

Yes I did try.. the result was the same as using DateTimeZone.SwitchZone.

No real difference could be detected.

Sorry I missed out on writing it down above.