Našimi kurzy prošlo více než 10 000+ účastníků
2 392 ověřených referencí účastníků našich kurzů. Přesvědčte se sami
Retrieving data from a folder – workbooks with different sheet names
Author: Miroslav Lorenc
Imagine, we have a folder with four Excel workbooks in it. Each workbook contains a list of planned ExcelTown courses for one month – December, January, February and March. The names of the sheets correspond to the individual months.
We want to merge the data into one table. This can be achieved by using the Power Query. Power Query can load all workbooks from a folder at once, however by default this only applies for files that have the same structure and the same sheet name. Now we will see how to retrieve data from sheets named differently.
We have all files in one folder. In Excel, go to the menu Data – Get data – From File – From Folder.
In the dialogue box, click on the Transform Data button. The Power Query opens.
We’ll combine the first column with binary files.
Nine courses from the sample file were loaded correctly, but then the listing ends because data from other files with different sheet names could not be loaded. This is an annoying thing that we can work around by telling that we don’t care about the sheet name – that we want to load first sheet from all the files, respectively we have only one sheet in each file.
So let’s set up so that it obtains data from any worksheet named. Go to the left pane and select Transform Sample File. Now, let’s go back in history to the step where the sheet was identified. We see that January 2021 was identified in the sample file and it was a kind = sheet. Delete this, including square brackets, and replace it with zero.
- = Source{0}[Data]
The first sheet in the workbook has the identification number 0, the second sheet in the workbook has the notification number 1, and so on. We will confirm this command, which means that we have changed the navigation step and now we will return to the display of all items.
We can see that all the data from all the files has already been loaded. Now all we have to do is load the result into Excel and we have one table with all the courses from all the sheets combined.
