Unable to combine data - Please rebuild this data combination - Refresh

Hi Wouter

I'm getting the same error message that you solved, where I'm using a WebAPI with a changing parameter as a data source to combine data. It works fine in desktop, but when trying to refresh the Power BI model in service it fails with the message: 

[Unable to combine data] Section1/CombinedVisitorQuery/Added Custom2 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Table: MergedVisitors.

On your solution:

"The issue was not using the parameter. The issue was that the parameter had a reference to a query (Suggested values). Changed the suggested values to 'any value'. Now refresh in the powerbu service works perfectly."

Where do you change the suggested values to 'any value'? I'm not following this logic. Do you perhaps have a screenshot of what you're doing here?

Thanks in advance. 

Laurie

Hey all,

here's my solution to the problem in case someone still ends up reading this thread, struggling with the Firewall:

The solution is based on Behind the Scenes of the Data Privacy Firewall and has two parts to it:

1. Put everything into a single Power Query M expression (Step).
   Go to Power Query Editor, right click the query, select Advanced Editor, then make sure to re-write your Power Query M expressions to access all data sources in a single Step, without any references to other steps or queries. See the example below for more details.
2. Set Privacy Level to Organizational (or Public) for each data source.
   From Power Query Editor, go to "File -> Options and settings -> Data source settings -> Data sources in current file", select each data source in turn and click on "Edit Permissions..." button, then select Organizational (or Public) for the Privacy Level and confirm with OK. Must have the same Privacy Level for each data source and it must be either Organizational or Public.

Here's an example to demonstrate the idea:

• First select a parameter value from one data source (simplified here to just select a constant)
• Then use the resulting value to build a query executed on another data source

First let's see the version that works well in Power BI Desktop, but does not work when published to a Power BI Service workspace:

let
    Count = Number.ToText(Record.Field(Table.SingleRow(
        Sql.Database("myserver1.database.windows.net", "mydb1",
            [Query="select 10 as Count"])), "Count")),
    Result = Sql.Database("myserver2.database.windows.net", "mydb2",
            [Query="select top " & Count & " * from sys.objects order by create_date desc"])
in
    Result

It does not matter here whether I had the Count as a separate query, or as a separate step. Either way, it ends up in a different "partition" and gets blocked by the Firewall.

Now let's see the version that works when published to a Power BI Service workspace:

let
    Result = Sql.Database("myserver2.database.windows.net", "mydb2",
        [Query="select top " & 
            Number.ToText(Record.Field(Table.SingleRow(
                Sql.Database("myserver1.database.windows.net", "mydb1",
                    [Query="select 10 as Count"])), "Count"))
        & " * from sys.objects order by create_date desc"])
in
    Result

Now that everything is done in a single Step (named Result here), the Firewall will not complain as long as both data sources have the same Privacy Level (either Organizational or Public).

Hope this helps, cheers!