Live connection/automated refresh to Sharepoint Excel file

I have a sharepoint site mapped to my local directory and a file in there is connected to a PBI report. I'm having trouble making the refresh more dynamic.

What I'd like to do:

1. Connect directly to the Excel file - PBI then refreshes either in real time or on a schedule
2. Less ideal, make a dynamic file path so any non-technical user can simply open PBI, click refresh, and publish. This can't be done now because my username is in the file path source.

What I've tried:

• Scheduling a refresh fails because it doesn't recognize my credentials (and also the file path is point at my mapped drive).
• Trying to add the file in via "Web" does not work as my credentials can't be authenticated with any option 

Any other options?