Formula.Firewall Error when Iterating through one table to create another

Thanks for your response Jimmy801 . 

Concerning your suggestion to include query for All_boards into this query, I would like to explain the actual scenario.

Query1. Call JIRA REST API to get a list of boards.

Query2. For each board ID in (Query1) iterate and get the list of sprints using JIRA REST API's

Query3. For each board ID, sprint ID combination (In table created in Query2) get the velocity per sprint using JIRA REST API's.

Query4. For each board ID, sprint ID combination (In table created in Query2) get burndown data per sprint using JIRA REST API's.

Given this scenario I need to persist (2) as a table to use it in Query 3 and 4 (and potentially more queries). So including Query 1 into Query 2 and potentially both Query 1 and Query 2 into Query 3 will mean 

- Unecessary REST API's call to the backend

- Duplication of code.

I would like to avoid both these.

Hello Anonymous 

as a last try ... 

let 
BoardIdTable = All_Boards,
GetAllSprintsInt = GetAllSprints,
AllSprintsAllBoards = Table.AddColumn(BoardIdTable , "ResultAllSprints", each GetAllSprintsInt ([board.id]))

could you post the complete code of your query. Maybe the firewall.error is caused from another step

Jimmy

Jimmy801 , I tried your suggestion but I still face the firewall error. 

I am posting the PQ code again (which includes your suggestion).

All_Boards PQ

let
EntitiesPerPage = 50,

GetJsonBoard = (StartAtIndex as number) =>
let RawData = Web.Contents(
"https://jira.mycompany.com",
[
RelativePath="/rest/agile/1.0/board",
Query=
[
startAt = Text.From (StartAtIndex * EntitiesPerPage)
]
]
),
Json = Json.Document(RawData)
in
Json,

GetPageBoard = (Index as number) as list =>
let Json = GetJsonBoard(Index),
Value = Json[values]
in Value,

GetAllBoards = () as list =>
let PageRange = { 0 .. 20 },
Pages = List.Transform(PageRange, each try{ GetPageBoard(_) } otherwise null),
AllPages = List.Union(Pages)
in
AllPages,

#"Converted to Board Table" = Table.FromList(GetAllBoards(), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Board Table", "Column1"),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "name", "type"}, {"board.id", "board.name", "board.type"}),
in
#"Expanded Column2"

All_Sprints_All_Boards PQ

let

EntitiesPerPage = 50,

GetJson = (BoardId as number, StartAtIndex as number) =>
let RawData = Web.Contents(
"https://jira.mycompany.com",
[
RelativePath="/rest/agile/1.0/board/" & Text.From(BoardId) & "/sprint",
Query=
[
startAt = Text.From (StartAtIndex * EntitiesPerPage)
]
]
),
Json = Json.Document(RawData)
in
Json,

GetPage = (BoardId as number, Index as number) as list =>
let Json = GetJson(BoardId, Index),
ValueList = Json[values]
in ValueList,

GetAllSprints = (BoardId as number) as list =>
let PageRange = { 0 .. 9 },
Pages = List.Transform(PageRange, each try{ GetPage(BoardId,_) } otherwise null),
AllPages = List.Union(Pages),
AllSprints = List.Union(AllPages)
in
AllSprints,

GetAllSprintsAllBoards = () as table =>
let BoardIdTable = All_Boards, GetAllSprintsInt = GetAllSprints, AllSprintsAllBoards = Table.AddColumn(BoardIdTable , "sprint", each GetAllSprintsInt ([board.id]))
in
AllSprintsAllBoards,

#"Converted to Table" = GetAllSprintsAllBoards(),
#"Expanded sprint" = Table.ExpandListColumn(#"Converted to Table", "sprint"),
#"Expanded sprint1" = Table.ExpandRecordColumn(#"Expanded sprint", "sprint", {"id", "state", "name", "startDate", "endDate"}, {"sprint.id", "sprint.state", "sprint.name", "sprint.startDate", "sprint.endDate"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded sprint1",{{"sprint.startDate", type datetimezone}, {"sprint.endDate", type datetimezone}, {"board.id", Int64.Type}, {"sprint.id", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([sprint.state] <> "future"))
in
#"Filtered Rows"

When I run PQ All_Sprints_All_Boards, I get the firewall error.

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

Hello Anonymous 

I've studied just now the firewall issue in your case.

The problem is that in one partition (Firewall function breaks your queries into pieces) you cannot reference another partition AND a data source. So the only solution here for me seems to be or to switch of the Firewall function or to put all your code in one query.

However, what you can do is to structure this one query with serverals let/in

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

suJimmy801, thanks for your post. 

You say "So the only solution here for me seems to be or to switch of the Firewall function"

How can the Firewall function be ignored ? I guess by setting File/Options & Settings/Options/Global/Privacy = “Always Ignore Privacy Level settings”  in Power BI desktop?

The issue is that with this setting Scheduled refresh for this query does not work (it fails with the same firewall error)

.

Jimmy801if I merge the queries, if a new query needs the All_Boards table, I will need to copy the code there again, isn't it? 

Hello Anonymous 

not forcidly. What you can do, create a central basic query. In the output you specify a record with all extracts you need. So for the handover to Power BI you create afterwards a new Query that just uses this syntax YourNewQueryName[Result]. For referencing only the AllBoardsPart use this syntax YourNewQueryName[Allboards]. Here the basic practical example.

let
    Quelle = 
    let
        test = {1,2,3}
    in 
        test,
    test = List.First(Quelle)

in
    [Result= test, AllBoards= Quelle]

What do you think of handling like that?

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

Jimmy801,I am not sure I understand your code. Can you modify the code to how how the table created by the All_Boards query can be used by the All_Boards_All_Sprints query without the firewall error ?