question

RichardKulish-4616 avatar image
0 Votes"
RichardKulish-4616 asked Ehren answered

Text is being converted to time.

I created a text column that converted a datetime column according to the formula below. I placed the field in a card and it displays correctly in Power BI Desktop but when I published it, the card now shows a different time, 16:26:33 instead of the text which is 10:07:53. I even purged the original query and card and saved everything, but the new card still behaves the same. It is the converted text field that is showing because it retains the correct date format. Why is Power BI Pro converting a text column to a time column and changing the value?

Last Refresh = FORMAT( Year(LastRefresh[Date Last Refreshed]), "0000") & "-" & FORMAT(Month(LastRefresh[Date Last Refreshed]), "00") & "-" & FORMAT(Day(LastRefresh[Date Last Refreshed]), "00") & " " & FORMAT(HOUR(LastRefresh[Date Last Refreshed]), "00") & ":" & FORMAT(MINUTE(LastRefresh[Date Last Refreshed]), "00") & ":" & FORMAT(SECOND(LastRefresh[Date Last Refreshed]), "00")

power-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Ehren avatar image
0 Votes"
Ehren answered

[Date Last Refreshed] is probably UTC time when refreshed in the PBI service, vs. your local time zone. How are you calculating [Date Last Refreshed] in Power Query? It's likely you can update it to be timezone agnostic and always return the date in your desired timezone (for example, by using DateTimeZone.FixedUtcNow, DateTimeZone.SwitchZone, and DateTimeZone.RemoveZone).

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.