Once you click Done, you should see the following: Cool!
We’ve got a list of all the worksheets in the file!
But unfortunately, they haven’t formatted any of the data using Excel tables.
Now the file lands in our hands (you can download a copy here if you’d like to follow along,) and we’d like to turn this into one consolidated table so that we can do some analysis on it.
Naturally we’re going to reach to Power Query to do this, but how do we get started?
We could just go and format the data on each worksheet as a table, but what if there were hundreds? But so far we’ve only seen how to pull Tables, Named Ranges or files into Power Query. Basically, we’re going to start with two lines of code: Of course, you’ll want to update the path to the full file path for where the file is saved on your system.
In last week’s post we looked at how to combine multiple files together using Power Query.
This week we’re going to stay within the same workbook, and combine multiple worksheets using Power Query.
Let’s consider a case where the user has been creating a transactional history in an Excel file.
It is all structured as per the image below, but resides across multiple worksheets; one for each month: As you can see, they’ve carefully named each sheet with the month and year.
At this point, the query should look like so: Next we’ll click the little double headed arrow to the top right of the data column to expand our records, and commit to expanding all the columns offered: Hmm… It looks like we’re going to need to promote the top row to headers, but that means we’re going to overwrite the Date column header in column 1. This makes sense, as we created a new table and worksheet when we retrieved this into a worksheet. Getting rid of the table is easy: Select the next couple of steps as well, and take a look at the output as you do. When you hit the “Changed Type” step, something useful happens…
Oh well, nothing to be done about it now, so: Rename the query to “Consolidated”, and load it to a worksheet. To be fair, our query has enough safe guards in it that we don’t actually have to do this, but I always like to play it safe. Edit the Consolidated query, and step into the Source line step. we generate an error: Let’s remove that error from the Name column. We’ve managed to successfully combine all the data worksheets in our file into one big table!
This method creates a bit of a loop in that I’m essentially having to reach outside Excel to open a copy of the workbook to pull the sheet listing in.