Forum Discussion
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
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.
6 Replies
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 Dinesh,
thank you very much for the reply. Confirmation about feasibility helped me developing a working pilot. Key is mainly
- working with functions using Web.Contents with RelativePath as parameter,
- splitting every relation up into a single M-query,
- using identical privacy levels across all levels (even though i avoided to do any joins)
- combining ListId and ListItemId to an new identifier (alternative to UniqueId, which i couldn't get everywhere)
- aggregating all relevant queries then via DAX into one table
Thank you again and kind regards,
Petter
Hi PetterR ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Hi @PetterR ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
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.
Hi Poojara,
thank you very much for your inputs.
Concerning central registry list we're working with both Microsoft Project Online as a central register of projects; in addition we are using SharePoint content types for the different lists above, enabling getting those via SharePoint Search API.
Thank you for your input and kind regards
Petter