Combining multiple sheets in multiple Excel files with Power Query

In a recent blog post, I described the process required to combine multiple sheets into one single table using PowerQuery. Shortly after posting this, the Power Query guru Wyn Hopkins pointed out an alternative method that makes the process more efficient which I wasn’t aware of.

Note - If you've not checked out Wyn's PowerQuery content you're missing out, check out his YouTube Channel here and his podcast 'Power Query Magic' which is available on all good podcast platforms.

When connecting to multiple sources in PowerQuery, you'll often be presented with the option to combine files. The Content column below displays the combine icon which consists of two down arrows and a single line across the bottom of the button.

Clicking the 'combine files' button presents the 'Combine Files' window which provides a number of options.

At first glance, it might seem that combining both sheets in the example below is not possible as there is no way of ticking both items. Fortunately, this isn't the case as there is an additional option that isn't immediately obvious that Wyn kindly pointed out.

The advice I received from Wyn is to right-click on the parent element in the 'Display Options' section of the window presents an additional option titled 'Transform Data' which will load all the elements displayed under the parent element. In this case, both sheets from an excel file. It's amazing to think that this functionality exists behind a menu that I'd bet the majority of PowerQuery users would never think to try! There isn't even an ellipsis to suggest there is a menu!

Once initiated the result is a list of all the elements in all the items that have been combined. Here this is 3 excel files, all containing 2 sheets.

Note - As the contents of the items to combine contain a column titled 'Name' the process has removed the file metadata which also included a column titled 'Name'. To resolve this, go back a few steps until the filename appears, rename the column titled 'Name' to something other than Name (i.e. FileName) and then delete the step that removed the columns and the filename will appear in the final table. If the 'Content' column is not required, remove it as required.

Most scenarios will only need the 'Filename' and the 'Data' columns to proceed. If this is the case, remove all the other columns until you're presented with a table similar to the one below

Click the 'Expand' icon next to the Data column header to expand the data.

The result will be a combined table containing the contents of all the sheets from all the files

Comments

How to combine multiple files with Power Query (with no VBA and just 10 mouse clicks!)

The need to combine information from multiple files is one that most users of Excel will have come across at some point in the use of Excel. I've personally spent far too many hours aggregating data from multiple files, that are identical in structure, so that I can analyse larger datasets and provide insights into products and processes. For anyone who has also done this and not yet discovered Power Query you'll probably be amazed how simple the process has become. I realise there might be some who will say "just use VBA, its easy once you learn how to code..." and they would be right. The method using Power Query provides a zero code solution that is an evolution of the Excel interface that many will already be familiar with. In this example, I've created a sample file and created a number of duplicates of the file which I've saved in a folder. The folder contains only these files and i'd recommend you do the same if you're looking to try out this pr...

Extracting data from Word (.docx) files into Power Query

Word and Excel don’t usually get along too well so it's no surprise that Power Query isn't directly compatible with its estranged cousin Word either. If you are presented with the need to import data from Word into Power Query you'll be please to hear it is possible however it requires a couple of manual steps to make it work. The manual steps could fairly easily be completed by a batch file which would automate the process further. Here is the Excel data pasted 'as values' in a Word file which i'll use for the first example Here is the Excel data pasted with 'keep source formatting' which i'll reference a couple of times in the article. Although the steps I've covered below aren't complex, this whole process has some unknowns around it so you may find the result in your instance varies from mine. The Word file I've used contains the contents of a range of excel cells that I deliberately pasted as values into Word to create a test file f...

How to automate the import of all files in a Google Drive folder to PowerBI, now updated, please read the first paragraphs!! (PowerQuery)

Update!!! This method no longer works although there is a new Google Sheets connector for PowerQuery that is currently in beta. If you're using PowerBI, you'll need to enable the preview features to enable it. https://docs.microsoft.com/en-us/power-query/connectors/googlesheets In the interest if demonstrating how it 'did' work, the original post is provided below. If you attempt to replicate this, you'll quickly realise the website doesn't behave as it used to. (here is the original article) Using PowerQuery to access multiple files within the same folder on a local or network drive is a game-changing feature that will almost certainly save many people hours of effort. This functionality is great if your data exists in a place that is easily accessible but what do you do if your data is somewhere less accessible like Sharepoint, OneDrive or even Google Docs? I have previously connected to data on SharePoint and found it fairly straight forward which raises the...

Red Gig

Search This Blog