Error when calling nested function within a table

[Anonymous]

Hey all,

Stuck in circles on calling a function within a table on each row. I have this function:

(groupid as number) =>
let
  JSONServer = "https://foo.example.com/api/jsonrpc",
  RequestContent1 = "{somejson which calls groupid}",
  Options1 = [Headers = [#"Content-Type" = "application/json"],Content = Text.ToBinary(RequestContent1)],
  ImportedJSON1 = Json.Document(Web.Contents(JSONServer, Options1)),
  result = Number.FromText(ImportedJSON1[result])
in
  result

I also have a table which just contains one column, with a number of IDs representing some groups. The API which returns items can only return results against a group ID. So what I've done is add a column to the Groups table, with an "each" function call to the count and passes the "groupid".

let
  s = Groups,
  Added = Table.AddColumn(s, "Items", each fnCountItemsbyGroup([groupid]), Int64.Type)
in
  Added

Unfortunately fails with this error:

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

Any suggestions what the issue is? Is it the fact that I'm calling a JSON request within a nested function?

[v-yuta-msft]

Hi oooo,

This issue should happen when you have nested a new data source in your custom function query. So everytime you call the function in other table query, the new data source will be imported one time which is forbidden by power query. You may sperate the custom function "fnCountItemsbyGroup" into two step queries:

In table query, you only need to connect to datasource

let
  JSONServer = "https://foo.example.com/api/jsonrpc",
  ImportedJSON1 = Json.Document(Web.Contents(JSONServer))
in
  ImportedJSON1

Then in custom function query, you can search your group id in the imported table and filter the table like pattern below:

(groupid as number) =>
let
  Source = ImportedJSON1,
  #"Filtered Rows" =Table.SelectRows(Source, each ([Group] = groupid)),
  #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows", {"result"}),
  result = Number.FromText(#"Removed Other Columns"[result])
in
  result

If this issue persists, please also refer to The Ken Puls Blog. 

Regards,

Jimmy Tao