Forum Discussion
Querying information from multiple excel files into a single sheet
- whiskeydudeSep 19, 2019Copper Contributor
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.
- SergeiBaklanSep 19, 2019Diamond Contributor
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.
- whiskeydudeOct 01, 2019Copper Contributor
thanks SergeiBaklan - got the 1st part working (pulling 30+ files into a single sheet in one file. NOW, it gets tricker. I have a second sheet (same structure as the first) in just some of the 30 files. I figuyred out how to get all this data pulled into the same file, but Excel puts all this info in a second sheet, not the primary. I'm guessing I'm just missing a setting somewhere. Any clues on how to "merge" two sheets in 30 files into a single sheet in one file?
- Rebecca965Jan 04, 2019Copper Contributor
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.
- Detlef_LewinJan 04, 2019Silver Contributor
It is the same when importing a single file. You only you have to do it multiple times.