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

[PowerBiIssues]

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

[SaiTejaTalasila · Accepted 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 

[PowerBiIssues · Accepted solution]

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 · Accepted 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