Formula.Firewall I

[Anonymous]

I have an OData query, Stg_ProjectSiteDatabase, that extracts rows from a SharePoint online database, including URLs for subsites. I have a second query that is attempting to add a column, looping through the rows of Stg_ProjectSiteDatabase, calling an OData function to get SharePoint online list ("Issues") data for each subsite using the siteUrl field. However, despite splitting the queries per this article, I am still getting this error:

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

The function 

Here's the second query:

let
    Sites = Stg_ProjectSiteDatabase,
    #"Invoked Custom Function" = Table.AddColumn(Sites, "IssuesTable", each GetSPOnlineProjectIssues([SiteURL]))
in
    #"Invoked Custom Function"

I haven't found a solution for this. I tried putting the Stg_ProjectSiteDatabase data in a List, then looping through it to call the function, but same issue.

[blopez11]

I've run into this so many times, especially when using web service data sources

The way I have got around this is by encapsulating the staging query in a function and having the subsequent query call the function instead of referencing the staging query

Also make sure your data sources have the correct privacy settings

Hope this helps

[Anonymous]

Thank you for the suggestion, I will try encapsulating the staging query in a function.

Is there a specific value needed for the data sourc privacy setting, or does it simply need to be consistent across data sources? In this case, I only have one data source.

[blopez11 · Accepted solution]

They just need to be set to where if you are using multiple data sources, queries combining between them have the appropriate rights

Yes, the encapsulation should work in your case, and this article helped my find that solution: https://www.poweredsolutions.co/2019/03/12/data-privacy-and-the-formula-firewall/

Thanks,

[Anonymous]

Thank you so much, that worked!