Excel Power Query - What's the best way to combine Excel Files with multiple Worksheets where WorkSheet may not exist in all Workbooks

0
votes

I am using Excel Power Query to consolidate a number (50+) of Excel files in a folder using Get Data->From File->From Folder. Each of the files will have one or more Worksheets (unknown how many I will end up with yet). For example.

File 1: Sheet1, Sheet2, Sheet3

File 2: Sheet1, Sheet2

File 3: Sheet1, Sheet3

Sheet1 works fine as it appears in all 3 files, however as Sheet2 and Sheet3 don't appear in all the files I get an expression.error "the key didn't match".

On the 'Combine Files' screen you can select which Worksheet you want to combine and there is also an option to 'Skip files with errors'. By selecting this option, I no longer get the error, but I'm wondering if this is a safe approach as I guess I'm removing all errors regardless of whether the error relates to the missing Worksheet?

From what I can tell, this option created the 'Removed Errors1' line and then uses this when expanding table columns

#"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Other Columns1", {"Transform File from S:\ (3)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Errors1", "Transform File from S:\ (3)", Table.ColumnNames(#"Transform File from S:\ (3)"(#"Sample File (3)"))),

The idea behind using Power Query and not VBA is that individual users will be updating their spreadsheet and the Power Query will be a consolidated view to keep track of various stats based on the updated data. From initial scoping out the project I'm working on, this works well as the user just needs to save their spreadsheet and then the Power Query just needs refreshing.

Additional info added after Answer One thing I forgot to mention is the data in each worksheet is very different (eg Sheet1 has a number of different columns to Sheet2). Each worksheet will be it's own query.

1
excelpowerquery

1 Answers

0
votes

See if this gets you started

Data ... Get Data .. From Other Sources ... Blank Query .... Advanced Editor ...

Paste below over everything in box

let Source = Folder.Files("c:\directory\subdirectory\"),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content],true)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom", "Name"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", { "Data", "Item"}, {"Data", "Tab"}),
ColumnsToExpand = List.Distinct(List.Combine(List.Transform(Table.Column(#"Expanded Custom" , "Data"), each if _ is table then Table.ColumnNames(_) else {}))),
#"Expanded Data" = Table.ExpandTableColumn(#"Expanded Custom", "Data", ColumnsToExpand, ColumnsToExpand )
in #"Expanded Data"

Change row 1 to reflect the path where your files will be stored

Hit 'Done'

What it does is

(a) read in all excel files in specified directory (b) pull out the contents of each file (c) remove extra columns (d) pull out contents of each tab into a table (e) dynamically determine all unique column names across all the tabs (f) expand all the tabs with appropriate column names

That will leave you with a table showing original file name, original tab, and contents of those