Combine Files in Power Query
Applies to: Power Query Desktop, Power Query Online
One powerful capability to import data using Power Query is to combine multiple files from a folder, which have the same schema, into a single logical table. To start the process of combining files from the same folder, select one of the folder-based connectors (e.g.: Folder or SharePoint Folder) within Power Query’s Get Data dialog.
Combine files step-by-step experience
Upon specifying the local folder or SharePoint site, users will be taken into the Navigator dialog (in Power Query Desktop) or the “Choose data” screen (in Power Query Online), where they can opt into the “Combine” option.
Alternatively, users can click "Transform Data" to access the Power Query Editor where the list of files can be subset (e.g. using filters within the Folder Path or any of the other columns in this table) and then Combine Files by selecting the column containing the binaries (e.g. Content) and clicking the “Combine Files” button in the Power Query Desktop ribbon (Home tab) or the Power Query Online toolbar (under “Combine” menu).
The combine files experience behaves as follows:
- Power Query analyzes each input file, and determines the correct file connector to use in order to open the file contents, such as text, Excel workbook or JSON file.
- Within the “Combine Files” dialog, Power Query allows users to select a specific object from the first file, for example, an Excel worksheet, to extract.
- Users can also pick a different example file instead of the first file, by using the Example File dropdown menu.
- Optionally, users can exclude files that result in errors from the final output.
Power Query then automatically performs the following actions:
- Creates an exemplar query that performs all the required extraction steps in a single file. It uses the file that was selected as the sample file in the “Combine Files” dialog.
- Creates a function query that parameterizes the file/binary input to the exemplar query. The exemplar query and the function query are linked, so that changes to the exemplar query are reflected in the function query.
- Applies the function query to the original query with input binaries (for example, the Folder query) so it applies the function query for binary inputs on each row, then expands the resulting data extraction as top-level columns.
- Note that in addition to the above queries, a new “Sample query” group is created with two helper queries that reference the sample file used.
Using “Combine Files”, users can easily combine all files within a given folder, as long as they have the same file type and structure (such as the same columns). In addition, you can easily apply additional transformation or extraction steps by modifying the automatically generated exemplar query, without having to worry about modifying or creating additional function query steps. Any changes to the exemplar query are automatically propagated to the linked function query.