Dynamic Data Source - Excel Spreadsheet

 To avoid this, I created a spreadsheet that users update that lists the filepath and file name for the most current version of each Excel.

That invites user errors.  Instead, have them place the "latest"  files in a specific sharepoint folder. Then in your Power BI you connect to that folder (static connection) and grab whatever files are in the folder.  That way you can refresh in the service _and_ don't need a gateway.

Hi elaine1217 ,

Incase if they are uploading all files to the same SharePoint then you can ask them to create the file name in specific format .It helps you to filter the files easily.example-<file name etc,..>_elaine.xlsx.

In transform data you can apply contains Elaine and filter file names and use split column name and you can further filter it based on requirement.

Thanks,

Sai Teja 

Hi elaine1217 ,

Thanks to SaiTejaTalasila  and lbendlin  for the quick reply and solution. Please allow me to offer other insights:

Please refer to the following documents to use RelativePath with Web.Contents():

Solved: Dynamic data sources aren't refreshed in the Power... - Microsoft Fabric Community

Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query 

https://tengkuma.vercel.app/blog/posts/conquering-urls-with-relativepath-and-query-in-power-bi

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.