|
|
| 1번째 줄: |
1번째 줄: |
| 토론 : 대문 만들기 연습중... | | 토론 : 대문 만들기 연습중... |
| 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
| |