digest
Short Summary
Main recommendation: To fix Power Query's "Formula.Firewall" error, land the query that makes the external folder-path call as a separate Connection Only query (named "Blended Data" here, using Folder.Files), then reference that staging query in the query that caused the conflict. The video also shows a quicker but less ideal fix: set Power Query's privacy setting to "Always Ignore Privacy Settings." The error arises because two queries have conflicting privacy levels.
Key Points
- The error appears when refreshing "FolderManyBadTables," which uses a folder-path parameter query and a function query whose privacy settings conflict.
- Solution one: change the privacy setting to "Always Ignore Privacy Settings" under Query Options > Privacy, which removes the conflict but is described as probably not very good.
- Solution two (the preferred fix): move the external folder call into a separate staging query that returns the full table of files, so the main query has no external data call.
- The staging query is loaded as Connection Only and then used as the source (step one) of the query that previously threw the error.
- The video frames this staging approach as matching a diagram from Ken Pohl's article, which calls it a "staging query."
Details Worth Noting
- Concrete instructions:
- To try the quick fix: go to File > Options and Settings > Query Options > Privacy, choose "Always Ignore Privacy Settings," click OK, and refresh.
- To revert the quick fix: return to Options > Query Options > Privacy, select "Combine Data," set the default step, click OK, then refresh (the error returns).
- To build the staging fix: open Advanced Editor in the query that has the folder path, copy the step that uses Folder.Files, and add it in a new query (the video names this step Blended Data).
- Click Close and Load, choosing Connection Only for the new staging query.
- Reopen the original query, go to Advanced Editor, and set step one's Source equal to the output of the staging query (Blended Data), then Close and Load.
- Privacy levels mentioned are Private, Organizational, and Public, which describe whether data is visible to everyone, some people, or is private; the video links to a reference for these.
- The video notes that the staging query is allowed because it contains no external data call, so it does not trigger the firewall flag.
- The video is labeled as a follow-up to MSPTDA video 9, and the accompanying PDF notes have a new note about this error.
Bottom Line
The durable fix is structural: isolate the external folder call in its own Connection Only query and reference that query, rather than changing privacy settings globally.
