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 and saved into the same SharePoint folder with the name (________YYYYMMDDhhmmss).xlsx. This has the exact same layout (columns, tabs, etc) as the previous file, however with the latest data.
Rather than having to copy and paste into the current file, overwrite the current file, or change the load data query each day, I'm hoping there is a way for Power BI to look in the same SharePoint folder and use the latest file.
I am new to Power BI so would greatly appreciate any simple instructions, clearly explained. Thanks community 🙂
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.
9 Replies
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.
Good point. The default generated Power Query will--but you can edit it to use index-based identifiers instead of named ones.
I have looked online and i still don't get how i can edit my query to make this update with a new file each day the date changes
where the next day would be ALL_SOL_2024_07_18.csv, to ALL_SOL_2024_07_19.csv
let
Source = SharePoint.Files("https://mydnb.sharepoint.com/sites/teams/na_delivery/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, let latest = List.Max(Source[Date created]) in each [Date created] = latest),
#"ALL_SOL_2024_07_18 csv_https://mydnb sharepoint com/sites/teams/na_delivery/606 Fulfillment Template/UPLOAD/RRF_REPORTS/" = #"Filtered Rows"{[Name="ALL_SOL_2024_07_18.csv",#"Folder Path"="https://mydnb.sharepoint.com/sites/teams/na_delivery/606 Fulfillment Template/UPLOAD/RRF_REPORTS/"]}[Content],
#"Imported CSV" = Csv.Document(#"ALL_SOL_2024_07_18 csv_https://mydnb sharepoint com/sites/teams/na_delivery/606 Fulfillment Template/UPLOAD/RRF_REPORTS/",[Delimiter=",", Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(#"Imported CSV", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"SOL - ID", type text}, {"SOL - Line Sequence", Int64.Type}, {"SOL - POB", Int64.Type}, {"SOL - End Date", type date}, {"SOL - Start Date", type date}, {"SOL - Qty Ordered", Int64.Type}, {"SOL - Product Name", type text}, {"SOL - Ratable Indicator", Int64.Type}, {"SOL - Description", type text}, {"SOL - OA Nbr", Int64.Type}, {"SOL - Manual Subsequent Indicator", Int64.Type}, {"SOL - Unit Type", type text}, {"SOL - Source DID", type text}, {"SOL_is_cancelled", type text}})
in
#"Changed Type"
Yes, it should. Each refresh will pull the max dated file.