So,Р’В go to the Home Tab and click on the РІР‚СљClose & LoadРІР‚Сњ. 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. To ensure a formatted and symmetrical look in our worksheets, we often need to merge a few cells together.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РІР‚Сњ.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. 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.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.For this, go to Data Tab РІС›Сљ Get & Transform Data РІС›Сљ Get Data РІС›Сљ From File РІС›Сљ From Folder.

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).

Office 365 excel merge workbooks