Combine all workbooks in folder, some workbooks have multiple tabs of which only some need to be combined

[millhouse123]

I have a scenario that has about 150 workbooks each with different names, many with only one worksheet but all have different names but many with two or more worksheets. Of those workbooks with multiple worksheets I would like to only bring in the worksheets that have a 10 digit number in the worksheet name. In the end I would like to combine all the workbooks/worksheets.

I am looking for suggestions on how to use power query to bring in all the files - my first problem is that all the worksheets are named differently so when I try to combine by folder it errors after the first record since it can't find the worksheet name used in my first example.

I kind of envision a way to bring in the list of all workbooks and worksheets and then set up filters to keep those that meet that contain a 10 digit number. the expanding to combine all the data of these files. Just not sure how to do this.

Any thoughts on this would be helpful.

 

[gazpage]

Interesting. I think you are going to need to create 3 functions.

1. Takes a sheet as an input and creates the desired table. Depending on the format of the data in your sheets this could be very simple.
2. Takes an excel workbook and only returns sheet names that match your criteria. Note that the result of this function can be fed into function 1.
3. Takes a folder and returns a list of all workbooks. No the that the result of this function can be fed into function 2.

Are you comfortable in creating functions? There are lots of blogs covering 1 and 3 on the web. I don't recall seeing one for 2, but it would go something like.

1. Use get Data on one of the excel workbooks with multiple sheets.
2. Pick any sheet and then edit.
3. Delete all the applied steps apart from the first one. You should now have a table with all the worksheets listed.
4. Add a custom column with whatever logic is needed to identify sheets that meet your criteria. Filter by this custom column and then delete it so you only have the relevant sheets you need.
5. Convert the query into a function.

 

[millhouse123]

Thank you for the guidance, I will take a stab at this and see what I can make of it. Once again your guidance is invaluable.

 

[gazpage]

Well, if you are comfortable creating and using your own custom functions I don't think this is too hard. If you have no idea what a function is I think this is not of a stretch.

 

[millhouse123]

I am obviously missing something here. I was able to complete function 1 but when I try to follow directions for Function 2 I am struggling.

Here is how I understand the directions:
1. save a single workbook with multiple tabs into folder
2. Get data from folder
3.I get a list of one with the workbook name and other attributes
4. Combine and edit
5. select a single worksheet
6. opens worksheet.

At no point in this process do I see a list a place where worksheet names are listed. I am probably missing something obvious.

thanks

 

[gazpage]

Sorry if I am unclear. Step two is to get data from a single excel sheet, not from a folder.

Choose the From File option. Select only one sheet from the list and choose edit. Should create 2-3 applied steps, the first should be a list of the objects in the excel sheet.