Forum Discussion

alexandrau's avatar
alexandrau
New Member
4 years ago

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

Hi All,    I am using a folder with multiple workbooks as my data source, and the workbooks have multiple sheets that i also need.    Before merging that data, each sheet needs to be transfomred ...
Need Help
Cyiin's avatar
Cyiin
Advocate I
3 years ago

Hi jennratten,

 

Can the script be modified to include only specific sheets instead of all sheets in the file? How would this be done?

jennratten's avatar
jennratten
Super User
3 years ago

Hello! Yes, you can certianly do this.  Here is an example below.  I have added a new line to the previous script in which specific sheets are selected.  Note, if you have more than just sheets in your workbook, like tables, named ranges, etc. also, then you may want to select the names and also filter for objects that are sheets - just in case you have a named range or table that has the same name as the sheet you are wanting to specify. 

let
    Source = Excel.Workbook(Parameter1, null, true),
    // Update the sheet names inside the curly braces { } with your actual sheet names.
    SelectSheets = Table.SelectRows ( Source, each List.Contains ( 
        {"Sheet1", "Sheet2" }, [Name] ) 
    ),
    RenameDataColumn = Table.RenameColumns ( SelectSheets, {{"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