How to combine data from multiple worksheets of workbook into single worksheet using Power Query in Excel is explained in this blog post.
The process of combining multiple sheets of a workbook into a single sheet using Power Query is explained below. Note that Power Query is known as Get & Transform in Excel 2016.
Go to Data Tab of the Excel ribbon > Click on Get Data > From File > From Workbook
Select the workbook containing sheets to be merged and click on Import
This will activate the dialogue for Navigator and the sheets present in the selected file will be listed in the left sidebar.
Right-click on the file name and Select Transform Data. This action will activate the Power Query Editor.
In the Power Query Editor, delete all columns except the one called Data
Click on the icon at the top right corner of the column called Data > Unmark the checkbox against the label ‘Use original column name as prefix’ and Click OK
Data present all those sheets in the selected file are now displayed in the Power Query Editor.
Now we have to load this data into our Excel sheet. Before that, click on the button called ‘Use first Row as Headers’ in the home tab.
Click on Close & Load button in the Home tab
Select Close & Load and data present every sheet of the selected workbook is merged into a single sheet.
The same method can be used to combine multiple worksheets present in multiple workbooks.