Get data from custom domain that points to a sharepoint site and load to power BI

[CMBLUE24]

I have two sites where i need to get excel data: one is a custom sharepoint site collection inside our organizational domain (i have to use our vpn to access the site). Second is from a standard sharrepoint site. However i dont know how i can easily get the data directly to load to pbi from the first site. How can i do that.

[christinepayton]

If it requires VPN, it's probably on-premise, meaning you'd need to use a gateway to schedule refresh on it. You should still be able to connect to it using the SharePoint connectors as you normally would, you would in the desktop app, you'd just need your VPN running when you do so. 

The technique depends on if you're trying to connect to individual distinct files or a bunch similar files with the same structure. The SharePoint folder connector is for the latter, for the former I prefer to connect to a local copy and then change the path to SharePoint in the query editor (here's a video on how to do that).