Open In App

Retrieving Data from Folders and Workbooks with Different Sheet Names

Improve
Improve
Like Article
Like
Save
Share
Report

Writing and running commands or queries on a database are often required for data retrieval. The database searches for and obtains the desired data using the query that was provided. Typically, applications and software use a variety of queries to retrieve data in various formats. Data retrieval also involves obtaining large amounts of data, typically in form of reports, in addition to smaller and simple data. In excel, when we want to merge different sheets with different names this can easily be done through a power query. However, by default, this only applies to files that have the same structure and the same sheet name. Power Query is able to load all workbooks from a folder all at once. We’ll now look at how to access data from sheets with various names. 

Steps for Retrieving Data from a Folder and Workbooks with Different Sheet Names

  1. Go to the option DATA and then click on GET DATA
  2. Go to option FROM FILE then choose FROM FOLDER.
  3. Choose the folder in which you have all sheets that you want to merge.
  4. Now you will observe all your sheet names on the window and then you have to click on the option TRANSFORM DATA.
  5. Now click on the top of the binary files column and as a result, you will a window in which select your sheet and then click on option OK.
  6. You will observe that all your sheets are merged.

Let’s understand this in detail by the given below example.

Example: We have three different sheets named Sheet-1, Sheet-2, and Sheet-3 and we have to combine all these sheets in one.

Sheet-1

 

Sheet-2

 

Sheet-3

 

The data should be combined into a single table. The Power Query can be used to achieve this. Although Power Qucany imports all workbooks from a folder at once, by default this function is only available for files with the same structure and sheet name. We’ll now look at how to access data from sheets with various names. All of our files are in one folder.

Step 1: For this first open excel and then go to the option DATA and then click on GET DATA

clicking-get-data

 

Step 2: Then you will observe here multiple options in which you have to choose the option of FROM FILE.

choosing-from-file-option

 

Step 3: After choosing this option you will again have multiple options then you have to choose the option FROM FOLDER.

choosing-from-folder-option

 

Step 4: Now you have to choose the folder in which your all sheets are present that you want to combine. After finding the folder sheets (That have sheet-1, sheet-2, and sheet-3), select that folder.

selecting-required-folder

 

Step 5: Now after selecting the sheets folder you have to click on option OPEN.

clicking-open

 

Step 6: After that, you will observe that a new window will open up in which you observe that all sheets are mentioned that we need to combine.

required-sheets-combined

 

Step 7: Now you have to click on TRANSFORM DATA.

clicking-transform-data

 

Step 8: Now again a new window will open up and here you have to choose a right-side option on CONTENT. Here you will see the combine file option.

choosing-content

 

Step 9: You will see the result that is shown below in the image.

combine-files

 

Step 10: Now you have to select the sheet 1 option.

selecting-sheet1

 

Step 11: Now click the on OK option.

clicking-ok

 

Step 12: Here you will get that all your sheets are combined.

sheets-combined

 



Last Updated : 23 Jan, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads