- #How to consolidate data in excel from multiple worksheets how to
- #How to consolidate data in excel from multiple worksheets update
- #How to consolidate data in excel from multiple worksheets download
Let’s review the completed query…Įdit the Consolidated query, and step into the Source line step. 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. Something Oddīefore you do anything else, Save the File. Rename the query to “Consolidated”, and load it to a worksheet. As we set the data types correctly, let’s also make sure that we remove any errors that might come up from invalid data types. We’re almost done, but let’s just do a bit of final cleanup here.
Obviously the Name and Item columns are redundant, so let’s do a bit of cleanup here. The next step is to prep the fields we want to preserve as we combine the worksheets. Once you click Done, you should see the following:Ĭool! We’ve got a list of all the worksheets in the file! Consolidating the Worksheets
#How to consolidate data in excel from multiple worksheets update
Of course, you’ll want to update the path to the full file path for where the file is saved on your system. Source = Excel.Workbook(File.Contents(FullFilePath)) What we need to do is replace the second line (Source = “”) with the following two lines of code:įullFilePath = "D:\Temp\Combine Worksheets.xlsx", You’ll now see the following blank query:
#How to consolidate data in excel from multiple worksheets how to
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? That would take way too much work!īut so far we’ve only seen how to pull Tables, Named Ranges or files into Power Query.
#How to consolidate data in excel from multiple worksheets download
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. But unfortunately, they haven’t formatted any of the data using Excel tables. It is all structured as per the image below, but resides across multiple worksheets one for each month:Īs you can see, they’ve carefully named each sheet with the month and year. Let’s consider a case where the user has been creating a transactional history in an Excel file. This week we’re going to stay within the same workbook, and combine multiple worksheets using Power Query. In last week’s post we looked at how to combine multiple files together using Power Query.