Date value difference between SharePoint and Powerbi

[PBILearner2022]

Hi all,

I've pulling data from a Sharepoint list, but the date values in PowerBi are off by 1 day. For example, I have a datecompleted of 01/02/2024 in my Sharepoint list, but in PowerBi it shows as 31/01/2024. 

I tried to change API version for the sharepoint source in Advanced editor from 15 to 14. I am getting error .

I am struck with this issue can anyone help me on fixing it 

[adple]

You can use Table.TransformColumns for each columns you needed.

Adple

[PBILearner2022]

Thank you @v-jingzhan-msft  I will try it out today and let you know 

[v-jingzhan-msft]

Hi @PBILearner2022 

This is caused by the timezone difference as Power BI uses the UTC datetimes when it gets data from SharePoint while the SharePoint displays the datetime in your locale. To resolve this, you can use Power Query Editor to create a custom column to convert the UTC datetimes into your locale's datetimes. Then use the new column for further reporting. 

Here are some similar threads for your reference:

Date value difference between SharePoint data sour... - Microsoft Fabric Community

timezone - SharePoint Date wrong in Power Bi - SharePoint Stack Exchange

Here is a detailed blog introducing several workarounds for this:

How-To: Convert UTC to Your Local Time Zone in Power Query

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[PBILearner2022]

I tried to create a duplicate column and changed it to local time  and it didnt worked 

Eg: It should show : 02/01/2024 instead it shows 01/31/2024

Could you please help 

[v-jingzhan-msft · Accepted solution]

Hi @PBILearner2022 

Assume that you are using UTC+8 locale datetimes, add a custom column with below code (change 8 according to your locale). 

DateTimeZone.SwitchZone(DateTime.AddZone([Date], 0), 8)

Then change this column to DateTime or Date data type per your need. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!