Forum Discussion

PBILearner2022's avatar
PBILearner2022
Helper I
2 years ago
Solved

Date value difference between SharePoint and Powerbi

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 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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!

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      PBILearner2022
      Helper I
      2 years ago

      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 

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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!

  • adple's avatar
    adple
    New Member
    1 year ago

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

    Adple