Convert UNIX timestamp to DateTime using Direct Query

[Anonymous]

Hi, Gurus!
I'm connected to Apache Phoenix through CData Power BI connector using Direct Query.

And i can't solve the problem with convertation UNIX timestamp to DateTime without using importing.

I tried some tasks to resolve this:

• Custom column in Query Editor
  #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [UnixTime])​
  as a result i had notification about importing my data

• Function + Cutom Column in Query Editor
  Function UnixToDate:
  
  let q = (UnixTime) =>
  let
  Source = (#datetime(1900, 1, 1, 0, 0, 0) + #duration(0, 0, 0, UnixTime))
  in
  Source
  in
  q​
  
  Custom column:
  = UnixToDate([UnixTime])
  result was the same - notification about importing my data

•  New column using DAX in Power BI Desktop
  UTCTime = VAR UnixDays = 'Date'[UnixTime]/(60*60*24)
  RETURN (DATEVALUE("1/1/1970")+UnixDays)
  
  as a result i had an error:
  OLE DB or ODBC error: [Expression.Error] Failed to collapse expression in data source. Try a simpler expression ..
  
  in any use of datevalue and addition results in this error.

Do you know any other methods that you can use with direct query to resolve this?

Converting timestamp to unix timestamp in our datamart was a necessity because we had another mistake with Direct Query and ApachePhoenix. Where timestamp was null in datamart in power bi null becomes default date, and without importing i'm also did not find a solution how to replace default date to null. Because then you use expressions like
if [date] = #datetime(1,1,1900,0,0,0) then null else [date]

you getting an error from database that you can't compare timestamp and varchar

Hope for help in this task. And sorry for my english (:

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

There are some limitations and considerations when you use DirectQuery. No built-in date hierarchy:  When importing data, every date/datetime column will also have a built-in date hierarchy available by default. This built-in date hierarchy isn't available when using DirectQuery. If there's a Date table available in the underlying source, as is common in many data warehouses, then the DAX Time Intelligence functions can be used as normal. Date/time support only to second accuracy: When using time columns in your dataset, Power BI only issues queries to the underlying source to a level of detail of seconds. Queries aren't sent to the DirectQuery source for milliseconds. Remove this part of the times from your source columns.

For further information, please refer to the following links.

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about

https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.