cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sivapratap Helper II
Helper II

Formula.Firewall: Query references other queries or steps

Hi Team,

 

Hope everyone is doing well!

 

Its been 2 months i have started using Power BI, but in the recent times i am hitting many road blocks while working with SharePoint data in Power BI desktop.. I am listing the issue i am facing on a very frequent basis. 

 

Issue #1: Formula.Firewall: Query references other queries or steps, so it may not directly access a data source. Please rebuild this data combination

 

Description:  I have two tables A and B which are directly accessed from datasource(sharepoint Online List). I am building the third one using Merge Queries as New (Inner Join). It gets successfully build and i can publish the same onto power bi online. Everything seems to be ok till this level. But when i open the same pbix file and click on  refresh, it fails with this problem. If power is proactively catching this issue, then y the hell are we given a option to build Merge query as new. Please advise.

 

Issue #2:  From past 1 week, my data refresh fails with the error "
Data source error: SharePoint: Request failed: The remote server returned an error: (429). (429 TOO MANY REQUESTS). The exception was raised by the IDataReader interface.
Cluster URI: WABI-NORTH-EUROPE-redirect.analysis.windows.net
Activity ID: 29b8a394-2a48-e36f-d222-0b30f6a44e5a
Request ID: 075325d6-c4dd-6c5f-f529-092adef3e127
Time: 2017-03-29 11:40:00Z "

 

Description: The account with which i am publishing the datasource is a pro account, still it fails with the above error. Very strange right

 

Issue 3: My data refresh fails with one more error "Processing error: The credentials provided for the SharePoint source are invalid. (Source at https://abc.com/sites/xyz)
Cluster URI: WABI-NORTH-EUROPE-redirect.analysis.windows.net
Activity ID: d2e62740-7b91-4c34-965c-5e2a12e11ae7
Request ID: 6afebd7c-354f-42a7-9948-ee5a28b14773
Time: 2017-03-29 10:42:46Z
"

 

Description: its the same credentials with which i am publishing the data set and scheduling the data refresh. stil it throws the above error. When i update it again using Oauth2, it says the credentials updated. but when i trigger on demand refresh it fails again with 429 bad request or some other stuff. Also, the account has full access on Sharepoint online site.This is seriously annoying

 

 

Pleased advise on how to handle these issues as our reports went live and its very annoying for the users to report why there is a warning symbbol next to data set.

 

 

 

sivaaprataap
18 REPLIES 18
Microsoft
Microsoft

Re: Formula.Firewall: Query references other queries or steps

@sivapratap

 

Issue #1: Formula.Firewall: Query references other queries or steps, so it may not directly access a data source. Please rebuild this data combination

 

Please try to ingore the Privacy in either Global or Current File setting under Option.

5.PNG

 

If it still doesn't work, I suggest you have your third query reference your existing query to do merge. Please refer to blog below:

 

Power Query Errors: Please Rebuild This Data Combination

 

 

For the second issue,

 

Data source error: SharePoint: Request failed: The remote server returned an error: (429).

 

I can't reproduce it. It might the issue with the Cluster server, you may try to refresh later.

 

Regards,

AJZoerner
Frequent Visitor

Re: Formula.Firewall: Query references other queries or steps

I am also facing Issue #2 and Issue #3. Were you ever able to resolve this?

 

Thanks,

Amy Jo

sivapratap Helper II
Helper II

Re: Formula.Firewall: Query references other queries or steps

HI Amy,

 

Microsoft claims it to be a know issue with SharePoint API for Power BI. The way API for power bi works is when ever the schedule refresh triggers, power bi runs query on each and every record against the datasource. so, if you have 2000 records, it will 2000 times the queries will be run against the datasource.

 

In the current implementation of Power BI, for any of the cloud hosted serverices like SharePoint Online, CRM tools like Salesforce the schedule refresh happens through token. So the current expiry of token is 1 hour. so if the sharepoint data is huge and if the dataset takes more than 1 hour to refresh, it will result in token expiry and we end up in above errors.

 

In future , this token time might increase to 2 hrs. but what if the sharepoint data size increases, even microsoft doesn't have a clue. so there are few workarounds to optimize the refresh time which i have published . Please try and let me know if this helps you

 

https://community.powerbi.com/t5/Desktop/Optimization-of-Queries-with-SharePoint-Online-List-as-Data...

 

 

 

 

sivaaprataap
AJZoerner
Frequent Visitor

Re: Formula.Firewall: Query references other queries or steps

@sivapratap,

 

Thanks for your reply. This is very useful information.

 

Amy Jo

Anonymous
Not applicable

Re: Formula.Firewall: Query references other queries or steps

Hi everyone!

I'm having similar error as you Issue #1.
[Unable to combine data] Section1/Consulta1/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

 

In my case I'm trying to conect to JIRA:

Query1 -> Connects to JIRA to get a list of issues.

Query2 -> Take the list generated in Query1 and connects to JIRA to look for info of every issue.

 

I've tried the link way above, I change the Privacy and gives me the same error.

Any other way?

Thanks!

 

 

sivapratap Helper II
Helper II

Re: Formula.Firewall: Query references other queries or steps

HI,

 

In your queries , are you using "Merge Queries as New" by any chance ?

 

Is it possible to rebuild the data model by using relationships instead of creating a new query ? 

 

 

sivaaprataap
Anonymous
Not applicable

Re: Formula.Firewall: Query references other queries or steps

Hi @sivapratap!

No, I'm not using any kind of Merge.
I think that It's not possible ussing relations. It´s needed a JSON query for every issue to bring the info.

I'm modifying the JIRA content pack in which, first of all uses a instruction like this to bring the list of issues:

let
    FetchPage = (url as text, pageSize as number, skipRows as number) as table =>
    let
        contents = Web.Contents(URL,[RelativePath = "rest/api/2/search",Query = [maxResults= Text.From(pageSize), startAt = Text.From(skipRows)]]),
        json = Json.Document(contents),
        Value = json[issues],
        table = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
        table meta [skipRows = skipRows + pageSize, total = 500]
in
    FetchPage

Once I have the list of all id issues, I need to do a query for every issue. Something like this:

 

let
    SearchIssue = () =>
    let 
        Source = FetchPages("", 500),
        table = Table.ExpandRecordColumn(Source, "Column1", {"id"}, {"id"}),
        Search = (id) => 
        let
            Issue = Json.Document(Web.Contents(URL,[RelativePath="rest/api/2/issue/"&id]))
        in
            Issue,
        Output = Table.AddColumn(table, "incidencia",each Search([id])) 
    in
        Output  
in
    SearchIssue

 

I don't find the solution.

 

Highlighted
sivapratap Helper II
Helper II

Re: Formula.Firewall: Query references other queries or steps

HI,

 

I believe you are facing this issue with Power BI Desktop. Can you please try by un-check the "Parellel loading of Tables" and re run the refresh, hope it might avoid this issue.

 

Regards,

Sivapratap. 

sivaaprataap
Anonymous
Not applicable

Re: Formula.Firewall: Query references other queries or steps

Hi @sivapratap,

 

I have un-checked the "Parellel loading of Tables" and the online refresh gives me the same error:

[Unable to combine data] Section1/Datos/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

 

The refresh works on my desktop but online It doesn't work.
Thanks for the answer!

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors