Forum

Combine Worksheet f...
 
Notifications
Clear all

Combine Worksheet from All Workbooks in a Folder - different sheet names

5 Posts
2 Users
0 Reactions
198 Views
(@raffrobb)
Posts: 38
Trusted Member
Topic starter
 

Hi All:

I am getting an error - An error occurred in the ‘Transform File’ query. Expression.Error: The key didn't match any rows in the table.

I know the cause which is the worksheet in each workbook has a different name. 

Googled but fixes are beyond my understanding.

How do I fix this so I can combine 01Dec2020 with 01Jan2021 etc.  Sheet names are same as workbook names.

No problem if sheets are in same workbook, but if in a folder, doesn't work.

Attached are screen shots and workbooks.

Thanks

Robb

 
Posted : 19/12/2020 8:23 pm
(@mynda)
Posts: 4761
Member Admin
 

Hi Robb,

Have you tried to use this technique to get multiple files from a folder that contain multiple sheets with Power Query?

Mynda

 
Posted : 19/12/2020 11:15 pm
(@raffrobb)
Posts: 38
Trusted Member
Topic starter
 

HI Mynda:

I did.  the Excel.Workbook ([Content]) M code did not work for some reason.  I got error for all Binaries.

I take it that this should solve the issue of different sheet names in workbooks in folder?

I'll try again, and see  what happens, and send screen shot.

 

Thanks

Robb

 
Posted : 20/12/2020 8:02 pm
(@mynda)
Posts: 4761
Member Admin
 

Hi Robb,

The example file you attached above uses the built in combine files function. The technique I linked to above works slightly differently and doesn't return errors for the binaries. I suggest you try following the steps in the tutorial, as I just tested it on your files and it worked for me.

Mynda

 
Posted : 21/12/2020 12:45 am
(@raffrobb)
Posts: 38
Trusted Member
Topic starter
 
Hi Mynda:

I see now. Works great,.  The built in functions throw me off.

Robb
 
Posted : 28/12/2020 10:17 pm
Share: