Forum Discussion

crejo's avatar
crejo
Regular Visitor
2 years ago
Solved

Loading multiple tables from one SQL query

Take the following PowerQuery M code:       let QueryResult = Sql.Database("Server", "database", [Query="select * from MyTable1; select * from MyTable2;"]) in QueryResult       This wil...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    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's avatar
crejo
Regular Visitor
2 years ago

The tables do not have the same structure unfortunately.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

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's avatar
    crejo
    Regular Visitor
    2 years ago

    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