Refresh fails on table with Append - Information is needed in order to combine data.

[AliPoTD]

Hi,

Please can I ask for your knowledgeable advice.

I have a report which was working fully uploaded to the Service and with scheduled refreshes set.

I have had to make some further additions to the report involving a new table which I have made in Power Query which includes the Append function.

It Appends data from an existing table with its data source being an Excel document saved on SharePoint. No new data is being fed into the report, just a new table made from combining 2 existing tables.

When I now upload the report to the service, I can't manually or schedule a refresh and am receiving the below error:

Data source error: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"Information is needed in order to combine data. Please specify a privacy level for each data source."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.DataSources","detail":{"type":1,"value":"[{\"kind\":\"File\",\"path\":\"xxxData.accdb\"},{\"kind\":\"Web\",\"path\":\"xxx\"}]"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.Reason","detail":{"type":1,"value":"PrivacySetting"}}],"exceptionCulprit":1}}} Table: TestTable.
Cluster URI: WABI-UK-SOUTH-C-PRIMARY-redirect.analysis.windows.net
Activity ID: 7e3e5914-fe79-4d79-8a39-331dafe44840
Request ID: 5f4b601f-a849-e3c6-ad3b-23f5742ef946
Time: 2024-09-05 11:20:36Z

I have tested using other existing tables in the report Appended to make a new table which works, but the issue occurs with a particular table which gets its data from Excel saved on Sharepoint so I'm pretty certain this is where the fault lies. 

In PBI desktop, Data Source Settings, I have set the Credentials for the SharePoint Excel data to being an Organizational Account with Privacy level set as Organizational.

On the Service -> Manage Connections and Gateway I have set the Authentication method as OAuth 2.0 and Privacy Level as Organizational.

I have played around with these settings but no luck so far. Can anyone explain why this is happening and how to resolve it please?

Many thanks in advance. 

[AliPoTD · Accepted solution]

Thank you everyone.

It was very strange as the privacy levels were set correctly, I believe.

I don't understand why it would behave this way - this was an existing report which worked correctly when published to the Service. The only change was to then merge an existing table (B) into existing table (A) and it fell over.

I have, however, managed to get it working by changing the way I was calling the data.

I had been using the Get Data -> Web option to call an excel file saved on SharePoint. I changed to using the Get Data -> SharePoint folder and then used the "= SharePoint.Contents(...) and voila, it worked, without needing to alter the Privacy levels. Can anyone explain why this would be? It's exactly the same file being called, just in a different way and yet it worked, hence why I don't understand why the Privacy settings would make a difference. I'd be grateful if anyone can explain so I understand in future. 

Many thanks.

[AliPoTD · Accepted solution]

Thank you everyone.

It was very strange as the privacy levels were set correctly, I believe.

I don't understand why it would behave this way - this was an existing report which worked correctly when published to the Service. The only change was to then merge an existing table (B) into existing table (A) and it fell over.

I have, however, managed to get it working by changing the way I was calling the data.

I had been using the Get Data -> Web option to call an excel file saved on SharePoint. I changed to using the Get Data -> SharePoint folder and then used the "= SharePoint.Contents(...) and voila, it worked, without needing to alter the Privacy levels. Can anyone explain why this would be? It's exactly the same file being called, just in a different way and yet it worked, hence why I don't understand why the Privacy settings would make a difference. I'd be grateful if anyone can explain so I understand in future. 

Many thanks.

[lbendlin · Accepted solution]

I had been using the Get Data -> Web option to call an excel file saved on SharePoint.

Very much not recommended as it will lead to authentication issues during service refresh.  Always use SharePoint Folder connector.

[v-jianpeng-msft]

Thank you lbendlin and AnkitKukreja

Hi, @AliPoTD 

I've found an error message similar to yours in a past error. An alternative solution is to set all your data source privacy levels to Public and then perform a refresh.

Understand Power BI Desktop privacy levels - Power BI | Microsoft Learn

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnkitKukreja]

Hi @AliPoTD 

Go to the File menu and select Options and settings > Options, Global section -->Data Load.

Under Data privacy, select Always combine data according to your Privacy Level settings.

Ensure that each data source in your queries has a privacy level set. If you have multiple sources (e.g., file and web), make sure their privacy levels are set correctly.

If you are combining data from Different Sources:

• If your query combines data from multiple sources, ensure that privacy levels are compatible for the data to be combined.
• You might need to adjust the privacy level settings to ensure that Power BI can combine data from these sources.

[lbendlin]

In your Power BI Desktop validate that the privacy levels for your sources are compatible.