Formula.Firewall: Query references other queries (calling api)

[Nadpco]

Hi

I have to call an API with the address below that requires a page number to be passed to.

(I'm doing it in Power Query in Excel 2019)

https://search.codal.ir/api/search/v2/q?PageNumber=[PageNo]&search=true&FromDate=[JDate]&ToDate=[JDa...

(JDate is a string representing Jalali Date, like "1401/01/31")

To do that, I did these steps:

1. I read JDate from a named range with this power query function:

GetValue:

let
    Source = (rangeName) => 
    Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]
in
    Source

2. I got total page numbers from calling API once:

PageNum:

let
    Source = Json.Document(Web.Contents("https://search.codal.ir/api/search/v2/q?search=true&FromDate=" & GetValue("JDate") & "&ToDate=" & GetValue("JDate"))),
    Page = Source[Page]
in
    Page

3. I made a function to process data on one page:

OnePage:

let
    Source = (Page as number) => 
let
    Source = Json.Document(Web.Contents("https://search.codal.ir/api/search/v2/q?PageNumber=" & Text.From(Page) & "&search=true&FromDate=" & GetValue("JDate") & "&ToDate=" & GetValue("JDate"))),
    Letters = Source[Letters],
    #"Converted to Table" = Table.FromList(Letters, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"TracingNo", "Symbol", "CompanyName", "Title", "PublishDateTime"}, {"TracingNo", "Symbol", "CompanyName", "Title", "PublishDateTime"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Column1",{"Symbol", "CompanyName", "Title", "PublishDateTime", "TracingNo"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Symbol", type text}, {"CompanyName", type text}, {"Title", type text}, {"PublishDateTime", type text}, {"TracingNo", Int64.Type}})
in
    #"Changed Type"
in
    Source

4. I made a list of page numbers from 1 to total page numbers:

PageList:

let
    Source = List.Numbers(1, PageNum),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "PageNum"}})
in
    #"Renamed Columns"

5. I made a reference to the query in step 4 and invoked the function from step 3:

DateData:

let
    Source = PageList,
    #"Invoked Custom Function" = Table.AddColumn(Source, "Data", each OnePage([PageNum])),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Errors", "Data", {"Symbol", "CompanyName", "Title", "PublishDateTime", "TracingNo"}, {"Symbol", "CompanyName", "Title", "PublishDateTime", "TracingNo"})
in
    #"Expanded Data"

This is where I get the error "Query references other queries" (in Invoked Custom Function)

I have read that in Excel PowerQuery you can go to:

File=>Options and Settings=>Options and set the Ignore Privacy Levels option.

but I'd rather not use it since it has to be set on every computer I use the excel in.

any help would be much appreciated

thanks in advance.

[Nadpco · Accepted solution]

Hi, I solved the problem myself by reading this post:

https://docs.microsoft.com/en-us/power-query/dataprivacyfirewall

I included steps 2 and 4 in the step 5 query and that solved the problem.

The bottom line is when this error occurs, either by combining or by turning one query into 2 the problem should be solved.