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:
- Don’t ignore privacy levels, you will need this error, to guide your way through the solution.
- Consolidate your queries till the firewall errors are gone.
- 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.
- 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:
Web.Contents("https://swapi.co/api/people/?page=" & PageNumber)
- And here is Web.Contents after the change:
Web.Contents("https://swapi.co/", [RelativePath="api/people/", Query=[page=PageNumber]])
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:
let FnGetPeoplePage = (PageNumber as text) => let Source = Json.Document(Web.Contents("https://swapi.co/", [RelativePath="api/people/", Query=[page=PageNumber]])), results = Source[results], #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"name", "height", "mass", "gender", "species"}, {"name", "height", "mass", "gender", "species"}), #"Expanded species" = Table.ExpandListColumn(#"Expanded Column1", "species") in #"Expanded species", CountSource = Json.Document(Web.Contents("https://swapi.co/api/people/?page=1")), PeopleCount = CountSource [count], Source = List.Numbers(1, PeopleCount/10), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Page"}}), #"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "FnGetPage", each FnGetPeoplePage([Page])), #"Expanded FnGetPage" = Table.ExpandTableColumn(#"Invoked Custom Function", "FnGetPage", {"name", "height", "mass", "gender", "species"}, {"name", "height", "mass", "gender", "species"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded FnGetPage",{"Page"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"height", Int64.Type}, {"mass", Int64.Type}}), #"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type1", {"height", "mass"}), #"Divided Column" = Table.TransformColumns(#"Removed Errors", {{"height", each _ / 100, type number}}), #"Added Custom" = Table.AddColumn(#"Divided Column", "bmi - custom column", each [mass] / Number.Power([height],2)), #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"bmi - custom column", type number}}), #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Custom", each #"BMI Categories"), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Category", "From", "To"}, {"Category", "From", "To"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each [#"bmi - custom column"] >= [From] and ([To] = null or [#"bmi - custom column"] < [To])), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"From", "To"}), #"Replaced Value" = Table.ReplaceValue(#"Removed Columns1","none","droid",Replacer.ReplaceText,{"gender"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","n/a","droid",Replacer.ReplaceText,{"gender"}) in #"Replaced Value1"
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.
Why are consolidated data combinations safer?
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.
They are not safer. Just easier to read by the firewall component. When the queries are complex, and may dynamically refer to different data source, which the firewall module cannot understand, Microsoft tells you in nice words that they are not certain which datasets are used, and as a result, cannot prompt you to set the privacy levels, and the only mitigation is to ignore the privacy levels, or rewrite your code.
Hi Gil!
I’m trying to modify the original template of the JIRA content Pack to get more information about the worklogs. In one query as you said, I made all the process: Searchin of all issues and after that taking the id of every issue to make one query by issue to look for the worklogs.
But It didn’t work, when I try to refresh, shows me this error:
“[Unable to combine data] Section1/Datos/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination. Table: Datos.”
Any Idea?
I have been studying Power Query and to do so I referenced some of Gil Raviv’s blogs/vlogs on DataChant – Your next stop in mastering Power Query and Power BI.
The Webinar Followup: Be a Full Stack #PowerBI Jedi – DataChant page is specifically concerned with learning how to iteratively load multiple pages from a website.
While completing the project, I came upon the need to replace missing height and weight values set to null with the average height or weight assigned to that species/gender for a particular character.
My methodology for assigning these average heights/weights was to remove the characters with missing heights or weights, group the characters by species/gender, then calculate the average heights/weights. This was all done via M in the “Ht/Wt by Species/Gender” query.
Once the sub-table “Ht/Wt by Species/Gender” was created, I pulled the full character table back and hoped that I could just “replace values” null with the average height or weight. I didn’t know how to do that. That is my question; Is there a way to pull the average value into a null value in the full table?
I accomplished my objective by Cartesian product in the “All but no height or mass” query … which is not a viable option once my projects and tables become much larger, which they will.
Any advice or have you accomplished the objective without using a Cartesian product? I have to believe that there is a function that could be coded in M for this.