Append all sheets within file, then all files within folder PowerQuery

[Craigc3814]

I have a spreadsheet that my company gets monthly for usage. The spreadsheet has 4 worksheets in it. East, West, Central, RTC. I am using Ken Puls FnGetContents function to pull in each one separately (so I created the function 4 times). Then I append them all after the fact. Is there a way to clean the spreadsheet up and have the FnGetContents only run 1 time, append each worksheet in a workbook then append all workbooks in the folder?
Here is one of my FnGetContents functions

(filepath)=>
let
Source = Excel.Workbook(File.Contents(filepath)),
EAST1 = Source{[Name="EAST"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(EAST1),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Usage(Gallons)", type number}, {"Available (Gallons)", type number}, {"Level (Ft)", type number}})
in
#"Changed Type"

 

[Matt Allington]

Your function takes a single input parameter (filepath). But you can pass more parameters. E.g. (Filepath, Region)

so change the line east1 so it accepts the region as a parameter. The. Get a table of all the sheets (regions) and pass them to the function.

 

[ImkeF]

Just create a table with each worksheet name in 1 row. Then add a column where you call the function, referencing the first column for the parameter.
If you expand that column, all sheets will automatically be appended. No need for a separate append-function then.

 

[Craigc3814]

Interesting, I will give that a try