Forum Discussion
How to Connect to Latest Excel File in SharePoint Without Slow Refresh?
Hello,
I have a SharePoint folder where an Excel file is added each month for the previous month. For example:
- August CSAT scores summary.xlsx
- September CSAT scores summary.xlsx
- October CSAT scores summary.xlsx
In November, we use October's data, and so on.
In my Power BI report, I need to connect to the latest Excel file in that SharePoint folder so the connection updates automatically each month.
I managed to do this using Power Query, but the refresh takes about 15 minutes, which is too long. The reason is that I connect via Get Data > SharePoint Folder, which loads all files from the parent folder (over 200,000 rows), and then I filter down to the file I need. This large query causes the slow refresh.
Question:
Is there a faster way to connect directly to the specific folder or file so I can reduce refresh time?
Thank you in advance!
rkhidesh , This same logic should apply for sharepoint folder
https://wkrzywiec.medium.com/getting-data-from-the-latest-file-in-a-folder-using-power-query-51dfa4bff711
4 Replies
rkhidesh , This same logic should apply for sharepoint folder
https://wkrzywiec.medium.com/getting-data-from-the-latest-file-in-a-folder-using-power-query-51dfa4bff711Hello, creating a shortcut of the SharePoint folder to my Desktop solved the slow refresh time issue. Thank you!
Right-click latest Excel > Copy link > Extract file path
In Power BI: Get Data > Web > paste direct file URL
If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pandeHi rkhidesh ,
Try below three solutions.
1. Connect directly to the specific folder path using Web.Contents.
Go to the file in SharePoint → click Copy Link.
Remove everything after /Shared Documents/…/Folder/.
Use below Power Query .
let
LatestFile =
SharePoint.Files(
"https://company.sharepoint.com/sites/SiteName/",
[ApiVersion = 15]
),
Filtered =
Table.SelectRows(
LatestFile,
each [Folder Path] = "https://company.sharepoint.com/sites/SiteName/Shared Documents/CSAT Reports/"
and Text.EndsWith([Name], "CSAT scores summary.xlsx")
),
Sorted = Table.Sort(Filtered, {{"Date modified", Order.Descending}}),
Final = Table.FirstN(Sorted, 1),
Imported = Excel.Workbook(Final{0}[Content])
in
Imported
2. Use SharePoint API to filter the list BEFORE download.
Power Query using _api/web/GetFolderByServerRelativeUrl:
let
Source = SharePoint.Contents("https://company.sharepoint.com/sites/SiteName/"),
Folder = Source{[Name="Shared Documents"]}[Content],
CSATFolder = Folder{[Name="CSAT Reports"]}[Content],
Filtered = Table.SelectRows(CSATFolder, each Text.Contains([Name], "CSAT scores summary")),
Sorted = Table.Sort(Filtered, {{"Date modified", Order.Descending}}),
Latest = Table.FirstN(Sorted, 1),
Data = Excel.Workbook(Latest{0}[Content])
in
Data
3. If filenames follow a date pattern, generate the expected file name
Instead of listing anything, you can construct the URL for the expected latest file,
e.g. 2024-10 CSAT scores summary.xlsx
Power Query:
let
CurrentMonth = Date.Month(Date.AddMonths(Date.From(DateTime.LocalNow()), -1)),
CurrentYear = Date.Year(Date.AddMonths(Date.From(DateTime.LocalNow()), -1)),
FileName = Text.From(CurrentMonth) & " " & "CSAT scores summary.xlsx",
Url = "https://company.sharepoint.com/sites/Site/Shared Documents/CSAT Reports/" & FileName,
FileBinary = Web.Contents(Url),
Data = Excel.Workbook(FileBinary)
in
Data
If my response as resolved your issue please mark it as solution and give kudos.
