Power BI Intermediate Level: 53 - Importing and Appending Multiple Excel Workbook Sheets

Table of Contents | Power BI Report File | Sample Input

Short summary: Power BI you can get a table view of all sheets in an Excel workbook. You can then filter for your sheets of interested and then process and append them. You could even combine this with importing a folder of files, e.g. import all sheets from all Excel files in a folder.

We have already imported a lot of Excel workbook sheets as tables in this article series, and also learned to individually append or merge the resulting tables. This works great when the sheets have different structures or you have just a few sheets. But in some cases you have many sheets with the same or similar structure which you want to append. In this article I will show you how to do just that.

Input Data

Our sample input is a workbook with multiple sheets of store transaction data, all with the same structure (column names and data types) as shown below. There is also a sheet Introduction which we don't want to import.

Approach

We will tackle the challenge in a similar way as we did in the previous article for loading a folder of files. If you haven't done so already, please read that article because I will keep the explanations shorter here.

The process which we will use goes as follows:

1. Create a query for one sheet, which is representative of all sheets which you want to process
2. Change the query into a function
3. Create a new query with the Excel connector as usual, then delete all steps except for the Source step.
4. Filter the sheets and apply the function from step 2 to each sheet
5. Append the processed sheet data

Let's quickly go through them.

1. Create a Query for One Sheet

Simply create a query to our Sample Excel file and select the sheet Store 1 when asked. Let's give the query the following name: 52_FN_Process_Store_Sheet

Power Query automatically applied all required steps, including promoting headers. In more complex examples, you could add more processing here but in our case it's enough.

2. Change the query into a function

Go to the advanced editor. The M code looks like this:

Let's text-replace the step name #"Store 1_Sheet" with input_table. This doesn't change any functionality but makes it clear for you that this is the step which we will replace with an input parameter.

Similarly to the previous article, we will now change the query into a function. In this case, the input will be the table containing the sheet data. Let's make the following changes:

• Add the following above the "let" statement: (input_table) =>This means that our query become a function with an input parameter input_table
• Comment out the Source step and the input_table step using //

The query has now turned into a function and the resulting M code looks like this:

If you want to do debugging, you can temporarily undo the changes, perform your fixes, and then turn the query into a function again. It's a bit of a hassle but in the long-term it's the best and most clean solution in my opinion.

3. Create a New Query to the Excel Workbook

Create a new query with the Excel connector as usual, go into any sheet, and then delete all steps except for the Source step. This shows us a table of all items in the Excel file with the names and kinds. In our example, you see four Excel sheets, an Excel table, and an Excel named range.

4. Filter to the Sheets and Apply the Function

We are only interested in the Store sheets. Let filter items of kind Sheet and names beginning with "Store". Within a cell in the Data column, if you click in the whitespace, you see a preview of the table data. You could try to append the tables using the arrow buttons within the Data column header, and Power Query would try to create functions and queries for you, however that often ends in issues and/or a big mess. Instead, let's add a column by invoking the function which we created in step 2, using the Data column as input.

5. Append the Processed Data Sheet Data

In the newly created column, click the arrow icons to expand all columns.

Then from delete the columns such as Data, which you no longer need. Feel free to keep the column Name, as it shows you which sheet each record was imported from. Also, you need to set the column data types again.

Conclusion

You now know how to process and append an arbitrary number of Excel workbook sheets. In conjunction with the learnings from the previous article, you could even import all sheets from all Excel workbooks in a folder. The requirement of course is that the structure of the sheets is the same or almost the same. If sheets have different structures, you can either try to make them into the same structure using the custom function, or just import and process them one by one.

Next up, we will leave Power Query again and I will teach you about the X functions in DAX, such as SUMX, and how they differ from their regular counterparts.

Please like, share, and subscribe and feel free to ask questions in the comments below.

Next article: Understanding X-Functions like SUMX, AVERAGEX, etc.