Forum Discussion
Error when loading SharePoint List merged with OnPremises data
Hi all
Today I had a bizarre error.
I have a Power BI file that loads multiple tables from an on-premises SQL Server and two SharePoint Online Lists.
Data from the SharePoint Online lists is merged with data from the SQL Server DB.
I do this for two tables.
I do not load the SharePoint tables into Power BI, as I only need the SharePoint data to add columns to the SQL data.
All goes well when refreshing the data in Power BI Desktop.
But after publishing, it is no longer possible to refresh the data.
This is the error:
I went step by step, first removing the Import from the SharePoint lists, then adding them back, and rebuilding the Load in Power Query.
After each step, I published the report and refreshed the data until the error appeared again.
The step that caused the error was the Merge between the SQL and the SharePoint Data.
As soon as I merged them, the refresh failed.
I tried to:
- Set the parallel loading of the table to one
- Changed the Privacy Level of the SharePoint data to Public while setting the SQL data as Organizational
Nothing helped.
In the end, I loaded the SharePoint data into the data model, added Relationships, and worked with the tables.
Now it works, but I have unnecessary tables in the data model, which I want to avoid.
Has anyone already encountered this issue?
If yes, is there a solution?
Kind regards
Salvatore
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.
5 Replies
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.
