Using a path name from an SQL query to open a csv file in power query

Hi Nathaniel_C ,
if the content is all on one site, this is a possible approach (FilePath is the parameter with the full path to the file on SP):

let
    Source = SharePoint.Files("https://<<YourDomainHere>>.sharepoint.com/sites/<<YourSiteNameHere>>", [ApiVersion = 15]),
    #"Inserted Merged Column" = Table.AddColumn(Source, "fullPath", each Text.Combine({[Folder Path], [Name]}, ""), type text),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Merged Column", each ([fullPath] = FilePath)),
    #"Content 1" = #"Filtered Rows"{0}[Content],
    #"Imported Excel Workbook" = Excel.Workbook(#"Content 1")
in
    #"Imported Excel Workbook"

 
For different sites, I don't know an approach that wouldn't trigger dynamic datasource error.

edit: Actually, this might be faster:

let
    Source = SharePoint.Contents("https://<<YourDomainHere>>.sharepoint.com/sites/<<YourSiteName>>", [ApiVersion = 15]),
    Documents = Source{[Name="Shared Documents"]}[Content],
    #"Filtered Rows1" = Table.SelectRows(Documents, each ([Name] <> "Forms")),
    #"Expanded Content" = Table.ExpandTableColumn(#"Filtered Rows1", "Content", {"Content", "Name", "Extension", "Folder Path"}, {"Content.1", "Name.1", "Extension.1", "Folder Path.1"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Expanded Content", "fullPath", each Text.Combine({[Folder Path.1], [Name.1]}, ""), type text),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Merged Column", each ([fullPath] = FilePath)),
    #"Content 1" = #"Filtered Rows"{0}[Content.1],
    #"Imported Excel Workbook" = Excel.Workbook(#"Content 1")
in
    #"Imported Excel Workbook"

Hi Nathaniel_C - it depends on the location of the file.  If you specify the file name and location in a connector like "Excel.Workbook" Power BI will try to create credentials for the specified file.  The Power BI service will not like the Dynamic Data Source, so it won't allow the Data Source credentials to change.

This would work if the file is located in SharePoint Online.  In this situtation, you can connect to the SharePoint URL and then dynamically find the file name using filter on the Folder Path and File Name.  This trick is that credentials are validated at the SharePoint site level, so the file can change dynamically.  Though you may need to "Ignore" the Privacy Setting to pass the file name from another source.