Unable to combine data] Section1/XXXTable/Source references other que

[shekhar_shres]

Hi everyone, 

I am facing a little weird problem. So, I have a big data coming from sql server that I need to extract and since we do not have any pre-defined process to standardize an sql query, the only option I have is to use employee ID in my "where" clause in my SQL query and since employees come and go so often, the EmployeeID in question is subject to frequent changes.

So, I created a table with only 1 column that is "EmployeeID" in an excel file in sharepoint where managers go and add in Employee ID. I connected the onedrive file to Power BI and used text group to make a list of employeeids.

Here's the table example

Here's what I have Trasformed the above table using Power Query 321,444,555 .  The queries name is "SelectedIDs".

And I passed following SQL query (Simplified for this question, the following query is named as "BusinessData" in Power Query).

"Select * from BusinessTable where EmployeeID in ("&SelectedIDs&")"

Now, I set the data source setting to Ignore Privacy Level to combine data.

The data is perfectly loaded and refreshed when using Power BI desktop, however when I publish it to Power BI services and try to refresh it in Power BI services it gives me error saying "Unable to combine data] Section1/BusinessData/Source references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."}},

Has anyone ever faced and solved this problem. Please help.

Thank you in advance.

[lbendlin]

yes, the formula firewall is not happy about your dynamic query as it has a risk of data leakage across queries. (well, not even a risk - it IS leaking data).

You could ignore privacy settings for that dataset (not good) or you could choose to keep these data sources separate in Power Query and do the join in the data model instead.