Combine Multiple Excel Files Into One Power Query

-->

Combine Multiple or All Sheets from an Excel File into a Power BI solution Using Power Query Dynamically. Combining multiple files from one folder is already supported built-in through the graphical interface of Power Query when use you Get Data from Folder. However, if you have one Excel file, with multiple sheets with the same structure but different data, you cannot use that option. Power Query Combine Files from Folder - Xelplus - Leila Gharani. Delete the “Promoted Headers” step. Remove the top 2 rows ( Home (tab) - Reduce Rows (group) - Remove Rows - Remove Top Rows) Promote the first row to a header row ( Home (tab) - Transform (group) - Use First Row as Header) Remove. After selecting Transform data in the Combine files dialog box, you'll be taken back to the Power Query Editor in the query that you initially created from the connection to the local folder. The output query now contains the source file name in the left-most column, along with the data from each of the source files in the remaining columns.

To combine, or append, your tables together, you need to create a connection to each of them in Power Query. Go to the Power Query editor by clicking on From Table/Range on the Data or Power Query tab (depending on which version of Excel you are using). This brings up a preview of your data. To create a connection.

With Power Query, you can combine multiple files that have the same schema into a single logical table.

This feature is useful when you want to combine all the files you have in the same folder. For example, if you have a folder that contains monthly files with all the purchase orders for your company, you can combine these files to consolidate the orders into a single view.

Files can come from a variety of sources, such as (but not limited to):

  • Local folders
  • SharePoint sites
  • Azure Blob storage
  • Azure Data Lake Storage (Gen1 and Gen2)

When working with these sources, you'll notice that they share the same table schema, commonly referred to as the file system view. The following screenshot shows an example of the file system view.

In the file system view, the Content column contains the binary representation of each file.

Note

You can filter the list of files in the file system view by using any of the available fields. It's good practice to filter this view to show only the files you need to combine, for example by filtering fields such as Extension or Folder Path. More information: Folder

Selecting any of the [Binary] values in the Content column automatically creates a series of navigation steps to that specific file. Power Query will try to interpret the binary by using one of the available connectors, such as Text/CSV, Excel, JSON, or XML.

Combining files takes place in the following stages:

Table preview

Power

When you connect to a data source by using any of the previously mentioned connectors, a table preview opens. If you're certain that you want to combine all the files in the folder, select Combine in the lower-right corner of the screen.

Alternatively, you can select Transform data to access the Power Query Editor and create a subset of the list of files (for example, by using filters on the folder path column to only include files from a specific subfolder). Then combine files by selecting the column that contains the binaries in the Content column and then selecting either:

  • The Combine files command in the Combine group on the Home tab.

  • The Combine files icon in the column header of the column that contains [Binary] values.

Combine files dialog box

After you select the Combine or Combine files command, the Combine files dialog box opens and the following occurs:

  1. Power Query analyzes the example file (by default, the first file in the list) and determines the correct file connector to use to open that file.
  2. The dialog box provides the file connector experience exactly as if you were to connect directly to that example file.
    • If you want to use a different file for the example file, you can choose it from the Example file drop-down menu.
    • Optional: You can select Skip files with errors to exclude from the final output any files that result in errors.

In the following image, Power Query has detected that the first file has a .csv file name extension, so it uses the Text/CSV connector to interpret the file.

Combined files output

After the Combine files process is finished, Power Query automatically performs the following actions:

  1. Creates an example query that performs all the required extraction steps for a single file. It uses the file that was selected as the example file in the Combine files dialog box.

    This example query has the name Transform Sample file in the Queries pane.

  2. Creates a function query that parameterizes the file/binary input to the example query. The example query and the function query are linked, so that changes to the example query are reflected in the function query.

    These queries are listed in the Helper queries group.

  3. 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, and then expands the resulting data extraction as top-level columns.

  4. Creates a new group with the prefix Transform file from and the initial query as the suffix, and organizes all the components used to create these combined files in that group.

You can easily combine all files within a given folder, as long as they have the same file type and structure (including the same columns). You can also apply additional transformation or extraction steps by modifying the automatically generated example query, without having to worry about modifying or creating additional function query steps.

Note

You can modify the steps inside the example query to change the function applied to each binary in your query. The example query is linked to the function, so any changes made to the example query will be reflected in the function query.

If any of the changes affect column names or column data types, be sure to check the last step of your output query. Adding a Change column type step can introduce a step-level error that prevents you from visualizing your table. More information: Dealing with errors

See also

Merge Multiple Excel Files Into One Sheet Using Power Query

WARNING!!! Mega POWER Ahead!

Note: When using the completed workbook, you will need to change the folder reference in the query to wherever you save the sample files. From the Query Editor, go to View > Advanced Editor and change the folder path.

If you’ve ever come across a situation where you’ve had multiple files of data with each file having data spread across multiple sheets then you’ll want to read on. In this post we’re going to explore how to use the From Folder Power Query to import multiple files with multiple sheets in each file and aggregate the data into one table.

In this example we have a series of sales files in a folder. Each file contains the sales for a given country and the files are named according to which country the sales data is from (i.e. Ireland.xlsx, England.xlsx, Luxembourg.xlsx, and Canada.xlsx). Each file has several sheets with different data in the same format. Each sheet contains the sales for a given sales person from the country and is named with the sales person’s name. As you could imagine, aggregating the data manually could be very time consuming as the number of files/sheets grows. This is where Power Query can shine.

Step 1: Create a From Folder query

Create a From Folder query.

  1. Go to the Power Query tab.
  2. Press the From File button.
  3. Select From Folder in the drop down menu.
  4. Select the folder path of the files you want to import.
  5. Press the OK button.

Check the preview data to ensure you it is the correct folder and files.

  1. A preview of the import data will appear. Check these are the correct files and folders.
  2. Press the Edit button.

Step 2: Remove data columns that aren’t needed

The From Folder query will include a lot of data such as file extension type, date modified, file location etc. that we do not need for our purposes. We can remove these to avoid clutter.

  1. Select the two columns we do need (Content and Name). Hold the Ctrl key and left click on the column headings to select them.
  2. Go to the Home tab.
  3. Press the Remove Columns button.
  4. Select Remove Other Columns from the drop down menu.

Step 3: Split the file name column

To get the country into our data, we will need to parse the text in our file name. Since our file naming convention is pretty simple (Country Name.xlsx) we can use the split column function using a period as the delimiter.

  1. Highlight the file name column.
  2. Go to the Transform tab.
  3. Press the Split Column button.
  4. Choose By Delimiter in the drop down menu.
  5. Choose Custom.
  6. Enter a period for the delimiter.
  7. Choose At the right most delimiter. This will only split the file name text using the right most period found (ie just before the file extention xlsx).
  8. Press the OK button.

We can remove the resulting column containing the extension part of the split file name.

  1. Select the column.
  2. Go to the Home tab.
  3. Press the Remove Columns button.
  4. Select Remove Columns from the drop down menu.

Step 4: Add column for file content

Now we will need to add a column to bring our content into the query.

  1. Go to the Add Column tab.
  2. Press the Add Custom Column button.
  3. Name the new column something like GetFileData.
  4. Type this formula into the formula area.
  5. Press the OK button.

Expand the new column to show all the items in the Content.

  1. Press the small double arrow icon in the right hand side of the column heading.
  2. Select Expand.
  3. Check Use original column name as prefix.
  4. Press the OK button.

We also need to expand resulting Data column to show all its elements.

  1. Press the small double arrow icon in the right hand side of the column heading.
  2. Select Expand.
  3. Check Use original column name as prefix.
  4. Press the OK button.
Combine multiple excel files into one power query online

We now have all the columns needed plus a few extra. Delete any columns you don’t need and rearrange the order of columns if desired by dragging and dropping columns.

Power Query will guess the data type of each column, but you may need to correct these.

Combine Multiple Excel Files Into One Using Power Query

  1. Select the column you need to change the data type in.
  2. Go to the Home tab.
  3. Press Data type and select the data type from the drop down menu.

Power Query Multiple Files

We were able to import all the data from multiple files and sheets into one table. We were also able to add in a country data column based on the file name and a sales person data column based on the sheet name. IF we add files to our folder or update data in a file, we can easily update our aggregated data by going into the Data tab and pressing the Refresh All button. Wow, Power Query can be very powerful!