Forum Discussion
Formula.Firewall: Please rebuild this data combination.
Hi Brice_LE-LANN ,
Try writing the code in only one query. I had the same problem few weeks ago and it worked for me.
You can create a function to call the API, but handle the tables together.
Avoid code like:
myTable = Table.Distinct( Table.SelectColumns(otherTable, { "key", "filteringValue"})),or
myItemList = listingItems
It's passing the tables/list from others queries, write them together.
Hi camargos88
thanks for the reply, much appreciated.
I have tried to put it in one query but it gave me the same output.
let
//creating my list in one query:
myItemList = Table.SelectRows(Table.Distinct( Table.SelectColumns(otherTable, { "key", "filteringValue"})), each ([filteringValue] = "KEYWORD"))[key],
// function to do one call per item
FnGetOneItem = (itemId) as table =>
let
myKey = "xxx",
pageLimit = 2,
myPageSize= 2,
order = "-modificationDate",
HTTPHeader = [RelativePath = itemId & "/query", Query = [pageSize = Number.ToText(myPageSize), page = Number.ToText(pageId), sort = order], Headers = [#"Ocp-Apim-Subscription-Key" = apimKey ]],
Source = Json.Document(Web.Contents("https://url.com", HTTPHeader)),
values = try Source[values] otherwise null,
results = [Values=values]
in
results,
data = List.Generate(
() => [pageId=1, result = FnGetOnePage(pageId)],
each [pageId]<= pageLimit and [result][Values] <> {},
each [pageId=[pageId]+1, url=urlroot & Number.ToText(pageId), result = [result] & FnGetOnePage(pageId) ]
),
output = Table.FromRecords(data)
in output,
Source = List.Generate(
() => [position=0, storeId = myItemList{position}, outputTable = FnGetOneItem(myItemList{position})],
each [position] < List.Count(myItemList),
each [position=[position]+1, storeId = myItemList{position}, outputTable = FnGetOneItem(myItemList{position})]
),
Final = Table.FromRecords(Source)
in
Final Thank you again, and if you have any idea, I'll take it
Try changing this part:
myItemList = Table.SelectRows(Table.Distinct( Table.SelectColumns(otherTable, { "key", "filteringValue"})), each ([filteringValue] = "KEYWORD"))[key],It receives other table "otherTable", try querying this table in the same query as well.
I am sorry but I don't understand. By what should I replace it?
I need to query this table to get the list of items.
Thanks,
Brice
Do not refer it on the other query, copy the code that queries it from the source.
Try writing the query without dependencies.
You can use disable the parallel load tables for performance issues, so it will query just once.