Forum Discussion
Querying information from multiple excel files into a single sheet
Hi Everyone,
I am trying to pull certain relevant data from multiple excel files that are located in multiple places into a single sheet on a new excel file. The files are not located in the same place because they are in their respective job folder. The data that I am trying to pull in should update automatically when updated in the original file.
If anyone has any advise I would appreciate it. If I was unclear or anyone has questions feel free to ask.
Thank you!
7 Replies
Detlef_Lewin curious where/how in Power Query you can specify more than the first worksheet? I have Power Query working on 40 files in a single folder, but I want to include information from multiple worksheets in EACH file.
If you use From Folder connector you specify first file (or any other one on your choice) as the sample. Based on it connector takes information from all files in the folder and combine it together. All these files shall have the same structure. More exactly, portion of information you combine. If you selected Table1 or Sheet1 for the sample file, from all other files it will be taken only Table1 or Sheet1. Within these objects small deviation in structure is possible, but it depends.
I did find the Power Query option on the internet when I was searching. I wasn't really sure how to do this when the files are all in different folders on the server. The examples I saw had all the excel files in a single folder.
It is the same when importing a single file. You only you have to do it multiple times.
