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

Hi, dragonhood 

The "Invalid Credentials" error in Dataflows often happens because the Power Query engine in the Service struggles to validate credentials inside a separate "Custom Function" object.

The most robust way to fix this is to move the transformation logic directly into your main query (the "Inline" approach). This bypasses the need for the Invoke Custom Function step entirely.

Here is the solution based on your data structure:

Step 1: Create your Parameters In your Dataflow, create two text parameters to handle the connection dynamically:

1.            Name: SharepointSite

o             Type: Text

o             Current Value: https://yourdomain.sharepoint.com/sites/YourSite

o             (Enter the root URL of your SharePoint site here)

1.            Name: FolderPath

o             Type: Text

o             Current Value: Shared Documents/YourFolder/

o             (Note: Copy the exact value from the "Folder Path" column in the preview to ensure it matches)

Step 2: Use this M Code Create a blank query and paste the code below. This script connects, filters the specific folder using the parameters above, extracts the "Charts" sheet from the Excel files, and performs the cleanup/splitting logic without calling external functions.

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"
  ExtractCharts = Table.AddColumn(
    ExcelContent,
    "ChartsSheet",
    each Table.SelectRows([Excel], each [Item] = "Charts" and [Kind] = "Sheet"){0}[Data]
  ),

  // 6. Promote headers inside each nested table
  PromoteHeaders = Table.TransformColumns(
    ExtractCharts,
    {"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 approach is much more stable in Dataflows because all lineage is contained within a single query context.

Let me know if this works for you!