cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
oemer_erguen
Frequent Visitor

Duration type columns are not recognized from an ODBC connection

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.)

2021-10-21 15_16_12-KPI-Report - Power Query-Editor.png

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:

2021-10-26 17_52_45-KPI-Report - Power Query-Editor.png

5 REPLIES 5
yingyinr
Community Support
Community Support

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

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

Hey @yingyinr  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 @yingyinr, never use DAX if you could handle the same thing in SQL. Dax is way to slow...

oemer_erguen
Frequent Visitor

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.

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

lbendlin
Super User
Super User

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?

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors