Mismatch between SP List Date/Time Column and imported BI Column

[StuartSmith]

I surely cant be the only one having this issue, so hoping there is a sutiable solution.  When importing an SP List that contains date/time columns, the corrosponding Power Bi column doesnt match. Looking at the SP Site setting, the SP Site timezone is UTC+1.  

Is there a way so that all the date/time columns match the SP Lists, instead of having to create something like a new column with +1 hour as this casues issues when time is involved.  Someything like setting the BI report to UTC +1 or something.

How are people dealing with this.

[StuartSmith]

I found if I downgrade the API from 15 to 14, the dates match in the SP List/Power Bi report. I had to reformat (Rename, Delete, Data Type, etc.) all the tables again, but the dates now match.  Obviously, unsure if this will cause any issues elsewhere.

[danextian]

Hi @StuartSmith ,

I am not aware of any option within Power BI to set the timezone. Power BI service uses GMT so I convert it to +1 using DateTimeZone functions. You can add a timezone to a column first if it is not present and then switchto another. Example:

DateTimeZone.SwitchZone ( DatetimeZone.AddZone ( [column], 0 ), 1 )

[v-jianboli-msft]

Hi @StuartSmith ,

It sounds like the issue is related to time zone differences between SharePoint and Power BI. One solution could be to change the time zone settings in Power BI to match the time zone in SharePoint. To do this, you can go to the Power BI settings and select the "Regional Settings" option. From there, you can select the appropriate time zone for your SharePoint site.

Alternatively, you could try converting the date/time values in the SP List to UTC before importing them into Power BI. This can be done using a formula in SharePoint calculated columns. Then, when importing the data into Power BI, you can specify that the date/time values are in UTC. This should ensure that the values match between SharePoint and Power BI.

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.