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?
