Power BI Service refresh error when using custom M functions for REST API calls + on-prem gateway

[Element115]

Power Service and Power Query Team,

please find a way so that REST API calls from various custom functions within local queries calling Table.AddColumn(a_table_as_prev_query, new_col_name, each custom_fun(), col_type) stop causing the following 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/handleID_table/handleID 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\":\"Web\",\"path\":\"https://api.###.com/oauth/accesstoken\"}]"
					}
				},
				{
					"code": "Microsoft.Data.Mashup.MashupSecurityException.Reason",
					"detail": {
						"type": 1,
						"value": "PrivacyError"
					}
				}
			],
			"exceptionCulprit": 1
		}
	}
}

As we all know, there is little to no standardization amonsgt REST API developers.  Regardless, as things stand, this issue makes Power Query unusable for anything but the most simplistic and by-the-book scenarios when needing to retrieve data through more than 2 different API calls from the Power BI Service (dataset needs to be refreshed multiple times daily).

At the moment:  5 mins in Python and you're done getting the data.  With Power Query: 1 week and counting!   Totally unacceptable, of course. 

Part of the problem is that these kind of error messages are documented nowhere.  Thank God there was one consultant, Ken Puls, who blogged about this, or users would have walked away that this is totally impossible to do.  

Ken Puls solution works fine for 1 redirection layer. I need 3 and haven't finished testing (because the API requires waiting 5 to 10 mins before being able to query it again, if you can believe it! Talk about being dragged through Moria!) 

Please please please fix this. Surely there is another way at the Azure cloud level to perform the necessary security checks for tenants using on-prem gateways without blowing up their M code?

[Anonymous]

Hi @Element115 ,

You may consider to contact the support.

How to create a support ticket in Power BI - Microsoft Power BI Community

[Element115]

Initial testing succeeded.  I was able to bring in a data subset into Service and have it refresh no problem. But honestly, what a royal pain in the ass it was to get to this point.  I wish esoteric stuff like this would be advertised front and center when you hit the PQ documentation, because quite frankly, anyone starting out with Power BI will never imagine there is such a thing as a data privacy firewall.  We all just start plugging away as fast as we can with DAX and a little M, then more M but who has the time to dig deep into the documentation, especially the advanced topics? 

[Element115]

I will if all tests fail.  atm though this page explained why the issue exists in the first place:  https://learn.microsoft.com/en-us/power-query/dataprivacyfirewall

And thanks to other users saying that there can be no reference to external local queries from within the query where you call the REST API if you also combine data from other sources in that same query, I was able to get the Service to finally refresh without errors.  

HOWEVER, that is not say that PQ could not be improved so that the naive user wouldn't have to resort to such ugly code gymnastics while at the same time still preserving data privacy.