Combine selected worksheets from workbook in Folder

[Peter_Yaacoub]

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"

[jennratten]

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.  

[Peter_Yaacoub]

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

[jennratten · 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] ) ) ),