Cannot import data when including sql statement in advanced options

[ECONcat1]

Hi all,

I'm having an issue importing data from an Oracle database into my PBI Desktop. 

If I simply select a table (or tables) to load, there is no problem.  However, if I attempt to include any SQL statement under Advanced Options in the get data window, the query will not resolve.  The preview pane will pop up and show correct data under the conditions I have specified, but then proceeding to either Load or Transform Data will result in the query evaluating endlessly and not resolving (have tried letting it go for hours, no resolution).

I don't think the SQL statement itself is the problem because I am inputting very basic statements and PBI seems to alert me when I have written garbage as it will not let me to proceed to the preview step.  I have also uninstalled and reinstalled the client. 

Any suggestions or experience with this would be appreciated.  Thanks!

(Obviously this is a problem if one is trying to parse a large data set before importing into PBI.  But for me this issue is exponentially worse because of the 10,000 table limit when navigating a database.  There are tables that I am trying to access that I must name using a SQL statement because they exist beyond this 10,000 limit and I cannot simply point to them with the navigator.)

[ebeery · Accepted solution]

@ECONcat1 have you tested executing the query in the DBMS first to verify its validity and performance?

Also, if the problem is simply that you aren't able to select the table from the navigator, simply build the query in Power Query instead.  Connect to any table, then in the advanced editor just edit the "Item" (table name) that is being selected.

[ebeery · Accepted solution]

@ECONcat1 have you tested executing the query in the DBMS first to verify its validity and performance?

Also, if the problem is simply that you aren't able to select the table from the navigator, simply build the query in Power Query instead.  Connect to any table, then in the advanced editor just edit the "Item" (table name) that is being selected.

[ECONcat1 · Accepted solution]

That's a good idea @ebeery .

I've been poking around this morning and I think I can get what I need with this workaround.  I didn't think of going this route so I appreciate it. 

[v-jayw-msft]

Hi @ECONcat1 ,

Is there any error message and does the sql involve multiple tables and a large amount of data?

Best Regards,

Jay

[ECONcat1]

No error messages.  And I have tested this on a relatively small table.  The most basic statment of:

select *

from "TableName1"

will cause an endless loop.