Using PowerQuery to pull multiple worksheets from multiple workbooks within multiple subdirectories

Question

Essentially, I'm stuck with a hierarchy of .xls workbooks that I would like to compile using PowerQuery into a PowerPivot data model.

Master Directory - Retailer Folder #1 - Brand #1 Workbook - SKU Worksheet #1 - SKU Worksheet #2 - Brand #2 Workbook - Retailer Folder #2 - Brand #1 Workbook - SKU Worksheet #1 - SKU Worksheet #2 - Brand #2 Workbook

Is there an easy way to do this using PowerQuery?

One complication: every workbook contains 2 summary tabs at the end that would need to be excluded. Similarly, every folder contains a summary workbook that is also an aggregation that would need to be excluded.

Mike Honey · Accepted Answer · 2016-07-21 00:57:42Z

2

Personally I prefer to avoid functions, as I find them harder to debug & maintain. Instead I just use From Folder and filter to the files I want, then Add a Custom Column using the formula:

= Excel.Workbook ( [Contents] )

Then you can expand this into sheets and tables, filter that list and expand the Excel.Workbook.Data column to get to cells of data. You can keep enough of the metadata columns to identify the source of each row (which folder / file / sheet).

This keeps the your maintenance of the whole Query within the PQ UI. When issues occur (which is inevitable with Excel sources), you can step through the whole process and isolate the problem.

I only use functions if there will be multiple calls from different source queries. That doesn't sound like the case here.

answered Jul 21, 2016 at 0:57

Mike Honey

15.2k1 gold badge29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Austin Wismer Over a year ago

This is great! I just started using PowerQuery yesterday, and I was able to compile multiple directories of pretty dirty data into a single database -- my alternate solution was looping through these directories with VBA, pulling into a giant array, then writing to a CSV. This is much easier. Thanks for your reply.

mike01010 Over a year ago

wha does 'expand into sheet and table' mean. how ?

Mike Honey Over a year ago

The UX is poor - "Expand" is a button in the column header support.microsoft.com/en-au/office/…

Alejandro Lopez-Lago - MSFT · Accepted Answer · 2016-07-20 18:41:42Z

0

It should be. You can use From Folder (Folder.Files) to access the Excel workbooks and filter out the summary workbooks. Then, for the remaining workbooks, you can create a function which, when given an Excel workbook, goes to the worksheet you want and removes the last two column. You can add a custom column which then calls this function.

answered Jul 20, 2016 at 18:41

Alejandro Lopez-Lago - MSFT

4,18422 silver badges25 bronze badges

Collectives™ on Stack Overflow

Using PowerQuery to pull multiple worksheets from multiple workbooks within multiple subdirectories

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related