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