Forum Discussion
How to read SQL requests in an Excel file?
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.
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?
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).
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?
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.