Load multiple #Excel Tables as separate #Queries quickly…

Excel Tip - Load Excel Tables as mulitple queries0.PNG

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

Excel Tip - Load Excel Tables as mulitple queries.PNG

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.

Excel Tip - Load Excel Tables as mulitple queries1.PNG

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

Excel Tip - Load Excel Tables as mulitple queries2.PNG

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:

Excel Tip - Load Excel Tables as mulitple queries3.PNG

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”.

Excel Tip - Load Excel Tables as mulitple queries4.PNG

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

Excel Tip - Load Excel Tables as mulitple queries5.PNG

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?

Excel Tip - Load Excel Tables as mulitple queries6.PNG

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.

Advertisements
This entry was posted in Power Query. Bookmark the permalink.

2 Responses to Load multiple #Excel Tables as separate #Queries quickly…

  1. Sabine says:

    Hello MF,
    since I haven’t got Power Query, I can only guess. What about File -> From Folder?

    Sabine

    Like

    • MF says:

      Hi Sabine
      You are right , we can get multiple tables into multiple queries by using From File ->Excel (pointing to the current workbook). But there is one issue… if you update any thing in a Table, Power Query won’t update it UNLESS you have saved your workbook, which is a hurdle to many users including myself… 😅

      Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s