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
Hello,
Helping a colleague with this project.
Thank you,
Nathaniel
Proud to be a 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
Proud to be a 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
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
63 | |
56 | |
27 | |
17 | |
13 |