Formula.Firewall: Query (step 'Invoked Custom Function') references other queries or steps....

[Coltella8013]

Hi

I am trying to build a paginated list of data from an API but am receiving the following:

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

I have the following Queries/Functions:

MDATPRecommendationGetPage

(cursor as text) =>
let
Source = Json.Document(Web.Contents("https://*************/api/1.1/obj/MDATP Recommendation?cursor=" & cursor, [Headers=[Authorization="Bearer *******************"]])),
response = Source[response],
results = response[results],
#"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"active_alert_boolean", "closed_boolean", "company_custom_company", "exposed_machine_count_number", "lastrefresh_date", "public_exploit_boolean", "reccommendations_text", "recommendation_category_text", "recommendation_name_text", "status_text", "total_machine_count_number", "weaknesses_number", "Created Date", "Created By", "Modified Date", "max_machine_count_number", "latestcve_date", "weaknessmaxscore_number", "_id"}, {"Column1.active_alert_boolean", "Column1.closed_boolean", "Column1.company_custom_company", "Column1.exposed_machine_count_number", "Column1.lastrefresh_date", "Column1.public_exploit_boolean", "Column1.reccommendations_text", "Column1.recommendation_category_text", "Column1.recommendation_name_text", "Column1.status_text", "Column1.total_machine_count_number", "Column1.weaknesses_number", "Column1.Created Date", "Column1.Created By", "Column1.Modified Date", "Column1.max_machine_count_number", "Column1.latestcve_date", "Column1.weaknessmaxscore_number", "Column1._id"})
in
#"Expanded Column1"

MDATPRecommendatiionTotalCount

let
Source = Json.Document(Web.Contents("https://***************/api/1.1/obj/MDATP Recommendation?cursor=1", [Headers=[Authorization="Bearer ******************"]])),
response = Source[response],
remaining = response[remaining]
in
remaining

MDATPRecommendationPages

let
MDATPRecommendationPages = List.Numbers(1, MDATPRecommendatiionTotalCount/100, 100),
#"Converted to Table" = Table.FromList(MDATPRecommendationPages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Data", each MDATPRecommendationsGetPage([Column1]))
in
#"Invoked Custom Function"

An example response from the API:

Can anyone see what i'm missing?

Much appreciated.

Nigel

[Anonymous]

Solution without turning off security, which will cause you issues on BI service:

https://community.fabric.microsoft.com/t5/Power-Query/Query-references-other-queries-or-steps-so-it-...

Gotta put all queries into one query, can't reference query outputs inside other queries it seems. 

[Coltella8013 · Accepted solution]

I have found a workaround here: Solved: Formula.Firewall: Query references other queries, ... - Microsoft Power BI Community

Answer was to set the privacy levels as per: File > Options, select Privacy, and if it's appropriate, select Ignore privay levels.