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

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

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
August Power BI Update Carousel

Power BI Monthly Update - August 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Kudoed Authors