Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
alexandrau
New Member

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 in the same manner; 
i have created a transform sample file, that looks at all the sheets regardless of the sheet names and transforms everything identically. 

 

After transforming, i should end up with columns that include:

- Date Received (when the used files where received)
- Future Dates

- Volume

 

In one workbook, I can have for example in the same workbook, received 21-July-2022, a Sheet for July dates and one for August dates. 

 

when i implement to apply for all my workbook and sheets the transformation i noticed that for every second sheet, 
continuing from the above example of July & August in the same file; the relative rows for August show instead of August future dates July and instead of August volume the July volume. 

 

Thanks, Alex

 

3 REPLIES 3
jennratten
Super User
Super User

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.

jennratten_2-1658835315497.png

 

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.

jennratten_1-1658835111192.png

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

jennratten_3-1658835756914.png

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.

jennratten_4-1658835875544.png

 

Hi @jennratten,

 

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

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

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.