토론:대문 편집하기

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