Microsoft Excel Power BI Power Query

How to combine Excel sheets with Power Query

Here is a simple and easy way how to combine tables from multiple Excel sheets with Power Query in Excel or Power BI. With this approach, merging will continue even if new sheets with new tables will be created in the future.

Here is how to combine data with Power Query from Excel sheets.

1. Get Data -> From File -> From Workbook

2. Choose Excel file

3. In the Navigator tab right-click on the folder with the file name and choose Edit.

4. Take a look at all the available content in the file.

5. Remove unnecessary columns. In my case all except actual data. In some cases, sheet names are needed (you can leave, for example, the Item column).

6. Expand Data column.

7. As you can see, everything is combined in one table. You can use the first row as headers and filter unnecessary column names from other tables. Every new sheet will be appended after a refresh. Leave a comment below if you need a source file used in the example.

Check out other Power Query posts.

2 comments on “How to combine Excel sheets with Power Query

  1. Good afternoon:
    Yes would you be so kind and send me the soure file so I can see to create the table.s
    I have having issue with it. I need to develop a KPI dashboard and the when I import the excel sheet I am loss on create the tables. Help!
    Thank you!

    • Janis Sturis

      Hello, Elba

      I’m afraid I don’t have that file anymore.
      I hope instructions are good enough to try it in your own data.

Leave a Reply

Your email address will not be published. Required fields are marked *