Webinar Followup: Be a Full Stack #PowerBI Jedi

In today’s post, I am sharing a follow-up to my webinar, that will allow you to refresh the queries, and enjoy the support of scheduled refresh on Power BI service.

During the webinar (you can watch it here, if you missed it), I created a custom function that paginates over multiple pages, and was asked if it supports scheduled refresh. The report that I shared, will not be refreshed, but keep reading to find out how to fix it.

Did you encounter the following error?

Formula.Firewall error... Please rebuild this data combination

The immediate solution is to set your report to ignore privacy levels. To do it, click File > Options & Settings > Options > Current File > Privacy, and check Ignore Privacy Levels… box.

I was asked in the webinar, if the custom function will support a schedule refresh. The original one will not.

To support scheduled refresh, follow these guidelines:

  1. Don’t ignore privacy levels, you will need this error, to guide your way through the solution.
  2. Consolidate your queries till the firewall errors are gone.
  3. Ensure that your custom functions are defined in the same query that accesses the external data sources that are needed for the end-result. Don’t use references to other queries, which access external sources.
  4. By consolidating all queries, you will avoid the Firewall error.

If you are not sure, what I mean, download the solution below, and compare the queries. To resolve firewall errors, follow Ken Puls’ article here, and Chris Webb’s here – Thank you Ken and Chris for sharing these techniques. It helped me dozen times.

Unfortunately, that is not sufficient to gain the scheduled refresh. You will also need to reconstruct Web.Contents, and use the RelativePath and Query arguments.

For example: Here is Web.Contents before:

  • And here is Web.Contents after the change:

Note, that this change is only needed in Web.Content lines inside the custom function implementation.

Here is the consolidated query, that will refresh without the firewall error, and will also support scheduled refresh on the service:

Here is the report on Power Bi service. Refreshed daily 🙂

You can download version 2.0, which supports scheduled refresh here.

Hope you found it useful.

This is not an ideal solution. Consolidating together multiple queries, prevents you from having clean and reusable solution. So, here is a plea for Microsoft (Feel free to share in the comments below, if and why you join me in this plea): Dear Microsoft, please don’t ask us to rebuild the data combinations.



    • Gil Raviv Post authorReply

      Not safer. This is a limitation by the engine to evaluate the combination and use Privacy Levels. When you consolidate it, the engine understands it better.

Leave a Reply