To load an Excel Table into Power Query is easy.  Just click into any cell of the Excel Table, and then click the From Table/Range button (depends on which version you are using, this button resides in different location on the ribbon).   This action will take your active Excel Table to the Power Query Editor.  To load another Excel table, we need to close the Power Query Editor, go to the Table, then repeat the step.  Easy, Piece of cake.

However, when you have 20 Excel Tables and you want to load all of them into Power Query, you need to repeat the steps 20 times.  Not really a nice experience.  😦

To my limited knowledge to Power Query, there is no simple way to load multiple Excel Tables as separate queries in one step.  Please correct me if I am wrong. ;p  I tried to Google it, but no success.

Nevertheless, there could be a “quicker” way to do so, just a bit quicker… 

First, start with a Blank Query in Power Query Editor

Input the following in the formula bar:

=Excel.CurrentWorkbook()
'Note: Pay attention to spelling and case.  Power Query is case sensitive.

Yeah!  This function gets all Tables in the current workbook to Power Query Editor.

But don’t be so happy and excited, we are not getting there yet…

If you are not new to Power Query, you should know that the above actions would actually “Combine” selected tables into ONE single query, as below:

This is not what we want…

We want them loaded as separate queries, i.e. Table 1, Table 2, Table 3, etc…

The slightly “quicker” way

Right-click any space next to “Table” within the same cell, there we can “Add as New Query”.

Yeah.  “Table3” is added as a Query “Table3”

Note: Query1 above is the query we created by using the Excel.CurrentWorkbook function.

What we need do is to repeat the above “Add as New Query” one by one.  Seriously!?  How “quicker” could it be?  We still need to do it 20 times for 20 Tables.

You are absolutely right.  We still need to do that 20 times.  However, we don’t need to close the Power Query Editor every time a Table is loaded by using the “From Table/Range” button.  Moreover, we don’t have to navigate from one Table to another 20 times.  This is how we save a bit, just a bit time.

User Voice

Do you think it’s a good idea to have one more option to “Add as new query” when the “double expand arrow” is clicked?

Please vote HERE if you like this idea.  Microsoft will listen (when there are sufficient votes).  Every vote counts.

or…

If there is a simple way to do it easily, please let us know by leaving comments.