Parameterized Query for Web API connection with SharePoint file

Hi SwaTHasSasIN 

Instead of invoking a function per file path:

You need to follow Power BI's "combine binaries" pattern — similar to when you click “Combine Files” after connecting to a folder.

Here’s the clean way:

Step-by-Step Solution Using SharePoint.Files() (Dataflow-safe)

Connect to SharePoint folder (not a single file):
let
Source = SharePoint.Files("https://yourtenant.sharepoint.com/sites/yoursite", [ApiVersion = 15])
in
Source

Filter for the correct folder and file types (e.g. .csv, .xlsx, etc.):
Filtered = Table.SelectRows(Source, each Text.StartsWith([Folder Path], "https://yourtenant.sharepoint.com/sites/yoursite/Shared Documents/YourFolder/")
and Text.EndsWith([Name], ".csv"))

Use a single transformation function across all binaries:

• Add a column: TransformFile = Table.TransformColumns(Filtered, {"Content", each MyTransformFunction(_)})

OR use the Combine Files GUI (it auto-creates a helper query TransformFile behind the scenes — reuse it!)

Don’t build dynamic file paths manually 
Let Power BI handle the content by passing [Content] from the SharePoint.Files table.
Example of a Valid Transform Function (static):
let
MyTransformFunction = (file as binary) =>
let
Source = Csv.Document(file, [Delimiter=",", Columns=5, Encoding=65001, QuoteStyle=QuoteStyle.None]),
Promoted = Table.PromoteHeaders(Source, [IgnoreErrors=true])
in
Promoted
in
MyTransformFunction
Then use this function like:

Transformed = Table.AddColumn(Filtered, "Data", each MyTransformFunction([Content]))

Notes on SharePoint.Contents()

SharePoint.Contents() is a more granular function that sometimes solves dynamic path issues, but only for certain auth modes. If you got a “Invalid Credentials” error:

• Try using Organizational Account when prompted

• Ensure your tenant allows OAuth flows via SharePoint.Contents (some don't)

You may have to switch back to SharePoint.Files() and apply the above technique instead

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!