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

[Nathaniel_C]

Hello,

Helping a colleague with this project. 

1. The colleague has created a SQL query that finds the path to a csv file. This path changes on a daily basis, so we need to have the path refresh when the .pbix refreshes.
2. The crux of the issue is to get the path name from the SQL query but to use the resultant csv file as the source for the a table in the .pbix file each day.
3. We wish the table to be the same table, day after day.  We are not interested in multiple tables.
4. I am thinking that this can possibly be done with a parameter.
5. Is there a way to put the result of the SQL query into Power Query so that we can use the path name value as a source for a table?
6. This must work in the Power BI service.

@ImkeF  @KenPuls 

Thank you,
Nathaniel

[ImkeF]

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"

[Nathaniel_C]

Thank you, @ImkeF ! I will give this a shot when I get back to work.

Nathaniel

[Daryl-Lynch-Bzy]

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. 

[Nathaniel_C]

Hi @Daryl-Lynch-Bzy ,

Thank you for your quick response. So assuming that we can use SharePoint, how are you suggesting that item #5 happens.
Thank you,

Nathaniel