Forum Discussion

rlansing's avatar
rlansing
Resolver I
8 years ago
Solved

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....
Need Help
  • rlansing's avatar
    rlansing
    8 years ago

    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.

     

rlansing's avatar
rlansing
Resolver I
8 years ago

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.