Forum Discussion
Loading multiple tables from one SQL query
Then you would have to package them (for example as JSON or XML) and send them over as a string that you would then untangle in Power Query. And this is where the fun stops - Power Query cannot create multiple tables from a single query.
What's your business case?
Do these tables have the same structure? In which case you could say
let
QueryResult = Sql.Database("Server", "database", [Query="select * from MyTable1 union all select * from MyTable2;"])
in
QueryResultThe tables do not have the same structure unfortunately.
Then you would have to package them (for example as JSON or XML) and send them over as a string that you would then untangle in Power Query. And this is where the fun stops - Power Query cannot create multiple tables from a single query.
What's your business case?
The query collects data from the database, then based on the data it collected outputs a few tables. This is for temporary reporting purposes. I use power query to read the script, execute it and load it into excel.
My current approach to this is the following:
declare report int = 0 -- REPLACE THIS IN PowerQuery with set report = x -- -- CODE -- if report = 1 select * from Table1 if report = 2 select * from Table2Then I make a PowerQuery query where report = 1 and one where report = 2. This will execute the query twice, which is not ideal.
Converting the output to XML as you suggested might be a better idea
