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:

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.