Duration type columns are not recognized from an ODBC connection

[oemer_erguen]

Hey there,

I was using Power BI Desktop with a local PostgreSQL connection. I had a table with some Interval columns (in SQL: "AGE(x_2, x_1) AS interval_column"). Those columns were recognized by Power BI automatically as 'duration' without adding any steps in Power Query.

Now I am switching from a local connection to an ODBC connection, and those columns are not recognized as 'duration' anymore, but therefore as 'text'. Changing the type from 'text' to 'duration' by adding a step in Power Query leads to an error:

(Literal for the duration couldn't be analyzed.)

Both tables are exactly the same, besides the different connections.

I even tried to change the SQL statements from:

AGE(x_2, x_1) AS interval_column

to

(x_2 - x_1) AS interval_column

but didn't work as well.

I hope you can help me out! A big thank you in advance!

Sincerely

Ömer

Example snippet of the table:

[v-yiruan-msft]

Hi @oemer_erguen,

Can we consider importing both the date columns x_1 and x_2 into Power BI, and then refer the solution in the following threads to get the duration.

1. Add a custom column in the Power Query Editor

Duration between Date Time

2. Using DAX

Calculate Duration in Days Hours Minutes and Seconds Dynamically in Power BI using DAX

Best Regards

[oemer_erguen]

Hey @v-yiruan-msft  and @lbendlin ,

I know how to solve the problem differently and currently I am using "option 3" in which I save the seconds between x_1 and x_2 and divide it by 86400 (One day has 86400 seconds) so I get a decima number, which is recognized by Power BI as decimal.

But the real issue is still the same: A direct query connection to a SQL leads to a different result table, than a connection via ODBC. Espacially the interval fields of the sql table are not recognized the same in Power BI (one is recognized as duration, the other as text).

For me it looks more like a bug or the queries differ from each other.

And tip for you @v-yiruan-msft, never use DAX if you could handle the same thing in SQL. Dax is way to slow...

[oemer_erguen]

Hey @lbendlin,
there are 3 ways in saving intervals (duration) in PostgreSQL. Either as "year month days hours:minutes:seconds:milliseconds", "days hours:minutes:seconds:milliseconds" or just in "seconds". The first 2 options are displayed as "duration" in Power BI from our local db, but as a "text" from our ODBC connection.

[lbendlin]

I think you should go with the third option. It can directly translate into the Power Query #duration(0,0,0,seconds)  construct.

[lbendlin]

just to clarify - this is how you get the data from the ODBC connection?  What does "1 mon"  even mean?!?! How many days are in that month?