Need help with Oracle SQL source query returning; The key didn't match any rows in the table

[OmegaΩ]

First off, thank you to all/any for your assistence. I am not sure how much of the source table data i can share due to privacy but i hope I can get the gist of it across.

I am trying to import data from an Oracle database I have full query rights to, or at least I have never had issues with the data before. I am connecting to the source in the format <servername>:PORT/<servicename> 

It contains 24 years worth of data containing ~91 million rows, as such I cannot/should not import it and am doing a direct query.

Also I only want to pull a years worth of data but no matter what query I write it returns:

"Expression.Error: The key did not match any rows in the table"

Filtering out some columns for privacy an example of the table would be

SAMPLE_TIME	INTERVAL	PORT	TOOLSET	PRODUCT	SESSION_START	CITY
2024-03-01 11:15:00 AM	15	27009	null	FME	2024-02-29 9:52:00 AM	Hogwarts

The query I want to use is: 

SELECT * FROM TABLE.TABLE_SESSION_DETAILS where (SAMPLE_TIME > to_date('01-MAR-24 11:00','DD-MON-YY HH:MI') AND SAMPLE_TIME < to_date('31-MAR-24 11:30','DD-MON-YY HH:MI'))

As it does transformation to make the date more readable, but even simple queries such as:

SELECT * FROM TABLE.TABLE_SESSION_DETAILS WHERE ROWNUM = 1;

All return the same error key did not match.

I can run any of these queries with the same database login on SQL Developer on my local machine no problem. 

What am I missing, the table/column/rows are not changing, I dont even have edit permission on the soure and the source has not changed in 24 years so it is not a naming mismatch. I have also changed case from lower to upper just to check and no result.

Please help, all the other results I can find about this error mainly have to do with excel or changing the source table in some fashion, that is NOT happening so whats wrong?

[OmegaΩ · Accepted solution]

So I only got the one reply, which was disappointing. Utilizing some in company resources who use PBI but not anything to do with Database we managed to bodge together a solution. 

In short DO NOT use the Data source settings SQL Statement (optional) this does not work with oracle and created broken code that failed to execute creating the error "Expression.Error: The key did not match any rows in the table" mentioned above.

Instead let it direct query or import the sample data (1000 rows max) and then use the advanced editor to manually create this query with working SQL.

Replace your import statement with the syntax below. Mine has the query from above but use whatever query you want to limit your data scope.

let
    Source = Oracle.Database("<server.domain>:1521/<servicename>", [HierarchicalNavigation=true,
        Query="
         SELECT * FROM TABLE.TABLE_SESSION_DETAILS where (SAMPLE_TIME > to_date('01-APR-23 11:00','DD-MON-YY  HH:MI') AND SAMPLE_TIME < to_date('31-MAR-24 11:30','DD-MON-YY HH:MI'))
         "]),

and then add your transformation steps after.

With this working I could see the data was correct and limited and the SQL was correct the entire time, PBI just doesn't know how to declare it in the editor properly.

I was then able to switch to an import, not a direct query, now that I knew it would only pull a years worth of data, so that I could perform data transformation.

[aduguid]

If you are using a native Oracle connector, try switching to an ODBC connection or vice versa. Sometimes changing the connection method can resolve compatibility issues

[OmegaΩ]

I setup an ODBC connection and while it works to test in ODBC it fails to load in PBI. 

Regardless this is not the route I want to go anyways as I want the dashboard to be moveable with the data source without having to setup ODBC to make it work.

I should point out that if I dont include the SQL filter then the data previews fine. its only with any filter it fails. But the filters are completely valid, what is broken in Power BI?