Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

Power Query – Import Data From the Current Workbook

Power Query - Import Data from Current Workbook

We’ve seen how to import external data from a single file, how to import all the files in a folder and how to import data from a Table / named range within the same workbook.  But what if we want to import ALL the data within the same workbook?  Well, that is where we are headed in this post.

If there is one Excel best practice which users ignore all the time it’s this – keep data of the same type on one tab.  As you’ll see in our example files, the January, February and March are the same type of data in the same structure, so they should really be in one table on one tab.  Most beginner Excel users don’t think like this, so there is a good chance you’ll come across these types of workbooks and will need to use this technique at some point.

Download the example file

I recommend you download the example file for this post.  Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.

Download Icon
Download the file: Power Query – Example Files

The examples in this post use the Example 11 – Import from Current Workbook.xlsx file.

Import Tables from the current workbook

Open the Example 11 – Import from Current Workbook.xlsx file.  Next, we are going to create with a blank query by clicking Data -> Get Data -> From Other Sources -> Blank Query

Create blank query

The Power Query Editor will open.  There is one step in the Applied Steps window, nothing in the Preview window and most of the transformations are greyed-out. While the Applied Steps window shows Source as the step, there is actually nothing in this step at present. This truly is a blank query.

Empty Query

We are going to write some M code to give Power Query the source.

If using the Formula Bar, we could type the following (Click View -> Formula Bar if the formula bar is not visible).


Or if using the Advanced Editor (Home -> Advanced Editor) we could have the following:

Source = Excel.CurrentWorkbook()

Remember, M code is case sensitive, so you’ll need to type the text exactly as it is shown above.

The Preview Window will display the Tables in the worksheet.

List of Tables in workbook

Click the Expand icon to drill into the workbook structure.  Uncheck the Use original column name as prefix, then click OK.

Expand the tables

The Preview Window now displays the combined data.

Preview after data combined

Complete the query with the following transformations:

  • Remove the Name column
  • Change the data type for each column
  • Give the query an appropriate name (I have chosen CombinedTable).

Click Close & Load to push the data into a new worksheet.  You don’t know it yet, but you’ve got a problem, I’ll show you.

The Queries & Connections pane shows 151 rows loaded.

Make some changes to the January, February or March tables and click Data -> Refresh All.

Err… what just happened. We’ve now got 301 rows, but we’ve not added any more rows.

Combedin Tables after Refresh

If we refresh the data again, we’ll have 451 rows.

Let’s go back into Power Query and see what’s going wrong.  In the Queries & Connection pane double-click the query to open the Power Query Editor.

Click on the Source step in the Applied Steps box, then click Home -> Refresh Preview.  The preview window now shows this:

List of Tables including new Query

Hopefully you see the problem now.  The query we created loads the data into Excel as a Table, therefore it is now included as a source Table each time the query is refreshed and is combined with the other tables, before being loaded back into Excel again.  Each time we click Refresh, the Table gets longer and longer and longer.  It’s a bit like the Excel version of the movie Inception.

Let’s fix this problem right now.  Add a step after the Source step to filter out the combined query.

Filter out the combined table

Will this change filter to remove the CombinedTables or will it filter to include tblJanuary, tblFebruary and tblMarch?  This is important, as we want the query to expand to include new tables as they are added to the workbook.

Take a look at the Formula Bar; the M code will be:

= Table.SelectRows(Source, each ([Name] <> "CombinedTables"))

The good news is that this code will remove the CombinedTables table, but allow any other Tables added to the workbook to will be automatically included into the scope of the query.

Having a standard naming convention of tables and queries is useful.  For example, you may decide that all source Tables must have tbl at the start, this allows us to filter only to include tables which start with tbl.  By doing this, it no longer matters how many tables or queries we have, the looping effect can be controlled.

Tables or Ranges

Power Query will happily import Tables and Named Ranges.  If they both exist in the worksheet then both are listed.

Tables Named Ranges Print Areas

Print areas are a special type of named range, so are also listed.  Correct naming conventions and filtering is required to ensure the query uses the source we want it to.

Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

7 thoughts on “Power Query – Import Data From the Current Workbook

    • Excel Off The Grid says:

      You can have Sheets or Tables, as they are treated as distinct sources, even though they may contain the same data.

      Assuming a Table exists in cells A1 to D10, a Sheet would load the all the rows in the used range as data. A Table would treat the first row as the column headers, and then load only the data from cells A2 to D10.

      • Dan says:

        It doesn’t work for sheets, only for tables. If you need to import the sheets, then you must use a separate excel file to import the sheets as a workbook.

        • Excel Off The Grid says:

          Yes, you are correct. This is a known issue with Power Query, which Microsoft has not yet done anything to resolve.

    • Excel Off The Grid says:

      “Excel.Workbook” would be what you’re after, but doesn’t work in the same way.

      The easiest option would be to get the code by loading a workbook in the standard way (Data > Get Data > From File > From Workbook). Then after you’ve loaded the workbook, just copy at the code that’s generated automatically, and use that as a start point for what you needs.

Leave a Reply

Your email address will not be published.