How to read SQL requests in an Excel file?

[Bertrand_fr]

Hi everybody,

Is there a way in Power BI to read SQL requests in an Excel file and then to dynamically execute them and get the results ?

[Anonymous]

Yes, with M, you can iterate over a series of queries and pass those into the Sql.Database() function in the query parameter. Although, if you do this, they'll all be in one "set", so you'll have to decide if you're going to append them or split them out into separate queries.

[Bertrand_fr]

Hi SonnChilds,

Thanks a lot for this detailed explanation, works great!

Now I'm trying to find where is the result set?

How can I use it?

[Anonymous]

Each result set resides in that "Table" value with the yellow text. Try clicking on the word "Table", and you'll add a step navigating to that result set. Alternatively, you can use the split arrows at the top of that column to expand out the tables, but only do this if all of your result sets are of the same structure (i.e. a union).

[Bertrand_fr]

Ok, I think I understand. But then, it is no possible to have different SQL in my xls source file and then to execute all these sql and get the result automatically by refreshing the report, right?

[Anonymous]

Well, technically that is possible, but keep in mind that if your SQL changes in any way, and you've used a transformation that references the structure of that query (e.g. a column name), then it'll break.

I guess I'm curious as to what you're actually trying to do, because allowing arbitrary SQL from an Excel file to form your data seems kinda brittle. I'd recommend having that SQL logic actually in Power BI itself. I'd have to know more details as to the nature of your queries and how they might change in that Excel file.

[Bertrand_fr]

I would like to use a list of sql to verify if some problem on the database are resolved, if there's no regression, some basic control of tables, etc. My list of SQL will change of course, and I would like to refresh a report and see if everything's ok.