Combine Files Quickly in Power Query
- Vojtěch Šíma
- Dec 22, 2024
- 8 min read
Updated: Feb 17
tl;dr Power Query provides a native file combiner, but it can feel complex and generates numerous queries. In this article, you'll learn two methods to create a simpler, more editable, and controllable file combiner with essentially just one-ish line of code. The guide is tailored for users who prefer a "click-based" approach.
Native File Combiner
You've likely encountered a feature in Power Query that allows you to combine files using a specific sample file as a reference. This option typically appears when, for instance, you use the SharePoint Folder connector.
Here, you would simply select Combine & Transform Data and provide a sample file or the first file to define the structure each file should follow during the combination. This process generates several queries with parameters and functions, which might seem messy and confusing for first-time users. However, you don’t need to worry about them if you're performing simple transformations or plan to transform your data after the appending process.
With some additional modifications, this process can produce the desired result: multiple files with the same structure appended into a single query or table, which you can then load into your model.
Is there a better way?
Yes, at least personally, I believe there’s a more convenient way to approach this. While this solution was most likely designed for users who prefer to click their way through, I find it even more challenging to modify anything within the settlement of new queries, functions, and parameters than to simply add one manual line and handle the rest with a few clicks.
Build your own "Native File Combiner"
Recreating what Power Query does for you is fairly simple and offers a much easier way to make adjustments later. Instead of creating four different components in Power Query, this approach generates just one: a function. Let’s break it down step by step to build your own file combiner.
Load data and Identify your files
In this example, I will use the SharePoint Folder connector, as it is a common choice for this type of connection. However, you can apply the same approach with any other connector of a similar nature.
Let's navigate Power Query to our Sharepoint Folder.
Now, provide the site you want to access, either by entering it manually or using a parameter. For better flexibility and easier manipulation, I usually recommend using a parameter. You can switch between the methods using the left icon.
In the next window, you wanna avoid the juicy green window and select simple Transform Data.
This step will load the content of the Sharepoint Folder to a single Query.
Select & Prepare Example File
Next, we need to select the sample file, following the same process as with the native combiner. Here, we simulate the transformations we want to apply to each file, operating under the assumption that the files we want to combine share the same nature and structure.
Let’s pick one.
Tip: It's worth naming your same files with same pattern, so it's easier to identify them as group later.
Let's say "yearly_file_excel_2023.xlsx" is our target. Since this is an Excel, we need to do a couple of steps to access the file.
First, we click on the "Binary" text in the column "Content". Clicking on the text will select this single file and access its content.
As I said, this is an Excel, so it will apply "Excel.Workbook()" function and list the sheets available.
Here, we repeat the process from the previous step. This time, we select the sheet and, in the corresponding row, click "Table" in the "Data" column. This action extracts the actual content we want to work with.
Disclaimer: In this approach we will hardcode the sheet we used, so make sure it's present in all your files.
Once we do that we get our data.
Here, we apply all the necessary transformations to achieve the desired result. In our case, we’ll simply promote headers to ensure proper column names, and that’s all we need to do for now.
You don’t necessarily need to select data types at this stage, as you’ll have to set them again after appending the files. However, it’s still worth checking them to ensure everything looks correct (even though you’ll remove this step later).
And that’s it! We now have our sample file with the desired transformations. The next step is to ensure that these transformations are applied to all of our files.
I'll show you two approaches:
Convert Sample File to a Function #1
Here’s the hardest part—but don’t worry, you don’t need to be a Hackerman to pull it off. What we need to do is access the code used to construct the sample file and adjust the part that references the file name, making it dynamic.
If you follow me you will see something like this:
Disclaimer: I added comments and renamed steps for better readibility, you will see different steps name (and no comments), but the structure will be same.
The only thing we need to do here is to wrap the whole code into a function and change the static file name to a dynamic one using the function parameter.
As you can see, we didn’t remove anything; we simply wrapped the existing steps in a new one, creating a function with a fileName parameter that accepts text. Additionally, we replaced the static file name in the selectFile step with the parameter. This ensures that the function dynamically handles different file names based on the input it receives.
Now that we have our function, let's wrap this first method and call the function for our data.
Combine Files #1
At this point, all we need to do is retrieve the file names and, for each file name, call our function to fetch the data. This approach doesn’t require you to revisit the SharePoint Folder connector—you can simply provide a text-based list of file names, and it will work seamlessly, as the function directly accesses SharePoint.
However, to make the process more dynamic—especially since we might not know all the file names in advance—let’s retrieve the file names directly from SharePoint.
So, load Sharepoint Folder as we did here ⚓. And filter your files based on the pattern you assigned to the file names.
I chose to filter file names that contain "yearly_file_excel_".
We got our file names, let's get rid of other columns because we won't need them.
Now this is perfect. Let's invoke our function now.
In the Add Column ribbon, click Invoke Custom Function.
In the new dialogue window, fill out the fields as follows:
Column name: This can be anything you prefer.
Function query: Select the function we built in the previous steps.
Value to feed the function's parameter: Choose the column "Name".
If you did everything correctly, you'll get this new table.
The final step is to expand the "content" column by clicking the small arrows at the top right of the column header. Here, you can select the columns you wish to expand. In our case, we’ll expand everything.
Make sure to untick the option "Use original column name as prefix", as leaving it checked would result in column names like "content.Month" or "content.Revenue".
And this is the final product! At this stage, you should assign appropriate data types to the columns. Additionally, you might want to remove the file name column, leaving only the Month and Revenue columns (or whichever columns are relevant to your analysis).
Disclaimer: This method retrieves each file directly from SharePoint, searches for the specific file, performs the transformations, and then loads it. The downside is that it searches the SharePoint folder for every single file, which can significantly slow down the process as the number of files increases. If you have a large number of files, consider exploring Method #2 for a more efficient approach.
Convert Sample File to a Function #2
In this method, we repeat the same steps to get to the Advanced Editor. I will simply post it here again, so you don't have to scroll.
-- Start of copied part 1 --
Here’s the hardest part—but don’t worry, you don’t need to be a Hackerman to pull it off. What we need to do is access the code used to construct the sample file and adjust the part that references the file name, making it dynamic.
If you follow me you will see something like this:
Disclaimer: I added comments and renamed steps for better readibility, you will see different steps name (and no comments), but the structure will be same.
-- End of copied part 1 --
In method two, we take a different approach to building our function. Since we are working with the SharePoint folder, we can leverage its capability to provide both the file name and its content in the same row. This allows us to directly extract the content without looping through the entire SharePoint folder to find a specific file, making the process significantly more efficient.
As you can see, we’ve streamlined the code by eliminating the need for the file name entirely. Instead, we introduced a new parameter, "content", which accepts binary data. The binary represents the actual file content, allowing us to directly use it and proceed with the necessary transformations.
This approach is much simpler in terms of code, but it requires an additional Content column in your dataset before calling the function.
Now that we have our function, let's wrap this second method and call the function for our data.
Combine Files #2
At this point, we need to return to our SharePoint folder and load both the file names and their corresponding content. The file names are useful for tracking which file is being processed. However, if you’re confident and don’t need to identify individual files, you can proceed with just the Content column.
So, load the Sharepoint Folder as we did here ⚓. And filter your files based on the pattern you assigned to the file names.
I chose to filter file names that contain "yearly_file_excel_".
Let’s eliminate unnecessary columns and keep only the "Content" column, which contains the file data, and the "Name" column for better tracking of which file is being processed.
Now, let's call the function.
In the Add Column ribbon, click Invoke Custom Function.
In the new dialogue window, fill out the fields as follows:
Column name: This can be anything you prefer.
Function query: Select the function we built in the previous steps.
Value to feed the function's parameter: Choose the column "Content".
If you did everything correctly, you'll get this new table.
The final step is to expand the "content" column by clicking the small arrows at the top right of the column header. Here, you can select the columns you wish to expand. In our case, we’ll expand everything.
Make sure to untick the option "Use original column name as prefix", as leaving it checked would result in column names like "content.Month" or "content.Revenue".
And this is the final product! At this stage, you should assign appropriate data types to the columns. Additionally, you might want to remove the file name and Content columns, leaving only the Month and Revenue columns (or whichever columns are relevant to your analysis).
Summary
In this article, you learned how to build your own file combiner with the flexibility to modify it at any time, without creating multiple additional queries. You were also introduced to two methods for achieving this. With this knowledge, you’re not limited to just SharePoint data—you can expand the concept of a "list of things" and apply the "for each, do something" approach to any type of data.
As an exercise, try creating a few Excel files, each containing multiple sheets, and then combine the sheets within each file before combining all the files together.
Thank you for reading!