Zulu time conversion in a calculated table

Question

I'm using PowerBI to build a calculated table to do time calculation on an existing table.

calc_issues = CALCULATETABLE(
SELECTCOLUMNS('public issues', "created_at_dt", 'public issues'[created_at], "closed_at_dt", 'public issues'[closed_at]))

Both these columns are text in my original database and are imported as text in this calculated table. When I try to switch this calculated table into a Date/Time I get the following issue:

Cannot convert value '2021-09-09T14:55:11Z' of type Text to type Date while processing column.

Now if I go into my database table and convert the text into Date/Time there's error. But I want this new calculated table to show specific Date/Time calculations.

When I try to create a new column and try to do a DATEVALUE on the text I get the same error.

Solution: Power Editor doesn't modify the existing database table when using direct.

Answer 1

The query editor can be used to convert the text into time stamps. I loaded your data sample into Power Query in a blank query and it was converted automatically, even converting Zulu to my local time.

If you don't want to convert to local time, then remove the Z from the text by extracting the first 19 characters of the text, then change the data type.