// debug
1 meta [IsParameterQuery=true, Type="Number", IsParameterQueryRequired=true]
// FolderPath
"J:\xlsx" meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]
// Запрос1
let
Source = Folder.Files(FolderPath),
Filtered = Table.SelectRows(Source, each not [Attributes]?[NotContentIndexed]?),
Transform = Table.TransformColumns(Filtered, {"Content",each fnTransformFile(_, 0, 0)}),
ForDebug = Transform meta (if debug=1 then [bin=Filtered{i}[Content]] else [])
in
ForDebug
// fnTransformSheet
let
Источник = (tbl as any, debug as number) => let
Source = if debug=1 then Value.Metadata(Workbook)[tbl] else tbl,
Promoted = Table.PromoteHeaders(Source),
Result = {Promoted,Table.ColumnNames(Promoted)}
in
Result
in
Источник
// fnTransformFile
let
Источник = (Bin as binary, debug as number, j as number) => let
Source = if debug=1 then Value.Metadata(Запрос1)[bin] else Bin,
Workbook = Excel.Workbook(Source),
Filtered = Table.SelectRows(Workbook, each ([Kind] = "Sheet"))[[Name],[Data]],
Transform = Table.SplitColumn(Filtered,"Data",(a)=>fnTransformSheet(a, 0),{"Data","Columns"}),
Expand = Table.ExpandTableColumn(Transform[[Name],[Data]], "Data", List.Distinct(List.Combine(Transform[Columns]))),
ForDebug = Expand meta (if debug=1 then [tbl=Filtered{j}[Data]] else [])
in
ForDebug
in
Источник
// i
0 meta [IsParameterQuery=true, Type="Number", IsParameterQueryRequired=true]
// Bin
null meta [IsParameterQuery=true, Type="Binary", IsParameterQueryRequired=false]
// Workbook
let
Source = if debug=1 then Value.Metadata(Запрос1)[bin] else Bin,
Workbook = Excel.Workbook(Source),
Filtered = Table.SelectRows(Workbook, each ([Kind] = "Sheet"))[[Name],[Data]],
Transform = Table.SplitColumn(Filtered,"Data",(a)=>fnTransformSheet(a, 0),{"Data","Columns"}),
Expand = Table.ExpandTableColumn(Transform[[Name],[Data]], "Data", List.Distinct(List.Combine(Transform[Columns]))),
ForDebug = Expand meta (if debug=1 then [tbl=Filtered{j}[Data]] else [])
in
ForDebug
// j
0 meta [IsParameterQuery=true, Type="Number", IsParameterQueryRequired=false]
// tbl
null meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=false]
// Sheet
let
Source = if debug=1 then Value.Metadata(Workbook)[tbl] else tbl,
Promoted = Table.PromoteHeaders(Source),
Result = {Promoted,Table.ColumnNames(Promoted)}
in
Result
|
