Custom Function for aggregating multiple sources into single tables.

[Anonymous]

Hi all,

I'm wanting to know if it's possible to combine multiple sources in a function so I can call it to transform more than 1 excel file and, if possible, get some help with writing it. I'm able to write code to cleanse a single sheet with the same name across multiple workbooks but not sure about multiple workbooks and multiple sources.

Context

I receive weekly files with 7-10 worksheets in them (all the same names, same column headers for each 'grouping'). The worksheets look something similar to this and never change:

These files are stored in SharePoint folder weekly, and I connect to the files using a SharePoint connector to list all excel files (query is called ExcelSourceFiles). The content of these files are a blank 1st row, and headers in the 2nd column.

I'm using the following function to transform all sheets in the workbooks with the same name to remove the blank row and promote the table headers to column headers.

let
  Source = (Excel as binary) => let
        Source = Excel.Workbook(Excel, null, true),
    Product1_Sheet = Source{[Item="Product1",Kind="Sheet"]}[Data],
    #"Removed Top Rows" = Table.Skip(Product1_Sheet,1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true])
    in
Source

What I'm wanting to do is combine all 3 groupings (Product / Sales / Warehouse) into consolidated tables by using a function for each individual grouping. If I can get 1 function to work (e.g. product) I'm sure I can replicate them all across.

If this isn't possible and multiple sheets can't be grouped/appended then I'm guessing the only solution will be to transform all of them into individual tables, and keep them as product1/2/3 etc. ?

[lbendlin]

It's certainly possible but you need to approach this in two stages

1. Create a function that iterates through all sheets of a given Excel file and concatenates the content. Decide if you want to include the sheet name.

2. Create a function that iterates through all Excel files, calls the above function for each, and concatenates the result. Decide if you want to include the file name.