Sharepoint List adjusted to Date/Time/Timezone & Using Locale showing incorrect time

Here's a Power Query solution for this.  I think it's quite a bit easier than DAX, but then I have very little DAX knowledge.  Posting here as hoping it will help someone else out as I believe this is a solution to get the correct dates.  At least it worked for my problem and took a lot of time to figure out, so hoping this helps someone else save a LOT of time.  Here goes:

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: