Forum Discussion
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"
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] ) ) ),
3 Replies
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] ) ) ),
