Forum Discussion
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.
18 Replies
Anonymous
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.
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,
I am also facing Issue #2 and Issue #3. Were you ever able to resolve this?
Thanks,
Amy Jo
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
Anonymous,
Thanks for your reply. This is very useful information.
Amy Jo
