0

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.

Austin Wismer
  • 281
  • 1
  • 4
  • 16

2 Answers2

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.

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
  • 1
    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. – Austin Wismer Jul 21 '16 at 19:15
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.

Alejandro Lopez-Lago - MSFT
  • 4,014
  • 20
  • 25