How to convert a column from number to Duration in Direct Query, without switching to import mode?

[MXXIV]

I have been trying to convert a UNIX timestamp to datetime in Power Query. I am using Direct Query, not Import.

I am very surprised that this is so difficult. I keep getting this error:

This step results in a query that is not supported in DirectQuery mode

Of course I had to type that error in here, as it cannot be copied. Now I have been investigating and have narrowed the problem down to the duration conversion. This is my code, step by step:

let
    Source = Snowflake.Databases("XXXXXX.snowflakecomputing.com","WAREHOUSE_NAME"),
    DB_NAME_Database = Source{[Name="DB_NAME",Kind="Database"]}[Data],
    PUBLIC_Schema = DB_NAME_Database{[Name="PUBLIC",Kind="Schema"]}[Data],
    TABLENAME_View = PUBLIC_Schema{[Name="TABLENAME",Kind="View"]}[Data],
    UNIX_EPOCH = #datetime(1970, 1, 1, 0, 0, 0),
    UnixEpochColumnStep = Table.AddColumn(TABLENAME_View, "unix_epoch", each UNIX_EPOCH),
    TimestampToDays = Table.AddColumn(UnixEpochColumnStep, "timestamp_days", each [timestamp]/86400.0, Double.Type),
    //ToDurationStep = Table.AddColumn(TimestampToDays, "timestamp_as_duration", each Duration.From([timestamp_days])),
    ToDurationStep = Table.AddColumn(TimestampToDays, "timestamp_as_duration", each #duration(0,0,0,[timestamp])),
    TimestampToDateStep = Table.AddColumn(ToDurationStep, "timestamp_as_date", each [unix_epoch]),
in
    DuplicateStep

Now  by separating this into steps, I can see that the error I am getting is caused by either each Duration.From([timestamp_days]) or each #duration(0,0,0,[timestamp]). Both of these cause the error.

Is there a method to converting UNIX timestamp to datetime that does not need Duration and does not trigger this error?

[AmiraBedh · Accepted solution]

You can try to convert the column from number to duration format in DirectQuery mode by using UTCDateTime and DateTimeZone  : Datetime = DateTimeZone(UTCDateTime(1970, 1, 1, 0, 0, [UnixTimestampColumn]), "UTC")