152_Power Query 快速合并文件夹中表格之自定义函数 TableXlsxCsv

let
fx0 = (
FolderName as text, 
RemoveFirstRows as number, 
RemoveLastRows as number, 
IsAddColumn as logical, 
optional ColunmName as text, 
optional ColumnType as text, 
optional NameStartNumber as number, 
optional Length as number
) as table =>
let
Types0 = List.Buffer({type text, type number, Int64.Type, type date, type datetime, type time}), 
Types1 = List.Buffer({"文本", "小数", "整数", "日期", "日期时间", "时间"}), 
ColumnNameTable0 = Table.Buffer( Excel.Workbook(File.Contents(Path & "\00_辅助表\01_名称管理.xlsx"), true, true){[Item = "ColumnNameTable", Kind = "Table"]}[Data]), 
ColumnNameTable0Fileds = List.Buffer(Table.ColumnNames(ColumnNameTable0)), 
TypeListNewjpz = List.Buffer(List.Zip({ColumnNameTable0Fileds, List.Transform({0, 2, 0, 0, 2, 0}, each Types0{_})})), 
ColumnNameTable1 = Table.Buffer(Table.TransformColumnTypes(ColumnNameTable0, TypeListNewjpz)), 
ColumnNameTable2 = Table.Buffer(Table.SelectRows(ColumnNameTable1, each ([文件夹] = FolderName))), 
ColumnCount = Table.RowCount(ColumnNameTable2), 
ColumnNameTableX = Table.Buffer(Table.SelectRows(ColumnNameTable2, each [是否显示] = "是")), 
ColumnNameListOld = List.Buffer(List.Transform(ColumnNameTableX[ID], each "Column" & Text.From(_))), 
ColumnNameListNew = List.Buffer(List.Zip({ColumnNameListOld, ColumnNameTableX[统一名称]})), 
TypeListNew = List.Buffer(List.Zip({ColumnNameTableX[统一名称], List.Transform(ColumnNameTableX[字段类型ID], each Types0{_})})), 
FileList0 = Table.Buffer(Folder.Files(Path & "\" & FolderName)), 
BinaryList0 = List.Buffer(FileList0[Content]), 
List0 = List.Buffer({0 .. List.Count(BinaryList0) - 1}), 
Extension0 = List.Buffer(FileList0[Extension]), 
NameList0 = List.Buffer(FileList0[Name]), 
NameList1 = List.Transform(NameList0, each Text.Middle(_, NameStartNumber, Length)), 
TableList0 = List.Buffer(
List.Transform(
List0, 
(n) =>
let
wb = Table.SelectColumns(
if Text.Contains(Extension0{n}, "Csv", Comparer.OrdinalIgnoreCase) then
Table.RemoveLastN(
Table.RemoveFirstN(
Csv.Document(BinaryList0{n}, [Delimiter = ",", Columns = ColumnCount]), 
RemoveFirstRows
), 
RemoveLastRows
)
else
Table.Combine(
List.Transform(
Table.SelectRows(
Excel.Workbook(BinaryList0{n}, null, true), 
each not Text.Contains([Name], "Filter", Comparer.OrdinalIgnoreCase)
)[Data], 
each Table.RemoveLastN(Table.RemoveFirstN(_, RemoveFirstRows), RemoveLastRows)
)
), 
ColumnNameListOld
)
in
if IsAddColumn then Table.AddColumn(wb, ColunmName, each NameList1{n}) else wb
)
), 
Rename0 = Table.Buffer(Table.RenameColumns(Table.Combine(TableList0), ColumnNameListNew)), 
Result = 
if IsAddColumn then
Table.TransformColumnTypes(
Rename0, 
TypeListNew & {{ColunmName, Types0{List.PositionOf(Types1, ColumnType)}}}
)
else
Table.TransformColumnTypes(Rename0, TypeListNew)
in
Result, 
TypeMeta0 = type function (
FolderName as (
type text
meta [
Documentation.FieldCaption = "1参:FolderName, Path 路径下需要合并表的文件夹名称.", 
Documentation.SampleValues = {"订单信息"}, 
Documentation.AllowedValues = List.RemoveItems(
List.Transform(
List.Distinct(Folder.Files(Path)[Folder Path]), 
each Replacer.ReplaceText(Replacer.ReplaceText(_, Path, ""), "\", "")
), 
{"00_辅助表"}
)
]
), 
RemoveFirstRows as (
type number
meta [
Documentation.FieldCaption = "2参:RemoveFirstRows,从顶部开始移除的行数.", 
Documentation.SampleValues = {1}, 
Documentation.AllowedValues = {0 .. 50}
]
), 
RemoveLastRows as (
type number
meta [
Documentation.FieldCaption = "3参:RemoveLastRows,从底部开始移除的行数.", 
Documentation.SampleValues = {0}, 
Documentation.AllowedValues = {0 .. 50}
]
), 
IsAddColumn as (
type logical
meta [
Documentation.FieldCaption = "4参:IsAddColumn,是否需要把文件名称按照规则添加列.", 
Documentation.SampleValues = {false}, 
Documentation.AllowedValues = {false, true}
]
), 
optional ColunmName as (
type text
meta [
Documentation.FieldCaption = "5参:ColunmName,添加列的名称.", 
Documentation.SampleValues = {"NewName"}
]
), 
optional ColumnType as (
type text
meta [
Documentation.FieldCaption = "6参:ColumnType,添加列的类型.", 
Documentation.SampleValues = {"文本"}, 
Documentation.AllowedValues = {"文本", "小数", "整数", "日期", "日期时间", "时间"}
]
), 
optional NameStartNumber as (
type number
meta [
Documentation.FieldCaption = "7参:NameStartNumber,文件名称字符开始的索引.", 
Documentation.SampleValues = {0}, 
Documentation.AllowedValues = {1 .. 50}
]
), 
optional Length as (
type number
meta [
Documentation.FieldCaption = "8参:Length,文件名称在 NameStartNumber 后的长度.", 
Documentation.SampleValues = {1}, 
Documentation.AllowedValues = {1 .. 50}
]
)
) as table
meta [
Documentation.Name = "TableXlsxCsv", 
Documentation.LongDescription
= "返回同一文件夹下相同字段表格合并后的表格,兼容 XLSX, XLS, CSV 三种常见的格式, 同时兼容一个 WorkBook 多个相同字段的 WorkSheet 的 Excel 文件.【注意】:当参数 IsAddColumn 为 true 时,后四个参数 ColunmName, TypeNumber, NameStartNumber, Length 必填; 【00_辅助表】文件夹下的名称管理 Excel 文件需要在使用 TableXlsxCsv 提前配置好.", 
Documentation.WhoAskedTheRightQuestion = "www.jiaopengzi.com", 
Documentation.Author = "焦棚子", 
Documentation.Examples = {
[
Description = "1、从文件夹【订单信息】中合并表格,且不用文件名称添加列.", 
Code = "TableXlsxCsv( " & """订单信息""" & ", 1, 0, false )", 
Result = "当参数 IsAddColumn 为 false 时,后四个参数可以不写;从文件夹【订单信息】中每个表格从顶部移除 1 行后合并表,且不用文件名称添加列."
], 
[
Description = "2、从文件夹【订单信息】中合并表格,使用文件名称按照规则添加列.", 
Code = "TableXlsxCsv( " & """订单信息""" & ", 1, 3, true," & """数据日期""" & ", "&"""日期"""&", 12, 10 )", 
Result
= "当参数 IsAddColumn 为 true 时,后四个参数必填;从文件夹【订单信息】中每个表格从顶部移除 1 行并且从底部移除 3 行后合并表,添加列的名称:数据日期,数据类型为:日期格式,按照文件名称从 12 个字符开始取 10 个字符."
]
}
], 
fx1 = Value.ReplaceType(fx0, TypeMeta0)
in
fx1