New PowerQuery reference error on older report: users get error, I can't replicate

[McSarah]

Hi, this is actually an issue with PowerQuery inside an Excel report - hope this forum can help.

Several months ago, I created an Excel report that accesses and refreshes data from other Excel workbooks in the same directory. Users refresh the data when they need to bring in additional source Workbooks.

Until around two weeks ago, this report was refreshing correctly for users. Then, around two weeks ago, they began getting the following error when they attempt to refresh:

Query '[query name]' references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

However, I can still refresh the report with no errors, and I believe the data is coming through correctly. So, why would users get this error, but not me? And why would this error pop up recently, on a report with no changes? I am reluctant to dig in and rejig the data flow when I don't yet understand the reason for this problem.

[McSarah · Accepted solution]

Thanks for your reply. I think I may have found the answer over the weekend...or at least I hope so. It looks like the users may have made a change to the data source privacy level of one of the data sources, such that the sources don't match each other any more. I have not yet confirmed that this is the cause of their issue, but I have stepped through all the queries with another user, who was prompted to select privacy levels, and was then able to successfully refresh. As for your other questions:

• We are all on the same version of Excel, and we all received a scheduled update a few weeks ago. I believe this update might have caused the data source privacy prompts to appear. Not sure why this would happen but it seems to be true.
• We're all using the same data sources, and we're all refreshing them in the same way, from the same directory. The report in question is located in the same folder as the source reports, and users can definitely run an initial query that accesses the folder. So it's definitely not a permissions/ directory access issue.
• The query accesses a sheet in an excel workbook in the same folder as the report using parameters to specify the file path and workbook name, then applies a series of transforms to the sheet, including a merge with another data source. However, since the query runs for me and at least one other user, I think the issue is probably not with the query itself.

[PhilipTreacy · Accepted solution]

Hi @McSarah 

For what it is worth at this stage, that error message si form the Formula Firewall.  It is being generated because a query is accessing a data source and another query.

If the Privacy Levels were set and thigs were working, then yes, perhaps somePrivacy Levels were altered.

Further reading:

https://community.powerbi.com/t5/Desktop/Formula-Firewall-Error-in-combination-with-PowerQuery-funct...

https://community.powerbi.com/t5/Power-Query/Formula-Firewall-Error/m-p/1061155

https://www.poweredsolutions.co/2019/03/12/data-privacy-and-the-formula-firewall/ 

Cheers

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anonymous]

@McSarah 
You may refer to the mothod in this blog:
https://aforanalytic.com/power-bi-query-references-other-queries-or-steps-so-it-may-not-directly-acc...

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[AllisonKennedy]

@McSarah  we need to do some troubleshooting and eliminate possible causes: 

1. Are all users on same version of Excel as you? Any updates or not in the last 2-3 weeks?
2. Are users using the same method to refresh data as you?
3. Where is the report saved and how are you and users accessing?
4. What is the data source and what credentials are required?
5. Can you share the M code from advanced editor of the broken query, or at least some more details about what it accesses and any merge, append or other combinations of data sources involved in it?

[McSarah · Accepted solution]

Thanks for your reply. I think I may have found the answer over the weekend...or at least I hope so. It looks like the users may have made a change to the data source privacy level of one of the data sources, such that the sources don't match each other any more. I have not yet confirmed that this is the cause of their issue, but I have stepped through all the queries with another user, who was prompted to select privacy levels, and was then able to successfully refresh. As for your other questions:

• We are all on the same version of Excel, and we all received a scheduled update a few weeks ago. I believe this update might have caused the data source privacy prompts to appear. Not sure why this would happen but it seems to be true.
• We're all using the same data sources, and we're all refreshing them in the same way, from the same directory. The report in question is located in the same folder as the source reports, and users can definitely run an initial query that accesses the folder. So it's definitely not a permissions/ directory access issue.
• The query accesses a sheet in an excel workbook in the same folder as the report using parameters to specify the file path and workbook name, then applies a series of transforms to the sheet, including a merge with another data source. However, since the query runs for me and at least one other user, I think the issue is probably not with the query itself.