HOW TO COMBINE EXCEL WORKBOOKS INTO ONE HOW TO
HOW TO COMBINE EXCEL WORKBOOKS INTO ONE DOWNLOAD

I’ve highlighted some rows that we don’t want as they don’t contain the data that we need. In our specific case, we want to only access the files that have the Kind equals to Table, so let’s do that filter: In there, what we see is a table with ALL of the objects that we can access from our Sample File. Head over to the query on the left that starts with Transform Sample File From and take a look at it: Instead of trying to select multiple tables, I want you to click on the “Sample File Parameter” on top which has a little folder icon to the left of it.

HOW TO COMBINE EXCEL WORKBOOKS INTO ONE HOW TO

Here’s the culprit of the most frequent problem users are usually having: they select just 1 table or don’t know how to select multiple tables.

The one with a blue bar on top are tables and the rest, in our care, are just sheets. Let’s go ahead and click the Combine Files button (in the Content Column) and see where it leads us:Īs you can probably tell, there’s a lot of data in that file and you can also make some distinctions between the objects found in that file by just looking at the icons. This is quite easy, just do a Does Not Begin with filter on the Name column like this:Īnd you’ll end up with just 2 files which are exactly the 2 files that we need: Step 2: Using the Combine Files Experience This means that the 2017.xlsx file is in use, so let’s go ahead and click on the Edit button so we can filter out any temporary files. If you take a closer look, you’ll notice that one of those files listed has a prefix of ~ which is a way to tell if the file is a temporary file or not. Remember, we only have 2 files in that folder, but in the preview we get 3 – why are we getting 3? Similar to what we did before, we’re going to be using the From Folder experience:Īnd once you select the folder where your files are, you might end up with something like this:

HOW TO COMBINE EXCEL WORKBOOKS INTO ONE DOWNLOAD

If you wish to follow along, you can download the files from the button below: Our goal is to connect to the files, grab all of the Tables that have the data that we want and then combine / consolidate / append everything into just one big table. We only have 2 files in our folder, but within each file we have 12 sheets (1 for each month of data) and within those sheets with a table which is where the actual monthly data is being stored. This time the situation is going to be a bit easier in terms of how many files we’re going to be dealing with. Let’s do an example on combining multiple Excel Workbooks from a folder Step 1: Connect to the Files Usually the issue that most folks have when using an Excel Workbook as their source for Combine Files from a folder is that they define their recipe to be too specific in the sense that it’ll work for some files but not for all.įor example, some people specify that they want to get the data from a Table named “Table1” from their “Sample Transform File”, but that table only exists in a their Sample File and not in the rest of the files. How does this difference between flat files and Excel files impact the Combine Files experience?Īnother great question! and the answer is simple: you need to take in consideration this file structure in order to create a “recipe” (function) that will work with all the files that you want to combine. In contrast, when you want to connect to a flat file you go straight into the data because the file structure is pretty simple. With Power BI / Power Query, you have the option to connect to 3 types of objects from an Excel workbook:

That’s a great question! the first thing that we need to understand is that a flat file has a very basic structure, whilst something like an Excel file is not as simple and you need to define what exact object you want to connect to. What’s different between combining flat files vs something like Excel files? This is the post where we’re going to see the difference between simple flat files and more complex files (like an Excel workbook) when it comes to using the Combine Files experience inside of Power BI / Power Query. In that post we were using flat files but, how would that process be for Excel files?

In the previous post we saw how we were able to combine multiple files from a Folder.

How to combine excel workbooks into one