In Case We Need To Include Sheet Name In The Final Output, Make The Following Adjustment In Step 3.

Go the ‘from other sources’ option. Note that power query shows you the worksheet name from the first file. This post will demonstrate how we can append (combine) data from multiple sheets into a single table or directly into a pivot table.

However, If You Have One Excel File, With Multiple Sheets With The Same Structure But Different Data, You Cannot Use That Option.

Combine sheets using power query. There is a very little but useful trick that read more about combine multiple or all. In the navigator pane, double click the products table.

Here Are The Steps To Combine Multiple Worksheets With Excel Tables Using Power Query:

To remove duplicate sheets and keep the connection, right click the connection on the right menu and click load to. This file would act as the key/template to combine other files. After adding all sheets you need to.

In The Import Data Dialog Box, Browse For And Locate The Products.xlsx File You Downloaded, And Then Select Open.

This will open the power query editor. After that, you can add rows to any of the source worksheets, and right click on the tab that is generated by the close and load and select refresh. Click the ‘blank query’ option.

In The ‘Combine Files’ Dialog Box That Opens, Select ‘Data’ In The Left Pane.

Click on ‘combine & load’. We will do this in a dynamic way. Repeat steps 2 to 6 for all your sheets.