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

[Element115]

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 · Accepted solution]

Hi @Element115 ,

This can have several problems behind, check the documentation below with information about possible errors and examples:

Dealing with errors - Power Query | Microsoft Learn

Behind the scenes of the Data Privacy Firewall - Power Query | Microsoft Learn

Or please refer to the following similar posts and try these inside solutions:

Solved: POWER QUERY references other queries or steps - Microsoft Power BI Community

Solved: References other queries or steps, so it may not d... - Microsoft Power BI Community

Best Regards,

Stephen Tao

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

[Element115]

Thanks, Stephen!  Didn't know about the Data Privacy Firewall.  However, still would be great if Microsoft could put up a web page where we could plug in error codes such as 

				{
					"code": "DM_ErrorDetailNameCode_UnderlyingErrorCode",
					"detail": {
						"type": 1,
						"value": "-2147467259"
					}
				}

and get redirected to a page explaining the probable cause in more details than the JSON error message and with a link(s) to the specific page(s) of the documentation pertinent for the error code.  Just saying.