Merging Rows from Thousands of Excel Workbooks

[JamesCarnegie]

Context:

My organization is conducting between 2-8 thousand surveys (using an Excel workbook) containing ~250 questions each. The format is standardized. The workbooks will be sent back to the organization from responders and uploaded to our EDW.

My thinking is that I will be able to create a hidden page in the Excel workbook that produces a numeric output from the questions answered (lots of binary or scale questions). From here, I would like to either merge the Excel workbooks (just the hidden page) OR import the 2K+ Excel files into Power BI so that the results can be analyzed on a macro and micro level (without losing granularity).

Questions:

• Is it possible to easily merge the Excel files together (e.g. stack just the hidden pages results as rows to produce one file with 2-8K rows that would go into BI)?
• Would it be possible to import all 2-8K Excel files (from an EDW) into Power BI to be analyzed?

Please feel free to reach out for clarification.

[amitchandak · Accepted solution]

@JamesCarnegie , You can merge multiple excel, if they are in a folder. You can use a folder as a source.

Same why multiple sheets can be merged. If your excel has more than one sheet, first will show all the sheets, you can remove other steps in the power query and merge those.

But I doubt you can combine both excels and sheets

[JamesCarnegie]

Thank you very much!