Working with Dynamic Excel File Names

[Anonymous]

I have an Excel file in SharePoint that I am getting data from. Peridocally I update the file by exporting it from a different system but the system always adds the date to the end of the file name. The Excel file has the same begining to the file name but always ends with a date. For example:

Sales_2021_02_28.xlsx

When I need to update my Power BI data I do to following steps.

1. Delete the file Sales.xlsx from SharePoint
2. Export the new file (example name Sales_2021_02_28.xlsx)
3. Change the name to Sales.xlsx
4. Put the file in the folder where the original file was located in SharePoint

I want to be able to skip step 3 above. I need Power BI to not care about the date in the file name. I want Power BI to use the file name that starts with "Sales". Can anyone helpe?

[AlexisOlson]

If I were doing this, I'd load the SharePoint folder that contains each sales file (all of them with no renaming) and either parse the file name to select the one with the most recent date or the one that was most recently created.

This way you'd only have to do steps 2 and 4.

[Anonymous]

This sounds like the direction I need to go. Do you have any guidance on how to parse for file names?

[AlexisOlson]

Check out the various text functions. You could create a calculated column that extracts just the date part and take the top file sorted by that column.

Text.BetweenDelimiters([FileName],"Sales_",".xlsx")

(This should return an empty string for files in the folder not of the form "Sales_*.xlsx".)

[ChrisMendoza]

While not directly a SharePoint solution maybe the pattern can provide you with some ideas. I used the technique described at https://www.youtube.com/watch?v=0NX-GctfZuU to connect a file stored in Box. Another idea is using Power Automate to delete the old file, and change the name of the new upload, actually I guess the logic would be when a new item is created > delete the old file > rename the new item.