Error When Refreshing Merged Data from a Public API with a SQL Query

[rbriga]

I would appriciate some help-

I'm getting an error while refreshing my model at the Service.

In PBI Desktop, the refresh works smoothly. In the service, I'm getting the error while merging a public API with a SQL Query (which connects through a gateway). It seems to be about privacy settings, but I've played with those without success.

The Error message for refreshing the table "API":

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"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.DataSources","detail":{"type":1,"value":"[{\"kind\":\"Web\",\"path\":\"https://status.liveperson.com/report/%7B%22params%22:%7B%22offset%22:0,%22limit%22:100000%7D,%22filt..."},{\"kind\":\"SQL\",\"path\":\"DWSVR-DBS01;LPSMART-ODS\"}]"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.Reason","detail":{"type":1,"value":"PrivacySetting"}}],"exceptionCulprit":1}}} Table: API.

Thank you

[rbriga]

Thank you both.

It appears changing the credentials of the API to Organizational (to match the other sources) at the PBI service solved it for me.

[ibarrau]

Hi. There is a policy about merging different data sources in PBI that is kind of weird for me. If you already have played with all the privacy levels without success, you can always try "ignore privacy level" option in the pbix settings of the file. File -> Options and Settings -> Options -> Current File / Privacy -> Ignore....

That's should fix it unless there is something else.

I hope that helps, 

[arvindsingh802 · Accepted solution]

As you merging the data from SQL and Web together, so you have to enable "Allow user's cloud data sources to refresh through this gateway cluster" option in Gateway setting. (Merge or append on-premises and cloud data sources - Power BI | Microsoft Docs)

If you taken care this already, check privacy level for below and keep all same

1. SQL query in power BI desktop

2. API query in Power BI desktop

3. SQL Data source in Power BI Gateway

If this post helps, then please consider Accept it as the solution and give it a thumbs up