Strange workaround for the error: Formula.Firewall: Query 'Query' (step 'Result') references other..

[igormacedo]

Hello,

I'm working with PowerBI Desktop and I did a report to execute TFS/VSTS workitem queries. I used the REST API, so I had to call one API to execute the query and retrieve the workitems IDs and, for each batch of IDs (200 at a time), I call another API to retrieve workitems details. You can download a sample here.

I created some functions an a Query that calls the functions like this:

let
    queryId = GetQueryId(CollectionUrl, QueryTeamProjectName, QueryPath),
    queryResult = ExecuteWIQ(CollectionUrl, QueryTeamProjectName, queryId),   

    fieldList = GetFields(queryResult),
    idList = GetWorkitemIds(queryResult), 

    //Control Parameters 
    countOfIds = List.Count(idList),
    ItensPerRequest = 200,
    WorkItemsTable = #table(type table [],{}),
    Result = GetAllWorkitems(CollectionUrl, idList, fieldList, 0, ItensPerRequest, countOfIds, WorkItemsTable)
in

    Result

When this code runs, I get this error:

Formula.Firewall: Query 'MyQuery' (step 'Result') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination

I build the functions exacly to avoid this kind of error, so I did not understand why I was getting it again. I did some debug, and I discovered that if I returned in the in clause a variable that was not in the last line of the let clause, the error disapears, and the code runs ok!!! Yes, very strange... so if I add an atributtion line of code, that don't means nothing, just to be the last line, and returns the line before it, the error desapears:

let
    queryId = GetQueryId(CollectionUrl, QueryTeamProjectName, QueryPath),
    queryResult = ExecuteWIQ(CollectionUrl, QueryTeamProjectName, queryId),   

    fieldList = GetFields(queryResult),
    idList = GetWorkitemIds(queryResult), 

    //Control Parameters 
    countOfIds = List.Count(idList),
    ItensPerRequest = 200,
    WorkItemsTable = #table(type table [],{}),
    Result = GetAllWorkitems(CollectionUrl, idList, fieldList, 0, ItensPerRequest, countOfIds, WorkItemsTable),
   
    //Workaround para erro => Formula.Firewall: Query 'MyQuery' (step 'Result') references other queries or steps, 
    //                        so it may not directly access a data source. Please rebuild this data combination
    workaround = 1
in

    Result

This is the version I used in the template you can download above.

Is this a Bug?? 

Regards,

Igor Macedo

[igormacedo]

Hello,

I'm working with PowerBI Desktop and I did a report to execute TFS/VSTS workitem queries. I used the REST API, so I had to call one API to execute the query and retrieve the workitems IDs and, for each batch of IDs (200 at a time), I call another API to retrieve workitems details. You can download a sample here.

I created some functions an a Query that calls the functions like this:

let
    queryId = GetQueryId(CollectionUrl, QueryTeamProjectName, QueryPath),
    queryResult = ExecuteWIQ(CollectionUrl, QueryTeamProjectName, queryId),   

    fieldList = GetFields(queryResult),
    idList = GetWorkitemIds(queryResult), 

    //Control Parameters 
    countOfIds = List.Count(idList),
    ItensPerRequest = 200,
    WorkItemsTable = #table(type table [],{}),
    Result = GetAllWorkitems(CollectionUrl, idList, fieldList, 0, ItensPerRequest, countOfIds, WorkItemsTable)
in

    Result

When this code runs, I get this error:

Formula.Firewall: Query 'MyQuery' (step 'Result') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination

I build the functions exacly to avoid this kind of error, so I did not understand why I was getting it again. I did some debug, and I discovered that if I returned in the in clause a variable that was not in the last line of the let clause, the error disapears, and the code runs ok!!! Yes, very strange... so if I add an atributtion line of code, that don't means nothing, just to be the last line, and returns the line before it, the error desapears:

let
    queryId = GetQueryId(CollectionUrl, QueryTeamProjectName, QueryPath),
    queryResult = ExecuteWIQ(CollectionUrl, QueryTeamProjectName, queryId),   

    fieldList = GetFields(queryResult),
    idList = GetWorkitemIds(queryResult), 

    //Control Parameters 
    countOfIds = List.Count(idList),
    ItensPerRequest = 200,
    WorkItemsTable = #table(type table [],{}),
    Result = GetAllWorkitems(CollectionUrl, idList, fieldList, 0, ItensPerRequest, countOfIds, WorkItemsTable),
   
    //Workaround para erro => Formula.Firewall: Query 'MyQuery' (step 'Result') references other queries or steps, 
    //                        so it may not directly access a data source. Please rebuild this data combination
    workaround = 1
in

    Result

This is the version I used in the template you can download above.

Is this a Bug?? 

Regards,

Igor Macedo

[igormacedo]

Hello,

I'm working with PowerBI Desktop and I did a report to execute TFS/VSTS workitem queries. I used the REST API, so I had to call one API to execute the query and retrieve the workitems IDs and, for each batch of IDs (200 at a time), I call another API to retrieve workitems details. You can download a sample here.

I created some functions an a Query that calls the functions like this:

let
    queryId = GetQueryId(CollectionUrl, QueryTeamProjectName, QueryPath),
    queryResult = ExecuteWIQ(CollectionUrl, QueryTeamProjectName, queryId),   

    fieldList = GetFields(queryResult),
    idList = GetWorkitemIds(queryResult), 

    //Control Parameters 
    countOfIds = List.Count(idList),
    ItensPerRequest = 200,
    WorkItemsTable = #table(type table [],{}),
    Result = GetAllWorkitems(CollectionUrl, idList, fieldList, 0, ItensPerRequest, countOfIds, WorkItemsTable)
in

    Result

When this code runs, I get this error:

Formula.Firewall: Query 'MyQuery' (step 'Result') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination

I build the functions exacly to avoid this kind of error, so I did not understand why I was getting it again. I did some debug, and I discovered that if I returned in the in clause a variable that was not in the last line of the let clause, the error disapears, and the code runs ok!!! Yes, very strange... so if I add an atributtion line of code, that don't means nothing, just to be the last line, and returns the line before it, the error desapears:

let
    queryId = GetQueryId(CollectionUrl, QueryTeamProjectName, QueryPath),
    queryResult = ExecuteWIQ(CollectionUrl, QueryTeamProjectName, queryId),   

    fieldList = GetFields(queryResult),
    idList = GetWorkitemIds(queryResult), 

    //Control Parameters 
    countOfIds = List.Count(idList),
    ItensPerRequest = 200,
    WorkItemsTable = #table(type table [],{}),
    Result = GetAllWorkitems(CollectionUrl, idList, fieldList, 0, ItensPerRequest, countOfIds, WorkItemsTable),
   
    //Workaround para erro => Formula.Firewall: Query 'MyQuery' (step 'Result') references other queries or steps, 
    //                        so it may not directly access a data source. Please rebuild this data combination
    workaround = 1
in

    Result

This is the version I used in the template you can download above.

Is this a Bug?? 

Regards,

Igor Macedo

[igormacedo]

Hello,

I'm working with PowerBI Desktop and I did a report to execute TFS/VSTS workitem queries. I used the REST API, so I had to call one API to execute the query and retrieve the workitems IDs and, for each batch of IDs (200 at a time), I call another API to retrieve workitems details. You can download a sample here.

I created some functions an a Query that calls the functions like this:

let
    queryId = GetQueryId(CollectionUrl, QueryTeamProjectName, QueryPath),
    queryResult = ExecuteWIQ(CollectionUrl, QueryTeamProjectName, queryId),   

    fieldList = GetFields(queryResult),
    idList = GetWorkitemIds(queryResult), 

    //Control Parameters 
    countOfIds = List.Count(idList),
    ItensPerRequest = 200,
    WorkItemsTable = #table(type table [],{}),
    Result = GetAllWorkitems(CollectionUrl, idList, fieldList, 0, ItensPerRequest, countOfIds, WorkItemsTable)
in

    Result

When this code runs, I get this error:

Formula.Firewall: Query 'MyQuery' (step 'Result') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination

I build the functions exacly to avoid this kind of error, so I did not understand why I was getting it again. I did some debug, and I discovered that if I returned in the in clause a variable that was not in the last line of the let clause, the error disapears, and the code runs ok!!! Yes, very strange... so if I add an atributtion line of code, that don't means nothing, just to be the last line, and returns the line before it, the error desapears:

let
    queryId = GetQueryId(CollectionUrl, QueryTeamProjectName, QueryPath),
    queryResult = ExecuteWIQ(CollectionUrl, QueryTeamProjectName, queryId),   

    fieldList = GetFields(queryResult),
    idList = GetWorkitemIds(queryResult), 

    //Control Parameters 
    countOfIds = List.Count(idList),
    ItensPerRequest = 200,
    WorkItemsTable = #table(type table [],{}),
    Result = GetAllWorkitems(CollectionUrl, idList, fieldList, 0, ItensPerRequest, countOfIds, WorkItemsTable),
   
    //Workaround para erro => Formula.Firewall: Query 'MyQuery' (step 'Result') references other queries or steps, 
    //                        so it may not directly access a data source. Please rebuild this data combination
    workaround = 1
in

    Result

This is the version I used in the template you can download above.

Is this a Bug?? 

Regards,

Igor Macedo

[v-qiuyu-msft]

Hi @igormacedo,

There is a new Power BI desktop version 2.47.4766.542 released, please try to test in this new version.

If issue persists, please enable the desktop trace log then repeat the steps to reproduce the issue, share .saz file here.

Best Regards,
Qiuyun Yu

[igormacedo]

@v-qiuyu-msft I downloaded the lasted version and the issue persists. I enabled the trace log, but I didn't find any .saz file there. Only .log files. Is any of them worth for you?