Example:
My SharePoint site root url is https://powerbipanama.sharepoint.com/, but all of my files are actually in another site that starts with https://powerbipanama.sharepoint.com/sites/externalsales/ URL.
In order to use the correct URL, we need to be in the folder of the data that we're trying to get and check the url that our browser shows. If it has the if it starts with the format of Error! Hyperlink reference not valid. address>/sites/<sitename>/ then we need to use that url, otherwise we use the much simpler Error! Hyperlink reference not valid. address> In my own case, I'll be using the https://powerbipanama.sharepoint.com/sites/externalsales url in order to connect to my site.
Reference:
https://powerbi.microsoft.com/sv-se/blog/combining-excel-files-hosted-on-a-sharepoint-folder/