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.
Leave a Reply