Forum Discussion
Formula.Firewall Error when Iterating through one table to create another
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
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.
Are you connecting to another table somewhere? Try posting your code (do not include anything senstive) so I could advise.
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_BoardsCode 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"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.