cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User
Super User

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors