Loading multiple tables from one SQL query

[crejo]

Take the following PowerQuery M code:

let
  QueryResult = Sql.Database("Server", "database", [Query="select * from MyTable1; select * from MyTable2;"])
in
  QueryResult

This will output One table (the result of select * from MyTable1). Is there a way to get the result from both tables, maybe in a list?

Thank you

[PwerQueryKees]

You input comes from 2 tables. In terms of query cost, the difference between 1 query over 2 tables or 2 queries each over 1 table will be negligible with sufficiently large tables. At the end of the day all data needs to be retrieved. 

[PwerQueryKees]

Why not have 2 sql queries? What are you trying to achieve?

[crejo]

The 2 output tables are based on the same collected data. I want to avoid having to rerun the same expensive query twice.

[lbendlin]

The SQL Server Query cache should take care of that.

[lbendlin]

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
  QueryResult

[crejo]

The tables do not have the same structure unfortunately.

[lbendlin · Accepted solution]

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?

[crejo]

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 Table2

Then 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