Changing PowerBI gateways from individual local files to one consolidated Sharepoint Folder

[ReegeChris]

For my job, I have currently built reports that are using locally stored excel files that are not all in the same folder. Some are stored on my desktop or their own respective folders. This causes me to have multiple gateway connections for each excel file which I don't wish to have. Instead I would like the gateway connection to be pulling from a single sharepoint folder with all the corresponding files.

I'm unsure how this process works and would like if someone could help me to solve it. My main concern is if it is in one sharepoint folder, how can I update each individual report to pull the approriate excel files it needs, while ignoring the rest, and also being updated through one gateway.

Any help wopuld be greatly appreciated!

[christinepayton]

Are you speaking of on-premise SharePoint or SharePoint Online? Online doesn't require a gateway at all, so if that's what you're using you could simply upload your files and swap out the paths that your reports are pointing to and then enter your credentials in the scheduled refresh settings. I did a video on how to swap sources from local to SharePoint Online here. 

Do your reports currently use the file connector or the folder connector? Swapping from file to folder would be a bit of a pain, but what you'd do is just apply a filter on the query on the file name and path to the single file and then "expand" the single file... it wouldn't be my preferred method though, I feel like using the folder connector on a single file is overkill. 

[ReegeChris]

Hey Christine, Thanks for the quick response.

To clarify, I am using sharepoint online so that the customer we are building the reports for has access to the excel files we created that helped us. 

I did check out your video and initally used that method as it was convenient. However, my boss would ideally want the gateway to utilize one folder versus me changing the individual source path in each query. They would rather not have the connections looking like this where it's pulling the path of each individual file (despite it working fine that way):

To be clear, I'm new to the gateway connections so I don't understand everything entirely but the way it is currently setup is that each gateway is being pulled from local file connectors (i.e in each report that i built, I pulled the individual files I needed to build the tables, rather then pulling an entire folder). 

This is where I'm stuck since each file is individually loaded and not through a folder. Using the folder connector on the single file does sound a bit overkill but is there no other work around to that? 

Is it not possible to create a single gateway to the sharepoint folder containing all of the files I used locally, even if they aren;t using a folder connection?

[christinepayton]

I've not tried doing that, it might work - the issue I have here is that you're just transferring the "mess" for the connections from one place to another. The SP folder connector has more overhead from a query perspective (they take a lot longer to run), and creates a lot of extra baggage in Power Query. I would only use it if I were trying to combine multiple files with the same structure for that reason. If they're worried about maintenance, if you take over the ownership of these with a service account and run the refresh under the service account, you will likely never have to touch them again unless something in the source breaks.