Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
PowerBiIssues
Frequent Visitor

Using the latest excel file of a sharepoint folder as a query breaks automatic refresh

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")))

 

 

2 ACCEPTED SOLUTIONS
SaiTejaTalasila
Super User
Super User

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 

View solution in original post

PowerBiIssues
Frequent Visitor

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...

View solution in original post

2 REPLIES 2
PowerBiIssues
Frequent Visitor

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...

SaiTejaTalasila
Super User
Super User

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 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.