Repeat steps for different files (In a list)

[Sabo256w]

Hi All, 

I have multiple excel files & and within those files are sheets that are similar in structure. I have come up with some transformations that can be applied to all of them. The only two items that need to be changed are the following:

"ExcelPathStr" - Location to the specific file

"SheetName" - Name of Excel Sheet

To achieve this, I've added an optional argument that will allow me to input these values: 

( optional ExcelPathStr as text, optional SheetName as text ) =>

I've added these variables in the relevant places: 

//Change Excel Path
  Source = Excel.Workbook(File.Contents(ExcelPathStr), null, false),

    // Change Excel Sheet
    AUDIT_Sheet = Source{[Item=SheetName,Kind="Sheet"]}[Data],

This has given me a parameter to input the names: 

This is the full code with the data cleaning steps I need to repeat: 

( optional ExcelPathStr as text, optional SheetName as text ) =>
 
let

    //Change Excel Path
  Source = Excel.Workbook(File.Contents(ExcelPathStr), null, false),

    // Change Excel Sheet
    AUDIT_Sheet = Source{[Item=SheetName,Kind="Sheet"]}[Data],

    // Data Cleaning Starts Here:
    RemovedBLANKColumns = Table.RemoveColumns(AUDIT_Sheet,{"Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34", "Column35", "Column36", "Column37"}),

    RemovedTopHeaders = Table.Skip(RemovedBLANKColumns,1),

    PromotedHeaders = Table.PromoteHeaders(RemovedTopHeaders, [PromoteAllScalars=true]),

    RemovedBottomBLANKRows = Table.FirstN(PromotedHeaders,139),

    FilteredMeasureRows = Table.SelectRows(RemovedBottomBLANKRows, each [Column1] <> " " and [Column1] <> "   CARL royalty" and [Column1] <> "   CARL royalty percentage" and [Column1] <> "   Technical staff" and [Column1] <> "Available hours per person" and [Column1] <> "Average net hourly billing rate" and [Column1] <> "Cost of Administrative staff (-)" and [Column1] <> "Cost of External subcontractors" and [Column1] <> "Cost of Intra-Group subcontracting" and [Column1] <> "Fee Income" and [Column1] <> "Gross Margin" and [Column1] <> "Gross Margin %" and [Column1] <> "Margin" and [Column1] <> "Margin %" and [Column1] <> "Out of pocket expenses" and [Column1] <> "Service Line Contribution" and [Column1] <> "Total Available hours" and [Column1] <> "Total Chargeable hours per person" and [Column1] <> "Total FTEs" and [Column1] <> "Total other charges" and [Column1] <> "Total Overheads" and [Column1] <> "Total Production" and [Column1] <> "Total Utilisation" and [Column1] <> "Under-recovery %" and [Column1] <> "Utilisation"),
    
    RemovedTotalColumn = Table.RemoveColumns(FilteredMeasureRows,{"TOTAL"}),

    AddedParentCat = Table.AddColumn(RemovedTotalColumn, "Parent Category", each if [Column1] = "Full-Time Equivalents" then "Full-Time Equivalents" else if [Column1] = "Chargeable hours per person" then "Chargeable hours per person" else if [Column1] = "Total Chargeable hours" then "Total Chargeable hours" else if [Column1] = "Available hours calculation" then "Available hours calculation" else if [Column1] = "Available hours" then "Available hours" else if [Column1] = "Utilisation" then "Utilisation" else if [Column1] = "Average gross billing rate per chrg hour" then "Average gross billing rate per chrg hour" else if [Column1] = "Cost of Technical staff" then "Cost of Technical staff" else if [Column1] = "Cost of Carl Partners" then "Cost of Carl Partners" else if [Column1] = "Cost of Administrative staff" then "Cost of Administrative staff" else if [Column1] = "Other charges" then "Other charges" else null),

    ReorderedParentCategory = Table.ReorderColumns(AddedParentCat,{"Parent Category", "Column1", "SEPTEMBER", "OCTOBER", "NOVEMBER", "DECEMBER", "JANUARY", "FEBRUARY", "MARCH", "APRIL", "MAY", "JUNE", "JULY", "AUGUST"}),

    RemovedUtulizationRows = Table.AlternateRows(ReorderedParentCategory,54,7,92),

    FilledDownParentCategory = Table.FillDown(RemovedUtulizationRows,{"Parent Category"}),

    FilteredOutExtraParentCatfrom2ndCol = Table.SelectRows(FilledDownParentCategory, each ([Column1] <> "Available hours" and [Column1] <> "Available hours calculation" and [Column1] <> "Average gross billing rate per chrg hour" and [Column1] <> "Chargeable hours per person" and [Column1] <> "Cost of Administrative staff" and [Column1] <> "Cost of Technical staff" and [Column1] <> "Full-Time Equivalents" and [Column1] <> "Other charges" and [Column1] <> "Total Chargeable hours")),

    ReplacedALLNULLS = Table.ReplaceValue(FilteredOutExtraParentCatfrom2ndCol,null,0,Replacer.ReplaceValue,{"SEPTEMBER", "OCTOBER", "NOVEMBER", "DECEMBER", "JANUARY", "FEBRUARY", "MARCH", "APRIL", "MAY", "JUNE", "JULY", "AUGUST"}),

    TrimmedText = Table.TransformColumns(ReplacedALLNULLS,{{"Column1", Text.Trim, type text}}),

    RenamedColumns = Table.RenameColumns(TrimmedText,{{"Column1", "Cost Category"}}),

    UnpivotedMonths = Table.Unpivot(RenamedColumns, {"SEPTEMBER", "OCTOBER", "NOVEMBER", "DECEMBER", "JANUARY", "FEBRUARY", "MARCH", "APRIL", "MAY", "JUNE", "JULY", "AUGUST"}, "Attribute", "Value"),

    RenamedColumns1 = Table.RenameColumns(UnpivotedMonths,{{"Attribute", "Month"}}),

    AddedDepartment = Table.AddColumn(RenamedColumns1, "Department", each SheetName),

    AddedMUType = Table.AddColumn(AddedDepartment, "Management Unit", each "Service Line Management Unit"),

    ChangedToDecimalType = Table.TransformColumnTypes(AddedMUType,{{"Value", type number}})
in
    ChangedToDecimalType

So my question is, instead of using the variables and changing them everytime the sheet or excel file changes. How can I create a list of the sheets and paths and have the Data cleaning steps repeated throughout the list? 

I'm assuming I will need two lists because I have 2items that need to change after the cleaning operation? 

I'm fairly new to the advanced editor so if you could add a little extra explanation that would be great!

Thanks in advance

[mahoneypat · Accepted solution]

Start with a two-column table (filepath and sheet name), either with the Enter Data interface or bring in an Excel sheet with that info, and then on the Add Column tab click on Invoke Custom Function. You can then use the values from your two columns as the inputs, and then expand the resulting column of Tables.

Pat

[Sabo256w]

Works like a charm 😁

Thanks Pat!