Power BI Service report on SharePoint lists with multi-select relationships

Dear all,

we plan to document our projects on project specific SharePoint sites with risks, decisions, changes, scope statements and status reports in dedicated lists. The idea is to also cross-reference those items, i.e. for example a weekly status report (documented as a row in the list) could reference multiple decisions, risks or changes (rows from the other lists) via lookup columns.

In addition to this setup, we want to be able to report via Power BI / Power Query in Power BI Service on the data documented in the project specific SharePoint sites and their lists.

Now we are facing several challenges in this setup:

• dynamic data sources
  • the number of projects, and hence the number of SharePoint sites and their lists are dynamic and need to be queried using functions, to circumvent the dynamic data source problem in Power BI service, e.g. Web.Contents() with option RelativePath
•  extraction of multi-select references
  
  • using SharePoint.Tables() gives the IDs of the referenced items in the other list, but this method cannot be modified with option RelativePath to circumvent dynamic data sources issue
  • Web.Content() is able to use RelativePath option, however output for multi-select relationships doesn't seem to be complete
• getting identifiers for resolving references across different list
  
  • one option we identified is combination of List.Id property (GUID) and ListItem.Id (int32), however here we are not sure if these are unique identifiers
  • another option would be the field UniqueID {32-bit Hex} coming via SharePoint Search api call 
• data privacy levels/combination of different data sources
  
  • for resolving the relations between list (and related query output) lists would need to be referenced, which is not working in the Power BI service across different queries
  • However we think, that we can circumvent that by doing the/resolving the relations via DAX

So all in all i tried to combine several approaches to adress these requirements and also restrictions, however i am not sure anymore if the above outlined approach would be feasible at all. I hope that my description is not to confuse; in case anything is unclear or should be outlined in more detail please let me know.

Did anybody else try to address such requirements and succeeded?

Looking forward to any input for feedback :)

Kind regards

Petter