Parameter source for SharePoint list file combine

[JasonC_XBI]

Please help. I have a need to load data from multiple SharePoint lists, and the number of different lists will increase over time (as projects are commissioned). my original plan was to use an excel table with the list of SharePoint sites, then invoke a custom function to perform the file combine from each list. While I can get this working as desired in desk top, once I publish the report I can't refresh due to an error with "Query1". I don't even have a Query1. From what I have been able to workout with Google, it's to do with the fact I am trying to connect to the data source without first authenticating. does anyone know how to get this to work in the Service?

[lbendlin]

Is the Excel table also on one of the sharepoints?  And yes, you're right about the authentication issues.

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-mashup-on-premises-cloud

[JasonC_XBI]

The excel file is also on SharePoint, but it is on a third site. 
excel - main company SharePoint 

list1 - SharePoint/sites/projectA/lists

list2 - SharePoint/sites/projectB/lists

i can't authenticate into the main company SharePoint root and navigate from there, as the project sites appear to be completely independent of the main site, despite the fact they share the same root name.
i.e. /sites/projectA doesn't appear to be a sub folder of SharePoint.com root.  

[lbendlin]

that's "normal".  A root url includes "/sites"  (or /"teams") and then the name of the actual sharepoint.

You don't really have a "main company sharepoint" - it's just like any other site.