Power Query custom functions | Applying a function to every filtered sheet(with different names) in every workbook that is stored in a folder

I am processing 3 files from 3 different distributors. Each file contains at least 6 sheets: SALES, PURCHASING, STOCK, SALES(Forecast), PURCHASING(Forecast), STOCK(Forecast) and then some overview sheets.

Sales indicate the products sold by the distributor, Purchasing indicated the products bought by the distributor, Stock indicates the remaining inventory of products for the distributor.

I need to combine the SALES and PURCHASING sheets of each file and combine each file to do further analysis.

Right now, I am using 6 custom functions to manage this task - each for each sheet type (fnSALES, fnPURCHASING, fnSTOCK, fnSALES(Forecast), fnPURCHASING(Forecast), fnSTOCK(Forecast).

I am trying to apply a custom function to every filtered sheet(SALES, PURCHASING) in every workbook that is stored in a folder.

Each time I try to do it - it crashes or throws out an error of looping conditions.

this is the function for one sheet:

(**PURCHESES_GROSS_FileName**)=>
let
    Source = Excel.Workbook(**PURCHESES_GROSS_FileName**, null, true),
    #"PURCHASES, GROSS_Sheet" = Source{[Item="PURCHASES, GROSS",Kind="Sheet"]}[Data],
    #"Transposed Table" = Table.Transpose(#"PURCHASES, GROSS_Sheet"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1", "Column2"}),
    #...
    #"Inserted Multiplication2" = Table.AddColumn(#"Inserted Multiplication1", "WHSL Value 2023, Eur", each [#"WHSL Price 2023, Eur"] * [Quantity], type number)
in
    #"Inserted Multiplication2"

it works fine for all sheets I need to transform and combine(SALES, SALES(Forecast), PURCHASING, PURCHASING(Forecast).

I can apply this to all workbooks but just for the "PURCHASES, GROSS" sheets. Is there any way to make this dynamic?

I tried this :

1)

Buffer:

let
    Source = Folder.Files("D:\1_1_1_ROOTMARK\Stock Movement\data"),
    #"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".xlsx"),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Name", Order.Ascending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Content", "Name"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Removed Other Columns", {{"Name", each Text.BeforeDelimiter(_, " SKU Stock movement.xlsx"), type text}}),
    #"Extracted Text Before Delimiter1" = Table.TransformColumns(#"Extracted Text Before Delimiter", {{"Name", each Text.BeforeDelimiter(_, " ", {0, RelativePosition.FromEnd}), type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter1",{{"Name", "Distributor"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each not Text.StartsWith([Distributor], "~")),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each ([Distributor] <> "ELVIM Bal"))
in
    #"Filtered Rows2"

(**Index**) =>
let 
    Inside_Of_workbook = #"Removed Columns"{**Index**}[data],
    #"_Sheet" = Inside_Of_workbook{[Item=[Item],Kind="Sheet"]}[Data],
    #"Transposed Table" = Table.Transpose(#"_Sheet"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column3", "Column2"}),
    ...
    #"Inserted Multiplication2" = Table.AddColumn(#"Inserted Multiplication1", "WHSL Value 2023, Eur", each [#"WHSL Price 2023, Eur"] * [Quantity], type number)
in
    #"Inserted Multiplication2"

let
    // just to clean up the code
    Buffer_Hopes_For_All_Sheet_fn = Buffer,
    // Open all files and store the content in a table that is stored in a row
    WorkbookContents = Table.AddColumn(Buffer_Hopes_For_All_Sheet_fn, "Sheets", each Excel.Workbook([Content])),
    // added index to get the "Row number"
    #"Added Index" = Table.AddIndexColumn(WorkbookContents, "Index", 0, 1, Int64.Type),
    // Used the [Index] to get all the sheets from the excel files( I don't know, maybe this is the exact same step as "WorkbookContents" - looks similar)
    Sheets = Table.AddColumn(#"Added Index", "data", each #"Added Index"{[Index]}[Sheets]),
    // removed unnecessary columns
    #"Removed Columns" = Table.RemoveColumns(Sheets,{"Content", "Sheets"}),
    // Tried invoking the custom function giving the index parameter in each row the value of [Index] column
    #"Invoked Custom Function" = Table.AddColumn(#"Removed Columns", "Clean_data", each #"fn_PURCHASES, GROSS (Foreccast)"([Index])),
    // Returned an error in each row instead of a table containing 2 tables from each file.
    Clean_data = #"Invoked Custom Function"{0}[Clean_data] // returned an error
in
    Clean_data

Error: An error occurred in the ‘’ query. Expression.Error: A cyclic reference was encountered during evaluation.

2)

I also tried fust copying the function code and using a concept of :

let
    *code1*
    Table.AddColumn(previousStep, "Clean_data", each 
    let 
        (index)=>
            let
                *function code*
            in
                *function output*
    in
        *function output*)
    *code*
in
    *output*

this crashed instantly, it did not recognize certain steps in the function because it couldn't reach information stored in code1

And of course, I expected this to work like a charm.

Any help or Ideas are appreciated. :)

1 Sheet structure: Sample data

PURCHASES:

SALES:

STOCK:

If I understand your data setup correctly, you could feed the function a table and the required sheetname and the function would clean and combine the data.

I also understand that your data is the contents of a worksheet (six of the sheets) and not a Table.

That being the case, starting with a Workbook which you will import, you can feed your function to process the worksheets as follows with the sheetname STOCK of PURCHASING or SALES

The imported workbook will show up at a step named something like Imported Excel Workbook and would look like:

You can call your processing function with a function call such as:

    #"Imported Excel Workbook" = Excel.Workbook(#"C:full_path_of_workbook"),
    #"Sheets to Process" = fnProcessSheet(#"Imported Excel Workbook", "SALES")[Data]

And this function, with the above call, would then process the SALES and SALES(Forecast) worksheets.

Whether is makes sense to have three separate calls to this function -- one call for each pair of sheets, or a single call that processes all six sheets, depends on what you will be doing with the output, but this outline should get you started.

(tbl as table, sheetname as text)=>

let 
    #"Actual" = Table.SelectRows(tbl, each [Name]=sheetname)[Data],
    #"Forecast" = Table.SelectRows(tbl, each [Name]=sheetname & "(Forecast)")

//Combine and clean the data