토론:대문: 두 판 사이의 차이

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

@@ 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