Need help regarding Workspace refresh issue which merging 2 source tables.

[Bansi008]

Hi there – I have created one dashboard in which source of the data is SQL, but this SQL query is not hard coded. What I have done is, I have created an excel file with list of inscope accounts and using this excel file source, I have created a new table which concatenates the list of inscope accounts in the required format to pass through SQL query (refer screenshot 1). Now this new table (ACCT_LIST) is used in SQL query instead of account numbers so that query will only extract the data for a list of accounts which are available in the inscope list (refer screenshot 2). This approach feels more user-friendly to me than editing source SQL query every time there is update required in the inscope accounts. However, now the issue is that I can refresh the report from the desktop application with no issue but am not able to refresh the same from the PowerBI workspace. Can someone please guide me on how I could resolve this issue or if any different approach is available so that report will get refreshed at the workspace as well? 

Below is the issue description :-
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":"[Unable to combine data] Section1/FI_Dan_Sec_List/Renamed Columns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.DataSources","detail":{"type":1,"value":"[{\"kind\":\"SQL\",\"path\":\"RISKFI-DB-PROD;MASTER\"}]"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.Reason","detail":{"type":1,"value":"PrivacyError"}}],"exceptionCulprit":1}}} Table: FI_Dan_Sec_List.

• Cluster URIWABI-WEST-US-E-PRIMARY-redirect.analysis.windows.net
• Activity IDd7013097-58aa-4e79-b908-69fe4d893a83
• Request IDdf96c039-18cb-e737-2dd5-e12cb47a39f7
• Time2025-03-04 10:41:26Z
  
  
  
  

[Bansi008]

Thank you everyone for your response. 

[HarishKM]

@Bansi008 Hey,
Follow below step 

Solution: Go to File > Options and Settings > Options in Power BI Desktop (you can later publish to the Power BI Service). Then, under Global > Privacy, check the privacy level of the data sources involved (like SQL and others). Make sure that the privacy levels allow for data combination:

• Private: Restricts combining data with other sources.

• Public: Allows combining data freely.

• Organizational: Allows combining data within the organization.

Ensure that the privacy level is set appropriately for all the data sources involved.

and 
Update your data source credential on Power bi service under report dataset setting - update data source credential.

Thanks
Harish M
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[v-pgoloju]

Hi @Bansi008,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution — it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

[Bansi008 · Accepted solution]

@v-pgoloju - Hi I did some amendments in my SQL query and that is working as expected at my end

[v-pgoloju]

Hi @Bansi008,

Great to hear that it's working as expected on your end! Could you please share the solution? It would be really helpful for others in the community who might be facing similar issues and can address them quickly. Also, I would suggest accepting your approach as the solution so that it can benefit others as well.

Thanks & Regards,

Prasanna kumar

[v-pgoloju]

Hi @Bansi008,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,

Prasanna Kumar

[Deku]

You need to look at privacy levels 

[Bansi008]

@Deku -- There is no issue of gateways. all the sources are mapped to organizational gateways, however still refresh fails. when I use normal SQL query (without merging acct id's from inscope list) it works fine over workspace mapping to same gateway. This is not gateway issue but this is something PowerBI workspace doesn't allow combine two data sources together. 

[Deku]

You need a personal or on prem data gateway  so the power bi service has access to your excel spreadsheet