"The credentials provided for the Sharepoint source are invalid" when using custom functions

Hi dragonhood,

That specific error (There weren't enough elements...) happens because the code is trying to access the first row {0} of a table that is empty.

Why? It means that at least one of the Excel files inside your SharePoint folder does not contain a sheet named exactly "Charts". When the code tries to grab that sheet and doesn't find it, it crashes.

To fix this, we need to modify the Step 5 (ExtractCharts) to handle errors gracefully (using try...otherwise null) and add a filter immediately after to exclude files that don't have the required sheet.

Here is the updated code block. Please replace your entire query with this version:

let
  // 1. Load SharePoint files using the parameter
  Source = SharePoint.Files(SharepointSite, [ApiVersion = 15]),

  // 2. Keep only the required folder
  FilteredRows = Table.SelectRows(Source, each [Folder Path] = FolderPath),

  // 3. Exclude hidden files
  VisibleFiles = Table.SelectRows(FilteredRows, each [Attributes]?[Hidden]? <> true),

  // 4. Read Excel content directly (Inline transformation)
  ExcelContent = Table.AddColumn(VisibleFiles, "Excel", each Excel.Workbook([Content], null, true)),

  // 5. Extract specific sheet "Charts" WITH ERROR HANDLING
  // If the sheet "Charts" doesn't exist, it returns null instead of crashing
  ExtractCharts = Table.AddColumn(
    ExcelContent,
    "ChartsSheet",
    each try Table.SelectRows([Excel], each [Item] = "Charts" and [Kind] = "Sheet"){0}[Data] otherwise null
  ),

  // 5.1 NEW STEP: Filter out files that didn't have the "Charts" sheet
  RemoveMissingSheets = Table.SelectRows(ExtractCharts, each [ChartsSheet] <> null),

  // 6. Promote headers inside each nested table
  PromoteHeaders = Table.TransformColumns(
    RemoveMissingSheets,
    {"ChartsSheet", each Table.PromoteHeaders(_, [PromoteAllScalars = true])}
  ),

  // 7. Rename file column to keep source traceability
  RenameSource = Table.RenameColumns(PromoteHeaders, {{"Name", "SourceName"}}),

  // 8. Expand data from the nested tables
  ExpandedData = Table.ExpandTableColumn(
    RenameSource,
    "ChartsSheet",
    Table.ColumnNames(PromoteHeaders{0}[ChartsSheet])
  ),

  // 9. Set Standard types
  ChangeType = Table.TransformColumnTypes(
    ExpandedData,
    {
      {"SourceName", type text},
      {"Period", type date},
      {"Total ISFs Filed", Int64.Type},
      {"Total On Time ISFs", Int64.Type},
      {"Total Late ISFs", Int64.Type},
      {"Percentage On Time", Percentage.Type},
      {"Percentage Late", Percentage.Type}
    }
  ),

  // 10. Remove unneeded columns
  RemoveExtraCols = Table.RemoveColumns(ChangeType, {"Column7", "Period_id"}),

  // 11. Filter only valid rows
  FilterValid = Table.SelectRows(
    RemoveExtraCols,
    each ([Percentage On Time] <> null and [Percentage Late] <> null)
  ),

  // 12. Duplicate SourceName for split logic
  DuplicateSource = Table.DuplicateColumn(FilterValid, "SourceName", "SourceName_Copy1"),

  // 13. Split importer info (Transition from Text to Number)
  SplitImporter = Table.SplitColumn(
    DuplicateSource,
    "SourceName_Copy1",
    Splitter.SplitTextByCharacterTransition({"c"}, each not List.Contains({"0" .. "9"}, _)),
    {"Importer", "ImporterCodePart1", "ImporterCodePart2"}
  ),

  // 14. Clean Importer Name
  CleanImporter = Table.ReplaceValue(
    SplitImporter,
    "ISF REPORT - ",
    "",
    Replacer.ReplaceText,
    {"Importer"}
  ),

  // 15. Merge importer code parts
  MergeImporterCode = Table.CombineColumns(
    CleanImporter,
    {"ImporterCodePart1", "ImporterCodePart2"},
    Combiner.CombineTextByDelimiter("", QuoteStyle.None),
    "ImporterCode"
  ),

  // 16. Clean file extension from code
  CleanImporterCode = Table.ReplaceValue(
    MergeImporterCode,
    ".xlsx",
    "",
    Replacer.ReplaceText,
    {"ImporterCode"}
  ),

  // 17. Reorder final columns
  FinalReorder = Table.ReorderColumns(
    CleanImporterCode,
    {
      "SourceName",
      "Importer",
      "ImporterCode",
      "Period",
      "Total ISFs Filed",
      "Total On Time ISFs",
      "Total Late ISFs",
      "Percentage On Time",
      "Percentage Late"
    }
  )
in
  FinalReorder

This ensures that if a file is missing the specific tab, it is simply ignored instead of causing an Enumeration error.

Let me know if it runs smoothly now!