Power Query’s Data Privacy Firewall

I spent a significant amount of time head-scratching about this same issue. I developed a masterful report locally with dynamic sources, code hotloading, and other cool tricks. It was refreshing just fine on my machine. I had privacy settings all set to none, and privacy checking turned off. Low and behold when I published to the service and tried refreshing there, I got all kinds of failures with error messages that didn't really tell me what was wrong. I spent days reading about formula firewalls both in blogs and the official MSFT documentation, as well as picking apart my powerquery to try and isolate the problems.

At the end, I had to make the hard decision to refactor how each query obtained possibly dynamic information. I had to refactor each query such that it could directly access information between multiple sources. I couldn't pre-load other sources in staging queries.

My solution was to create several user-defined power query functions which controlled details on how to access a data source and retrieve information from that source. Then, for each query, I called those functions to get the info from the source. My non-working version instead referenced a staging query that may have done the same. The working pattern ensures that each query has direct access to the static information about a given data source, and were not referencing other queries to get that information.

Another issue you may run into: do not create data source addresses (for example a URL) dynamically at run time. There are specific built-in functions that let you construct content retrieval queries against sites. You will find blogs about this on the net as well.

TLDR: don't confuse static analysis or PowerBI.com refresh will not work!