If you have a bunch of Excel files that all have the same structure, you can easily pick a table name, sheet name or range name that exists in all of them and read that one item from all workbooks into a single table using Power Query.

It’s an incredibly useful tool for situations such as:

  • Regular dumps of data into files from other systems (e.g. ask your IT staff to run a script each night that dumps the day’s transactions into a file).
  • Data files that are regularly sent to you by your partners, customers, suppliers etc.
  • Excel templates that need to be completed by multiple people in different locations, then consolidated.

To get started, go to Data > Get Data > From File > From Folder and choose your folder as normal.

This works best if all the files in the folder you select have exactly the same format (i.e. the same columns).

Next, go to Edit (or “Transform Data” depending on your version).

You can then click the double arrow in the Content column and bingo!

Power Query Click the Double Arrow

Now you see a list of all your tables, sheets, and (if you keep scrolling down) all your range names too!

Choose the one you want, wait for a few seconds while Power Query does its magic, and then a table showing the values from all your files appears!

This works with all tables, sheets (provided they aren’t hidden), and range names (except dynamic range names that are generated using a formula).

The screenshots above were taken from Power Query in Excel but it works exactly the same way in Power BI Desktop.

What a magnificent tool Power Query is!