- Power Query allows you to consolidate multiple files from a local or cloud folder as long as they share the same data schema.
- Using a sample file makes it easier to apply bulk transformations, such as removing top rows or changing headers.
- There are limitations when columns do not match exactly, requiring manual cleanup or the use of VBA scripts for complex cases.
Imagine you have a bunch of monthly or weekly reports scattered across folders, and instead of wasting your morning frantically copying and pasting data, you could unify everything into a single Excel spreadsheet with just a couple of clicks. This is precisely where Power Query comes in, a tool that's essentially the right-hand man of anyone working with data, allowing us to consolidate files that share the same structure effortlessly.
The coolest thing about this system is that it doesn't limit you to a local folder on your PC; you can pull data from SharePoint sites, Azure Data Lake, or Azure blobs . The key to making this work smoothly is that the files have the same schema, meaning the columns match, although Power Query is smart enough to help clean up the data if it contains any noise or unnecessary rows.
The step-by-step combination process
When you connect to a data source, the first thing you'll see is the file system view. Here, the most important column is Content, which contains the binary representation of each document. If you click on one of these binary values, the program will try to determine if it's a CSV, JSON, or Excel file in order to open it. To combine the entire batch, you have two options: click the Merge button directly, or, if you're more meticulous, select Transform Data to filter the file list (for example, to ignore specific folders) before proceeding with the merge.
Once you enter the merge dialog box, Power Query uses a sample file to analyze the structure . By default, it selects the first file in the list, but you can change it if there's another one that better represents the data set. Here, you can select the option to skip files with errors , which is a lifesaver when a report is corrupted or empty, preventing the entire process from stopping.
Output management and data cleansing
After accepting the merge, the system works some technical magic behind the scenes: it creates a sample query and a parameterized function. This means that any changes you make to the sample file will be automatically replicated to all other files in the folder. If, for example, your CSV files have four rows of unnecessary headers before the actual data, you can go to the transformation query and use the option to remove top rows to clean up that clutter.
After cleaning the rows, it's essential to apply the " Use the first row as headers" function . Keep in mind that when you do this, Power Query usually adds an automatic data type conversion step. If, upon returning to the main query, you see an error stating that column 'Column1' cannot be found, don't worry; this is normal because you renamed the columns in the example , and the subsequent step no longer recognizes the old names. Simply delete that erroneous step from the Applied Steps pane, and everything will return to normal.
Common challenges and advanced alternatives
Sometimes life isn't so simple, and we encounter files that aren't identical. For example, if you have ServiceNow reports where one header is called 'resolved_on' and another 'closed_on', Power Query might give you problems because it requires a consistent structure . If the sample file doesn't exactly match the rest, the tool might omit the information from the mismatched files or throw import errors.
For cases where the structure is completely chaotic or the data volume is massive (exceeding one million Excel rows), there are other options. Some users opt for VBA scripts using CSV interfaces , which allow files to be joined without headers being repeated, regardless of whether the field or record delimiter varies from file to file. While the Windows Shell is extremely fast, it often fails when handling embedded line breaks, whereas a robust VBA solution doesn't overload RAM and can handle millions of records with ease.
To complete the process, once the data is combined in Power Query, remember to assign the correct data type to each column (Date for dates, Currency for income, etc.) before loading the final table. You can verify that everything is correct by filtering the Source Name column to ensure that all files in the folder have been successfully processed.
The ability to automate the joining of multiple CSV files allows you to move from tedious manual work to a professional workflow, whether by leveraging the flexibility of Power Query for data cleansing or resorting to VBA when structures are incompatible or volumes exceed the limits of spreadsheets.