Forum Discussion
SharePoint UTC Date conversions - am I missing something here?
Hi,
Recently, I have been battling with UTC dates, sourced from SharePoint, reverting back to showing as UTC when displayed on powerbi.com.
My current working solution is to create a new column using DAX column, that checks the date against predefined list of day light savings change dates and times, to ensure that the correct conversion is applied. This predefined list usually spans a 5 year period.
I have tried using Power Query with a similar list, using a custom function, but I am seeing a weird bug, which reverts the time back to UTC. I am not sure whether this is because I am converting the date to date/time/zone in the first instance, but regardless, it seems like a work around to create a 5 year list.
My question is, am I missing something? Apart from calling a date/time API (where I would need to apply some form of function call anyway, specifically when using historical dates), I cannot see any other way I can dynamically convert UTC stored dates to my local date/time.
Can anyone advise if I am doing this wrong?
Thanks
2 Replies
_sfrostSolution Specialist
Anonymous
Check if the following community post helps you!
Solved: SharePoint Date Data getting changed in Power BI - Microsoft Power BI Community
If not, you can use DateTimeZone.SwitchZone() to convert the timezone. So that way you will not have to create a list and the function will automatically convert it to the expected timezone.
Did I answer your questions? Give a thumbs up and accept this post as solution!
Thanks for your reply.
I have attempted to implement the API V14 call and it appears to be what I am after, but I was still getting some anomalies in the data being displayed. I am not sure, though, whether this is due to a Sharepoint config issue. I also haven't tested when published to the service
I am going to try another list to see how that performs, but I think this is on the right track