Power Query - Transform Sample File for Multiple Workbooks with Multiple Sheets

Hello - if you are using the get files from folder connector, that creates a sample transformation function for you, when the function is invokes (in the table where the data is combined), it is most likely applying the transformations specified for only the first worksheet in the file.

In this example, in my Transform Sample File query, we can see that it is setting up the transformations for the sheet named "Sheet1" and I am renaming columns and removing other columns.

Then when the function is invoked, we can see that in my example the function is failing, because the all subsequent files do not have a sheet named Sheet1.

Instead, in the Transform Sample File, you can modify the script to apply the transformations to all rows, like so...

let
    Source = Excel.Workbook(Parameter1, null, true),
    RenameDataColumn = Table.RenameColumns ( Source, {{"Data", "DataOLD"}}),
    //Sheet2 = Source{[Name="Sheet1"]}[Data],
    TransformAllSheets = Table.AddColumn ( 
        RenameDataColumn,
        "Data",
        each
        let
            RenameColumns = Table.RenameColumns([DataOLD],{{"Column1", "NewColumn1"}}),
            SelectColumns = Table.SelectColumns(RenameColumns,{"NewColumn1"})
        in
            SelectColumns
    )
in
    TransformAllSheets

Now in the step where the function is invoked, we can see that it is working.