Getting data from an Oracle database in Power BI desktop keeps forever

Question

I am trying to get data from a single Oracle database table in Power BI Desktop. This table has around 2 million rows of data.

The following Query works just fine:

let
    Source = Oracle.Database("PPM_PROD", [HierarchicalNavigation=true]),
    PPM_DEV = Source{[Schema="PPM_DEV"]}[Data],
    Timesheets = PPM_DEV{[Name="BI_TIMESHEET"]}[Data]
in
    Timesheets

However, if I add an SQL statement as below, I run into problems:

let
    Source = Oracle.Database("PPM_PROD", [HierarchicalNavigation=true, Query="SELECT * FROM PPM_DEV.BI_TIMESHEET#(lf)WHERE (ACTUAL_TIME > 0) OR (OVERTIME > 0) "]),
    PPM_DEV = Source{[Schema="PPM_DEV"]}[Data],
    Timesheets = PPM_DEV{[Name="BI_TIMESHEET"]}[Data]
in
    Timesheets 

This query will not get any data and data load/refresh will be stuck at "Evaluating" phase forever. Any suggestions will be greatly appreciated.

Answer 1

In this case, I'd recommend not using the SQL statement but rather filtering it in the Power Query Editor. In the most common filtering scenarios, Power Query will transform the filter steps applied into a native query similar to your SQL statement.

You can still use a SQL statement if you prefer but the steps

    PPM_DEV = Source{[Schema="PPM_DEV"]}[Data],
    Timesheets = PPM_DEV{[Name="BI_TIMESHEET"]}[Data]

don't make sense if you've already selected and filtered the Timesheets table. Those steps are there to navigate to the schema and table you intend to query but you've already handled that in your SQL.