how to combine excel workbooks into one file

Also, we can see that the Product & SaleAmount column were also successfully loaded. Lastly, notice that the SaleAmount from the first sheet, which was located in the 4th column was not loaded because the Sample File only contains three columns as a base.Īfter refreshing the data sources in the second method, we can observe that the SaleAmount from the first sheet was correctly loaded regardless of it being in the 4th column position. Also, we can see that the SaleAmount from the second sheet was loaded into the Product column and not the SaleAmount column. After refreshing the data source, we can observe that the headers for the second sheet were not parsed as headers but as rows. Now that we have performed these changes let’s have a look at the results inside each method. This option can also be edited manually inside the Transform File Function if needed later on Otherwise, it will hardcode the sheet name into the Transform File Function. Select the Parameter1 folder and not the sheet, Note: Unless you explicitly would like to use a specific sheet in all excel files, do not select the sheet by name.Select the Excel file which you would like to use as a Sample File for Power BI to automatically create a structure for.Browse for the path of the Excel files, then click OK.Find and click on the Folder connection, then click Connect.Click on the Get Data option and click More….Nonetheless, let’s have a look at how to load the Excel files using this method. That is the reason why I prefer the second method as its more straightforward to avoid the issues as mentioned earlier. Data loaded into wrong columns if the columns are not sorted in the correct order.Multiple sheets could cause issues where a sheet is not found or incorrect header issues.

Certain sheets cannot be found if a specific sheet is referenced.

However, still, everything can be achieved using this method. However, this method only really works if our Excel files have a similar structure as it adds a lot of additional unnecessary overhead when they are not. The first and most commonly used one is the Combine & Transform/Load Data Option since its nicely highlighted in yellow and provides us with an automatic transformation of our Excel files. Method 1: Combine & Transform/Load Data Option I prefer using the second method as it is easier and cleaner to implement. One way would be to load the Excel files as is using the Combine & Transform/Load Data option, and the second method is only using the Transform Data option.

In this post, we will have a look at two different ways to load the Excel files.

How to combine excel workbooks into one file