Power BI – Issue in using Query List Parameter (Query references other queries or steps)

I had a business requirement where I need to get the list of Products Ids in scope from an Excel file provided by the business and use those Product ID’s to get some data from SQL Server for reporting. It sounds like a simple requirement correct. But it turned out to be a nightmare for me.

So I loaded the Product Ids from Excel file and converted those into a list. Then I created a parameter based on that list of ProductIds.

 

In the next step, I am passing this parameter as a query parameter in my Query.

 

 

When I tried to run this Query, I was getting below error message.

Formula.Firewall: Query ‘ProductDetails’ (step ‘Source’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

 

 

This error happens because you are trying to access data from two different Datasets (In my case, Excel and SQL Server). I was able to fix this issue by setting Privacy Levels under File > Options and Settings > Options > Privacy.

Please note that you should not set ‘Always ignore Privacy Level Settings” if you have sensitive data. You can read more about Privacy levels here.

 

 

So everything was working fine and I was able to complete my report design and published my report to Workspace. But the report refresh started failing with the below error message.

Underlying error message[Unable to combine data] Section1/ProductDetails/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

 

 

Looking at the error message, we can understand that it is related to having reference to two different sets of data sources in my query. I tried to set the Privacy level of each data source but it didn’t resolve the issue.

 

 

I was doing some research on this issue and came across this interesting post from Ken Puls which talks about a similar issue.

https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

This article talks about a similar issue that occurs when you are trying to access two different data sources in the same query and the solution suggested is to stage them separately and then use the staged tables to combine datasets.

But this approach won’t work in my scenario as I am using a parameter reference values in a list and sent the selected result to a separate query. I did more research on this issue and found that this issue can be resolved by

• • Setting the correct Privacy level
  • Enable the Fast Combine option in Personal Gateway

 

Fast Combine setting on a personal gateway helps you ignore specified privacy levels while executing queries. But there is no direct setting where you can enable this on-premises data gateway (personal mode). To enable Fast Combine, please follow below steps

• Open the config file Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config present at the path %localappdata%\Microsoft\On-premises data gateway (personal mode)\g
• At the bottom of the file, add the following text

<setting name="EnableFastCombine" serializeAs="String">
<value>true</value>
</setting>

 

• Save the file and restart Personal Gateway

Read more about this at Use personal gateways in Power BI. This workaround fixed my issue. But this setting is applicable only for on-premises data gateway (personal mode).