Dynamic SQL through ODBC is breaking refresh capability

[rlansing]

Hello Everyone,

I have been working on this issue for a few days and need some help. I have a data model that uses an Excel table to dynamically generate SQL queries sent through an ODBC connection. The server is actually IBM Netezza, but the beta connector does not allow custom SQL code, so I am using the ODBC driver on my computer. It works well in the desktop program.

Workflow:

- I have an Excel table with category names and the associated SQL code to load those categories

- I filter to the category of data I would like to load into Power BI in a Power Query table called Category-SQL

- Select Close and Apply and wait for the data to load

- The M Code creates multiple SQL queries and loads the data, then I save and Publish

As I said, this works well in the Desktop version and the reports publish fine to the service, but when attempting to refresh through the Personal Gateway it fails with this error:

[Unable to combine data] Section1/ProductsRaw/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Before you ask, yes I read this website multiple times: https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

I am using the Personal gateway because of some limitation with multiple data source types in the Enterprise gateway.

I separated each ODBC call into a separate table and reference the table as needed. It looks like the core issue is the code that dynamically creates the SQL query is technically combining data sources, and so the ODBC call is failing or not even made.

I am not using parameters, but after filtering the Category-SQL table, I reference the #"Category-SQL"{0}[SQLCommand] in the statement that generates the SQL statement.

If I replace the dynamic SQL with a static one, all is fine.

Any ideas if I can make this work? All help is appreciated!

Bobby

[rlansing · Accepted solution]

I hope this helps someone in the future.

I got this report to refresh by moving the'parameter' table into Power BI. The Category-SQL table was copied and pasted into Power Query's Enter Data screen and thus is no longer an external data source. I can still dynamically select categories and have the SQL generate on the fly, which is exactly what I wanted to do, but now to add or change categories, it must be done in Power Query, not the previously referenced Excel file. This is not perfect, but it is more important that I can auto-refresh these reports than have the 'parameter' table located in Excel.