토론:대문

토론 : 대문 만들기 연습중... let

CombineWorkbook = (optional FolderPath as text, optional FileName as text, optional SheetName as text) as table =>

let tempPath = try Excel.CurrentWorkbook(){[Name="취합정보"]}[Content][FolderPath]{0} otherwise null, tempFile = try Excel.CurrentWorkbook(){[Name="취합정보"]}[Content][FileName]{0} otherwise null, tempSheet = try Excel.CurrentWorkbook(){[Name="취합정보"]}[Content][SheetName]{0} otherwise null, fPath = if FolderPath = null then if tempPath <> null then tempPath else "" else FolderPath, fName = if FileName = null then if tempFile <> null then tempFile else "" else FileName, sName = if SheetName = null then if tempSheet <> null then tempSheet else "" else SheetName, list_fName = Text.Split(fName, ","), list_sName = Text.Split(sName,","), init = Folder.Files(fPath), remove_temp = Table.SelectRows(init, each not Text.StartsWith([Name], "~")), filter_filename = Table.SelectRows(remove_temp, each List.AnyTrue(List.Transform(list_fName, (substring) => Text.Contains([Name], substring, Comparer.OrdinalIgnoreCase)))), select_content = Table.SelectColumns(filter_filename,{"Content"}), open_workbook = Table.AddColumn(select_content, "ExcelFiles", each Excel.Workbook([Content])), select_sheets = Table.RemoveColumns(open_workbook,{"Content"}), expand_sheet_data = Table.ExpandTableColumn(select_sheets, "ExcelFiles", {"Name", "Data", "Kind"}, {"Name", "Data", "Kind"}), filter_sheets = Table.SelectRows(expand_sheet_data, each ([Kind] = "Sheet")), filter_sheet_name = Table.SelectRows(filter_sheets, each List.AnyTrue(List.Transform(list_sName, (substring) => Text.Contains([Name], substring, Comparer.OrdinalIgnoreCase)))), open_sheets = Table.SelectColumns(filter_sheet_name,{"Data"}), promote_header = Table.AddColumn(open_sheets, "HeaderTable", each Table.PromoteHeaders([Data])), remove_error = Table.RemoveRowsWithErrors(promote_header, {"HeaderTable"}), col_list = Table.ColumnNames(Table.Combine (remove_error[HeaderTable])), expand_all_sheets = Table.ExpandTableColumn(remove_error, "HeaderTable", col_list, col_list), results = Table.RemoveColumns(expand_all_sheets,{"Data"}) in results in CombineWorkbook