let CombineExcelFolder = (ExcelFolderPath) =>let
Source = Folder.Files(ExcelFolderPath), #"Expanded Attributes" = Table.ExpandRecordColumn(Source, "Attributes", {"Archive", "Compressed", "Content Type", "Device", "Directory", "Encrypted", "Hidden", "Kind", "Normal", "NotContentIndexed", "Offline", "ReadOnly", "ReparsePoint", "Size", "SparseFile", "System", "Temporary"}, {"Attributes.Archive", "Attributes.Compressed", "Attributes.Content Type", "Attributes.Device", "Attributes.Directory", "Attributes.Encrypted", "Attributes.Hidden", "Attributes.Kind", "Attributes.Normal", "Attributes.NotContentIndexed", "Attributes.Offline", "Attributes.ReadOnly", "Attributes.ReparsePoint", "Attributes.Size", "Attributes.SparseFile", "Attributes.System", "Attributes.Temporary"}),
#"FilteredRows"= Table.SelectRows(#"Expanded Attributes", each ([Attributes.Kind] = "Excel File") and ([Attributes.Hidden] = false) and ([Attributes.Encrypted] = false) and (Text.Contains([Name], ""))),
#"AddedCustom"= Table.AddColumn(#"Filtered Rows", "Custom", each Excel.Workbook([Content])),
#"RemovedErrors1"= Table.RemoveRowsWithErrors(#"Added Custom", {"Custom"}),
//非Excel2003的Excel处理：
#"FilteredNo2003"= Table.SelectRows(#"Removed Errors1", each [Extension] <> ".xls"),
#"ExpandedCustom"= Table.ExpandTableColumn(#"Filtered No2003", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name.1", "Data", "Item", "Kind", "Hidden"}),
//Excel内部筛选条件 1.只包含sheet表，不含定义域及表单；2.非隐藏；3.Sheet表命名包含“Sheet”关键字.
//#"FilteredRows5"= Table.SelectRows(#"Filtered Rows2", each Text.Contains([Item], "Sheet")),
#"FilteredRows3"= Table.SelectRows(#"Expanded Custom", each ([Kind] = "Sheet")),
//Excel2003的Excel处理：
#"Filtered2003"= Table.SelectRows(#"Removed Errors1", each [Extension] = ".xls"),
#"ExpandedCustom1"= Table.ExpandTableColumn(#"Filtered 2003", "Custom", {"Name", "Data"}, {"Name.1", "Data"}),
#"FilteredRows2"= Table.SelectRows(#"Expanded Custom1", each not Text.EndsWith([Name.1], "$FilterDatabase")),
funAddcolumn1 = (parTable,x as text) as table =>
let
Source1 = Table.AddColumn(parTable,"文件名",each x)
in
Source1,
CombinedExcels =Table.Combine({#"Filtered Rows3" ,#"Filtered Rows2"}),
#"AddedCustom12"= Table.AddColumn(CombinedExcels, "Custom12",each Table.PromoteHeaders([Data])),
已添加自定义 = Table.AddColumn(#"Added Custom12","Custom",each funAddcolumn1([Custom12],[Name])),
删除的其他列 = Table.SelectColumns(已添加自定义,{"Custom"}),
追加的查询 = Table.Combine(删除的其他列[Custom])
in
追加的查询
in
CombineExcelFolder
