Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Nathaniel_C
Super User
Super User

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

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

 





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

Proud to be a Super User!




4 REPLIES 4
ImkeF
Super User
Super User

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"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

 


Nathaniel





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

Proud to be a Super User!




Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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. 


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





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

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.