Forum Discussion
How to combine multiple excel data using power query
In attached file a query that combines all sheets in all workbooks
If this is what you want to do, update the Folder path with yours in sheet 'PARAM' > Refresh the query in sheet 'Query Result'
refer below snapshot of error message.
test1 file have single worksheet with name as sheet however remaining files have multiple worksheets like 2 or 3 but they all have different names.
Do we need to match worksheet name as well?
As I understand your files don't have the same number of sheets, though you want to combine them all, correct?
Assuming I understood and top of my head the Get & Transform > From File > From folder wizard won't work in such scenario. A custom query is required or you first combine, file by file, all worksheets as one within each workbook where you have > 1 worksheet
If you're not too advanced with Power Query IMHO the 2nd option is the easiest one. Though, you must pay attention to rename the combined sheet (within each workbook) so they have the same name in ALL your workbooks (I guess that was your question)