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.
Thanks Jeff. And to confirm, would this have the effect that each morning Power BI will pull from the latest/top report in the folder?
Yes, it should. Each refresh will pull the max dated file.