How to use Power Query to have one consolidated file with multiple sheets from multiple files with multiple sheets

[questforexcel]

Hi,

Fairly new to Power Query. I am learning my way through it.

I have 10 different file each of which has 6 different tabs.

I would like my main combined file to have 6 sheets, which are a consolidation of each of the 6 tabs across the 10 different files.

How do I do that on Power Query?

Till now I have only found videos of people combining multiple sheets into the one main sheet on the consolidation file. Or having only one sheet on the invidividual workbook.

My workbooks are binary enabled.

When I tried to insert a "Excel.workbook([content]) under insert custom column header it gave me an error. Not sure why that was.

Would appreciate your guidance and help.

Thank you

 

[tablemerge]

You should have a try with TableMerge on messy,complex spreadsheets.PowerQuery can not handle complex spreadsheets.

www.tablemerge.com

Extract,Consolidate,Combine hundreds of sample or complex spreadsheets
in one minute without coding!​

Automatically Consolidate/Merge/Extract Complex headers,Inconsistent table structure,Non-fixed table position, Different header names, Multi-level header rows,Merged headers,Key-value form tables spreadsheets and workbooks by Zero-coding.

1. Merge, Consolidate thousands of Excel sheets even with non-fixed table position, different header names, multi-level headers,merged headers​

2. Combine, Extract inconsistent headers,multiple headers,one sheet with multiple workbooks into one sheet​

3. Extract 1000+ workbook of key-value cell on form table, along with multiple rows of table records at the same time, output into one data table​

4. Unpivot complex reports and multi-dimensional pivot table data, with indentation header styles, multi-level headers, and merged cells​