Changing date to display correctly

[subguts]

I have the Created column from a Sharepoint list displayed as follows:

How do I get this to diplay in the right time, which would be subtracting either the -4 or the -5 hours as shown.  I cannot get this column to format so that it is the local time when the record was created, even though it is right there on the screen.

This has to be easy, surely.   Thanks!

[subguts · Accepted solution]

Problem: There is a field that comes across from the data source as the UTC date value, which will have a -4 or -5 attached to it, but no way to adjust to display with the actual time it was entered (in local EST time)

Fix:

1. Change your datetime field to Date/Time/TimeZone.  In my example the field is [Created]
2. Get the hours different from UTC your date time is by adding a new column with this formula:

          = DateTimeZone.ToUtc([Created]))

          Note: I called this field “UTCDifference”

      3. Create a new column to adjust the Created field by the UTC using the following formula to give you the time            that was actually entered into SharePoint:

           = [Created]-#duration(0,[UTCDifference]*-1,0,0))

          Note: I multiply by -1 instead of using an ABS function

          Note:  I called this field “LocalDateTime”

      4. Change the type of the “LocalDateTime” column to be DateTime

          Note: This last step removes the UTC part of the date timestamp. 

Transform Results:

This is what it looks like in Transform. The highlighted steps are the ones corresponding to the ones above.  The other steps were just to rename and remove some of the columns and aren’t necessary for the solution.

Tip:  If this column shows up in the following format when you add it to your Power BI then see below as to how to fix it so it displays as a Date Time field.

You can toggle between having it display in the above hierarchical format and the datetime format by selecting one of the two options under columns.  A screen shot follows here:

End Result:

This is what it looks like in Power BI:

[lbendlin]

Right time for who?  Are your report viewers all in the same timezone?

[subguts]

Yes, they are.  I just need the time to take off the -4 or -5 hours indicated in the field and it will be correct.  If you subtract these from the time shown it is accruate as to what is in the SharePoint data source.

[lbendlin]

Use this transform:  

DateTimeZone.RemoveZone - PowerQuery M | Microsoft Learn

or this one:

DateTimeZone.SwitchZone - PowerQuery M | Microsoft Learn

[subguts]

I've tried these before posting this.  Neither of these work.  There is a lot about this on the internet and these functions will not convert to the timezone I'm in and where these transactions were timestamped.  Thanks for the suggestion.  I think this is a pretty major bug with PowerBI as it makes no sense why it would convert a date timestamp that is given to it from a SharePoint datasource and represent in a different timezone.  Ugh 🙂  I may have to adjust the timezone on the Sharepoint side to or figure out a way to subtract the hours so that it reflects correctly.  This will be a sigificant work-around that should not be necessary.