= (#"Binary Sample Parameter" as binary) => let
Source = Excel.Workbook(#"Binary Sample Parameter", null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data]
in
Sheet1_Sheet
//fnImportSheet
(
BinaryFile as binary,
optional SheetName as nullable text,
optional SheetIndex as nullable number,
optional UseHeaders as nullable logical
) as table =>
let
useHeaders = if UseHeaders is null then false else UseHeaders,
sheetIndex =
if SheetName <> null
then null
else
if SheetIndex is null
then 0
else SheetIndex - 1,
Source = Excel.Workbook( BinaryFile, useHeaders, false ),
SheetsOnly = Table.SelectRows( Source, each [Kind] = "Sheet" ),
Sheet =
if sheetIndex is null
then SheetsOnly{ [Item = SheetName] }[Data]
else SheetsOnly{ sheetIndex }[Data]
in
Sheet
/*
Designed to be invoked when adding a custom column.
*/
// GetSheetsFromFolder
let
Source =
DummyFolder,
KeepContentAndName =
Table.SelectColumns( Source, {"Name", "Content"} ) as table,
RemoveFileExtension =
Table.TransformColumns(
KeepContentAndName,
{
"Name",
each Text.Start(
_,
Text.PositionOf( _, ".", Occurrence.Last )
),
type text
}
) as table,
ImportSheets =
Table.TransformColumns(
RemoveFileExtension,
{"Content", each fnImportSheet( _, null, null, true), type table}
) as table,
RenameFileNameColumn =
Table.RenameColumns( ImportSheets, {{"Name", "FileName"}} ) as table,
ColumnNames =
List.Union(
List.Transform(
RenameFileNameColumn[Content],
each Table.ColumnNames( _ )
)
) as list,
NewColumnNames =
List.Transform(
ColumnNames,
each if _ = "FileName" then "Imported." & _ else _
) as list,
ExpandData =
Table.ExpandTableColumn(
RenameFileNameColumn,
"Content",
ColumnNames,
NewColumnNames
)
in
ExpandData