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

[Anonymous]

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

[Anonymous]

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
    AllSprintsAllBoards

[Anonymous]

I 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.

[Anonymous]

Are you connecting to another table somewhere? Try posting your code (do not include anything senstive) so I could advise.

[Anonymous]

I am posting the code below (please note small changes in names compared to what I posted earlier)

Code for 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"),
    All_Boards = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "self", "name", "type"}, {"board.id", "board.self", "board.name", "board.type"}),
in
    All_Boards

Code for 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, BoardName as text) as list =>
        let PageRange = { 0 .. 9 },
            Pages = List.Transform(PageRange, each try{ GetPage(BoardId,_) } otherwise null),
            AllPages = List.Union(Pages),
            AllSprints = List.Union(AllPages),
            SprintList = { 0 .. (List.Count(AllSprints)-1)},  
            SprintsWithBoardId = List.Transform(SprintList, each Record.AddField(AllSprints{_},"Board ID", BoardId)),
            SprintsWithBoardIdAndName = List.Transform(SprintList, each Record.AddField(SprintsWithBoardId{_},"Board Name", BoardName))
        in
            SprintsWithBoardIdAndName,

    GetAllSprintsAllBoards = () as list =>
        let Source = All_Boards,
            AllSprintsAllBoards = Table.AddColumn(Source, "Custom", each GetAllSprints([board.id],[board.name]))[Custom]
        in
            AllSprintsAllBoards,

    #"Converted to Table" = Table.FromList(GetAllSprintsAllBoards(), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"Column1", "sprint"}}),
    #"Expanded sprint" = Table.ExpandRecordColumn(#"Renamed Columns", "sprint", {"id", "self", "state", "name", "startDate", "endDate", "completeDate", "originBoardId", "Board ID", "Board Name", "goal"}, {"sprint.id", "sprint.self", "sprint.state", "sprint.name", "sprint.startDate", "sprint.endDate", "sprint.completeDate", "sprint.originBoardId", "sprint.Board ID", "sprint.Board Name", "sprint.goal"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded sprint",{{"sprint.Board ID", "Board ID"}, {"sprint.Board Name", "Board Name"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"sprint.startDate", type datetimezone}, {"sprint.endDate", type datetimezone}, {"sprint.completeDate", type datetimezone}, {"sprint.originBoardId", Int64.Type}, {"Board ID", Int64.Type}, {"sprint.id", Int64.Type}})
in
    #"Changed Type"

[Anonymous]

In All_Sprints_All_Boards, the code below references an external source.

 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,

The section below references all_boards and is most likely now triggering the firewall issue. In my experience, moving the code above into another query would fix the issue.

    GetAllSprintsAllBoards = () as list =>
        let Source = All_Boards,
            AllSprintsAllBoards = Table.AddColumn(Source, "Custom", each GetAllSprints([board.id],[board.name]))[Custom]
        in
            AllSprintsAllBoards,

Hope this helps.

[Anonymous]

I guess you wanted me to move GetJSon to  a separete query? I did that and still end up with the Firewall error.

[Anonymous]

Can somebody from Microsoft Power BI product team help? My requirement is really quite basic. I don't understand whay there is no solution to the problem.

[Jimmy801]

Hello

try the following approach

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

if this doesn't work out try to include the all_boards-query into this querry (or the getallsprints-function too)

All the best

Jimmy

[Anonymous]

@Jimmy801, Your solution is same as @Anonymous and does not work. 

[Jimmy801]

Hello @Anonymous 

I only saw it yet 🙂

did you try my other 2 suggestions to include the whole query of All_boards / or function into this main query?

Jimmy

[Anonymous]

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.

[Jimmy801]

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

[Anonymous]

@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. 

[Jimmy801]

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

[Anonymous]

su@Jimmy801, 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)

.

[Jimmy801]

Hello...

I meant solutionS...
One is ignoring the setting the other is to create only one query

🙂

Jimmy

[Anonymous]

@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? 

[Jimmy801]

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

[Anonymous]

@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 ?

[Jimmy801 · Accepted solution]

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

   final

now 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