Forum Discussion
Formula.Firewall Error when Iterating through one table to create another
I have a PowerQuery that is creating one table All_Boards using REST API calls to JIRA. The structure of the table is as follows:
| board.id | board.name |
| 100 | Team A |
| 101 | Team B |
| .. | .. |
| .. | .. |
Once this table is created, I need to iterate through each row in this table and query for all the sprints in each board using the board.id to create a new table All_Sprints_All_Boards. For this purpose I use the following PowerQuery code.
let
BoardIdList = { 0 .. Table.RowCount(All_Boards) -1 },
AllSprintsAllBoards = List.Transform(BoardIdList, each GetAllSprints(All_Boards{_}[board.id])),
I get the Firewall error when executing this code. On the other hand, if I hardcode the All_Boards table like shown below, the query works just fine.
All_Boards = #table(
type table
[
#"board.id"=number,
#"board.name"=text
],
{
{ 100, "Team A" },
{ 101, "Team B" }
...
}
),
The only way I can get around this is by setting the global Privacy level in Power BI Desktop to "Always ignore Privacy Level settings". With this change the Firewall error disappears during Power BI desktop refresh but crops up when I schedule a refresh for this dataset (same error “Formula.Firewall: Query references other queries, so it may not directly access a data source”).
I have tried setting the privacy level in Power BI service and also tried setting the data source permission to "Public" and could not succeed.
Any help would be appreciated.
Raghu
Hello Anonymous
the solution to your problem was that you have to put every code to access your API in one query. Means that some data access query have also be placed there. And there your question was If it would possible to reuse a part of bigbig query.
The solution I gave you is a realy basic version of how it could work. To explain it a bit further
in your big query you hava "let" and a "in". When you now have to include a data access query to this big query, do like that
YourQueryNameToBeIntegrated = //and here you put your query let ..... final= .... in finalnow to be able to steer how this query is access, you can modify your output of your bigbig query as a record like this
finalresultbigbigquery = .... in [FinalResult= finalresultbigbigquery, YourIntegratedoldQuery = YourQueryNameToBeIntegrated]this enables you to use your bigbig query in two ways.. meaning the final result AND your integrated old query (that accesses basic data). Just invoke the bigbigquery whether like bigbigquery[FinalResult] OR bigbigquery[YourIntegratedoldQuery].
With this scenario you can reuse the coded inputed in your big query.
Hope its clearer now
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
20 Replies
My best guess is that PQ is complaining about hitting the same external data twice. Once in the Table.RowCount and once in the GetAllSprints.
Functionally, I think the code below produces the same result and should not have a Firewall issue.
let Source = AllBoards, AllSprintsAllBoards = Table.AddColumn(Source, "Custom", each GetAllSprints([board.id]))[Custom] in AllSprintsAllBoardsI still have the same problem.
Formula.Firewall: Query 'AllSprintsAllBoards' (step 'Changed Type') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
Please note that I have some steps that operate on the [Custom] column to expand it, change type of some columns etc. The last one of them (Changed Type) is the one which flags the Formula.Firewall error.