Forum Discussion
Load Query Based on Dynamic File Name (SharePoint)
I'm looking to ensure my Power BI report pulls the latest data from a SharePoint file without having to overwrite the current sharepoint file with the latest data. Each day a new file is received...
Steps would be like this:
- Use "SharePoint folder" connector to connect to the site using the site root URL.
- Click "Transform Data" button in preview window.
- Filter Folder Path to folder contain the files (will have a trailing "/").
- Filter Extension column to ".xlsx" .
- Duplicate "Name" column, and/or Replace Values in Name column to remove filter name prefix, so remaining text is "YYYYMMDDhhmmss).xlsx"
- Sort "Name" column Descending
- Click on "Binary" in first row of "Content" column to drill into the XLSX file, then drill into the desired Sheet. NOTE: the default generated Power Query code will used hard-coded named identifiers. You may want to edit this to use index or position-based identifiers instead.
jeffshieldsdev
Solution Sage
Solution SageThe row filtering criteria is embedded in the #"Filter Rows" step. You'll need to move this logic into step(s) before #"Filtered Rows", filtering to just one row, and then selecting it.
Something like this:
let
Source = SharePoint.Files("https://sharepoint.com/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each [Name] = "Filename" and [Folder Path] = "URL"),
Content = #"Filtered Rows"{0}[Content],
#"Imported Excel Workbook" = Excel.Workbook(Content)
in
#"Imported Excel Workbook"
this works! thank you so much!