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

Hi PetterR ,

Thank you for reaching out to the Microsoft Community Forum.

Please try below architecture.

1. Ingest via SharePoint REST or Microsoft Graph, using Web.Contents with RelativePath so your dataset can refresh in the Power BI Service. Keep the base URL static https://<tenant>.sharepoint.com or https://graph.microsoft.com and vary only the relative path per site/list.

2. Pull the multi‑lookup values, expand the arrays, and produce edge/bridge tables like StatusReport_Decision, StatusReport_Risk. and relate them in the model. SharePoint REST returns multi‑lookup values as OData “collection” arrays like { "results": [1,2,3] } that you expand in M code.

3. Use stable identifiers end‑to‑end. List (GUID) + Item ID (int) or ListItemUniqueId (GUID). Microsoft Graph exposes these in the sharePointIds facet: listId, listItemId, and listItemUniqueId. These are stable identifiers you can store and use to resolve cross‑list relationships and to deduplicate.

4. Set data privacy levels to Organizational for all SPO/Graph sources. You can’t disable the privacy firewall in the Service, make sure sources are compatible and avoid sending data across different privacy zones.

Please refer below links.

Solved: Scheduled Refresh using Dynamic Data Source - Microsoft Fabric Community

SharePointIds - Microsoft Graph v1.0 | Microsoft Learn

Privacy levels in Power Query - Power Query | Microsoft Learn

Working with lists and list items with REST | Microsoft Learn

SharePoint.Tables - PowerQuery M | Microsoft Learn

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

Hi PetterR 

Yes, this type of setup — dynamically reporting on multiple SharePoint project sites with cross-referenced lists — is possible but indeed complex due to the limitations of dynamic data sources and data privacy isolation in Power BI Service. The main challenge is that Power BI treats each SharePoint site as a separate data source, and the service blocks dynamically constructed URLs for security reasons. The typical workaround is to use functions with Web.Contents() and the RelativePath option, which allows dynamic referencing while still being refreshable in the service. However, this becomes tricky when dealing with lookup and multi-select fields, since SharePoint’s REST API doesn’t always return the complete relationship data in one call. In practice, many organizations that faced similar needs succeeded by standardizing their list structures across sites and maintaining a central registry list (or a SharePoint Search API call) that tracks all project site URLs and metadata. From there, Power Query can loop through the registry and retrieve each list using REST calls. For cross-list references, it’s best to extract both the List ID (GUID) and List Item ID, since their combination uniquely identifies a record even across sites. The UniqueID (hex) is also globally unique but harder to use for joins. To avoid privacy level conflicts, some teams perform all list merges and relationships in Power BI’s data model (DAX) rather than in Power Query. In summary, your proposed approach is feasible — but it requires strict structure standardization, a site registry, and careful handling of privacy and dynamic source restrictions. Others have implemented similar architectures successfully using these exact techniques.