At this point, your merged data is ready and all you need is to load it into your new workbook.
After that, double click on the header and select “Rename” to enter a name for the column i.e.
Here in the “Value to Replace” enter the text “.xlsx” and leave “Replace With” blank (here idea is to remove the file extension from the name of the workbook).
So, right-click on the column header and select “Replace Values”.
Click the up arrow on the left of Browse and switch to the Excel file example.xlsx. For the first step, you can create a new Excel file and switch to Data tab. In the Get & Transform Data group, click on the ‘Get Data’ option. For example, there’re 2 tables from example.xlsx and example2.xlsx separately need to be merged. At this point, you have merged data from all the files into your power query editor and, if you look closely you can see a new column with the name of the workbooks from which data is extracted. Here are the steps to combine multiple worksheets with Excel Tables using Power Query: Go to the Data tab. Copying and pasting is an essential Excel.
From here, the next thing is to select the table in which you have data in all the workbooks and yes, you’ll get a preview of this at the side of the window. The simplest way to combine multiple worksheets together is to copy and paste the data into a single worksheet.
Now, you need to combine data from these files and for this click on “Combine & Edit”.
In the end, click OK, and once you click OK, you’ll get a window listing all the file from the folder, just like below.
Here you need to locate the folder where you have files.
Each tab should also have the same column structure and layout so that Sheetgo can combine the data correctly.
For this, go to Data Tab ➜ Get & Transform Data ➜ Get Data ➜ From File ➜ From Folder. To consolidate data from multiple Excel sheets into one, the source tab must have the same name in every file.
Now, the next thing is to open a new Excel workbook and open “POWER Query”.
First of all, extract all the files from the sample folder and save that folder at the desktop (or wherever you want to save it).