Forum Discussion
Error when loading SharePoint List merged with OnPremises data
When you merge SQL + SharePoint in the same query, the Service evaluates this as cross-source data combination, and the mashup engine blocks it unless the privacy rules line up perfectly.
Option 1: Keep SharePoint loaded, hide it
What you already did, but clean it up:
Load the SharePoint list
Create relationships
Hide the SharePoint table from report view
Optionally move it to a “_Helper” display folder
Option 2: Use a Dataflow (clean architecture)
Ingest SharePoint + SQL in a Dataflow
Do the merge there
Consume the single output table in Power BI
This avoids Service mashup issues entirely.
Thank you for your answer.
At first, I set the Privacy level for both to Organizational.
But it didn't work either.
I set SharePoint to Public to enforce the Mashup Engine to load the entire dataset and do the Merge locally, which is how it is now.
What I don't understand is why it blocks the Merge when it should be able to merge the data locally.
By my understanding, the Mashup engine should be able to work locally with the data if something doesn't align with the Privacy levels.
It seems that the Mashup engine in the cloud acts differently from the engine in Desktop.
Anyway, I set the SharePoint tables to hidden, as you suggested. Next, we will look at how to do it better.
Kind regards
Salvatore
Hi SalvaC ,
Power BI Desktop is intentionally more flexible during development and can locally buffer or materialize data, which allows cross source merges to succeed even when privacy alignment isn't perfect, especially if privacy checks are relaxed. That flexibility doesn't exist in the Power BI Service, where refresh runs in a secure, multi tenant environment and the privacy firewall is enforced much more strictly to prevent unintended data leakage. Because of this, even when both sources are set to Organizational, certain cross-source operations such as materializing data during a merge are blocked by design, which explains why the behavior differs from Desktop. Setting a source like SharePoint to Public can force the merge to work, but this should be treated as a workaround rather than a recommended production approach. For long term stability, keeping SharePoint data loaded separately with relationships and hiding helper tables or moving the merge logic into a Dataflow remains the most reliable and supported pattern.
Thank you for the explanation, v-sshirivolu
This means that the current implementation, which loads both tables in Power BI with relationships between the tables, is correct.
Thank you again.
Regards
Salvatore
Hi SalvaC ,
Yes, that’s correct.
Loading the SQL and SharePoint data as separate tables and using relationships between them is the supported and recommended approach in the Power BI Service.
This avoids cross source mashup issues that occur when attempting to merge the sources in a single query.