Power BI – Fixing Dates from Sharepoint Lists (converting UTC Dates to Local Dates)

So the problem below was in relation to working with dates from a Sharepoint 2013 List, but the same technique should be applicable any time you want to convert UTC/GMT datetime fields to a local time.

When Sharepoint exposes datetime values from lists using its REST API it converts them to the UTC timezone. I suppose a lot of the time this makes sense, specially if you are dealing with data entry across multiple time zones. But if you are just working in a single timezone this can be frustrating to deal with. The with bringing this data into Power BI is that the column in Power BI does not get tagged as being UTC which can lead you to think that something has gone wrong. Because if your are in any timezone other than GMT you will see dates/times being offset when you bring them in to Power BI. I live in UTC+10 so this effect is quite marked often shifting dates back to the prior day.

If you look at the data from the Webservice calls to Sharepoint you can see that it’s returning the dates in UTC format, not as they were entered in the UI. The images below show you an example of this.

1. Is the data visible in the Sharepoint list

image

2. Is the data coming from the Sharepoint Webservice API ( the trailing ‘Z’ indicates that this date/time is now in UTC) and you can see that 11 hours have been subtracted from time (the date used above is during daylight savings in my local timezone which is 11 hours ahead of UTC)

image

3. Show how the data appears in Power BI, which is how it was sourced from the Sharepoint REST API.

image

To fix this so that you can view the dates in the local timezone we can go into the query in Power BI and do the following:

1. Click on the date column and then in the “Transform” tab click on the DataType setting and change this to “Date/Time/Timezone” – this will correctly tag the column as being in UTC+00:00

image

2. Then in the “Add Column” ribbon click on the “Custom Column” option and give this new column a name like “TxnDateLocal” and then enter the following formula where the text in red is the name of the column from sharepoint that we changed the type of in step 1

= DateTimeZone.ToLocal( [TxnDate] )

image

3. Repeat the above for each DateTime column and then use the “local” versions of these column in your model. It’s probably also a good idea to go back and delete the original version of these columns from the query after we have added the new local versions to prevent them being used accidentally.

Print | posted on Friday, February 1, 2019 2:22 PM

Comments on this post

No comments posted yet.

Your comment:

 (will show your gravatar)