Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rlansing
Resolver I
Resolver I

Dynamic SQL through ODBC is breaking refresh capability

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

1 ACCEPTED SOLUTION
rlansing
Resolver I
Resolver I

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.

 

View solution in original post

1 REPLY 1
rlansing
Resolver I
Resolver I

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.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.