Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
My report connects to a sharepoint folder where we put a new excel file in weekly. The query filters for the latest file in the folder and then grabs the data inside the excel file (see code below).
The issue is that when a new file is put in the folder, the automatic refresh in the power bi service fails. This happens because it's still trying to open the previous latest file after it got filtered out through "List.Max()". Weirdly enough, if I download the report and refresh in PBI desktop, the refresh is succesful with no issue. It looks like for whatever reason the service hardcodes the name of the file on upload but can remain dynamic in the desktop.
How can I workaround this issue?
let
Source = SharePoint.Files("https://sharepoint.com/sites/OURSITE", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "OUR/FOLDER/PATH")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", let latest = List.Max(#"Filtered Rows"[Date created]) in each [Date created] = latest),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows1", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
Solved! Go to Solution.
Hi @PowerBiIssues ,
Save your file name with a date at the end like progress_10/12/2024.If you were able save like then on power query you can apply a filter contains progress in next step split column by delimiter _ then make the date data to date and apply date desending and keep first row only.Then select table it gives the data inside that file.I have suggested the same to our users when they have the similar requirement.
I hope it will be helpful.
Thanks,
Sai Teja
I solved it by not filtering the file before I expend the data. So it opens up all files and then I filter based on the file date. It's not efficient at all since it loads the data of all files but at least it works now...
I solved it by not filtering the file before I expend the data. So it opens up all files and then I filter based on the file date. It's not efficient at all since it loads the data of all files but at least it works now...
Hi @PowerBiIssues ,
Save your file name with a date at the end like progress_10/12/2024.If you were able save like then on power query you can apply a filter contains progress in next step split column by delimiter _ then make the date data to date and apply date desending and keep first row only.Then select table it gives the data inside that file.I have suggested the same to our users when they have the similar requirement.
I hope it will be helpful.
Thanks,
Sai Teja
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
20 | |
18 | |
15 | |
13 |
User | Count |
---|---|
37 | |
24 | |
22 | |
18 | |
12 |