Forum Discussion
Querying information from multiple excel files into a single sheet
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.
- 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?
- SergeiBaklanOct 01, 2019Diamond Contributor
If Query1 collects data from Sheet1 of all files, and Query2 - from Sheet2, load these queries as connections only. After that in Power Query editor append one query to another and load result into Excel sheet.
Assuming all sheets have the same structure.