Forum Discussion
New PowerQuery reference error on older report: users get error, I can't replicate
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.
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/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.
McSarah we need to do some troubleshooting and eliminate possible causes:
- Are all users on same version of Excel as you? Any updates or not in the last 2-3 weeks?
- Are users using the same method to refresh data as you?
- Where is the report saved and how are you and users accessing?
- What is the data source and what credentials are required?
- 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?
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.