cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Peter_Yaacoub
Frequent Visitor

Combine selected worksheets from workbook in Folder

Hi everyone! 

New to power query and I need some help please.

I am currently connected to a folder and I want to apply my transformations to only a certain number of worksheets in that workbook that gets dropped every month, based on a sample worksheet.

The code generated from the steps applied to CPMI worksheet is shared below.

Any suggestions on how to solve this are much appreciated.

 

Thank you!

 

 

let
    Source = Folder.Files("W:\Power BI\Manufacturing Data"),
    #"Filtered Rows" = Table.SelectRows(Source, each not Text.Contains([Name], "~")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Name"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "GetManufacturingData", each Excel.Workbook([Content])),
    #"Expanded GetManufacturingData" = Table.ExpandTableColumn(#"Added Custom", "GetManufacturingData", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded GetManufacturingData", each ([Hidden] = false) and ([Kind] = "Sheet") and ([Name.1] = "CPMI")),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows1",{"Content", "Name", "Name.1", "Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns1", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28", "Column29", "Column30", "Column31", "Column32", "Column33", "Column34"}),
    #"Removed Other Columns2" = Table.SelectColumns(#"Expanded Data",{"Column16", "Column20", "Column21", "Column23", "Column25"}),
    #"Kept First Rows" = Table.FirstN(#"Removed Other Columns2",27),
    #"Removed Top Rows" = Table.Skip(#"Kept First Rows",3),
    #"Transposed Table" = Table.Transpose(#"Removed Top Rows"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true])
in
    #"Promoted Headers"

 

 

 

 

1 ACCEPTED SOLUTION

To select multiple sheets by name, you could do it like this:

    #"Filtered Rows1" = Table.SelectRows(#"Expanded GetManufacturingData", each ([Hidden] = false) and ([Kind] = "Sheet") and ([Name.1] = "CPMI" or [Name.1] = "Sheet2" or [Name.1] = "Sheet3") ),

or like this:

    #"Filtered Rows1" = Table.SelectRows(#"Expanded GetManufacturingData", each ([Hidden] = false) and ([Kind] = "Sheet") and ( List.Contains({"CPMI", "Sheet2", "Sheet3"}, [Name.1] ) ) ),

View solution in original post

3 REPLIES 3
jennratten
Super User
Super User

Hello - you would need to edit the step below in your script with the criteria that selects the sheets to be included.  

    #"Filtered Rows1" = Table.SelectRows(#"Expanded GetManufacturingData", each ([Hidden] = false) and ([Kind] = "Sheet") and ([Name.1] = "CPMI")),

For example, if you want to include all sheets that have names beginning with CPMI, regardless of whether the letters CPMI were uppercase or lowercase, you could do this:

    #"Filtered Rows1" = Table.SelectRows(#"Expanded GetManufacturingData", each ([Hidden] = false) and ([Kind] = "Sheet") and Text.Contains ([Name.1], "CPMI", Comparer.OrdinalIgnoreCase) ),

On your #"Expanded Data" all the sheets will be combined and expanded.  

 

 

Thank you for your response Jenn.

I would like to select multiple worksheets by name referring to column Name.1 instead of referring to certain characters if that is possible.

 

Thanks again.

 

Peter

To select multiple sheets by name, you could do it like this:

    #"Filtered Rows1" = Table.SelectRows(#"Expanded GetManufacturingData", each ([Hidden] = false) and ([Kind] = "Sheet") and ([Name.1] = "CPMI" or [Name.1] = "Sheet2" or [Name.1] = "Sheet3") ),

or like this:

    #"Filtered Rows1" = Table.SelectRows(#"Expanded GetManufacturingData", each ([Hidden] = false) and ([Kind] = "Sheet") and ( List.Contains({"CPMI", "Sheet2", "Sheet3"}, [Name.1] ) ) ),

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Kudoed Authors