Sometimes you might come across situation where you have to use two separate data sources in one query. In my case I had list of order numbers in Azure Blob and I needed to loop through this list and make API call to receive products for each order.
Side note: You should do looping in Azure Data Factory or your integration tool of choice. Looping API calls in Power Query is not best solution performance wise and you easily end up with super slow query. In my case API limitations and customer requirements forced me to do API calls in Power Query.
Query works fine in Power BI Desktop and refresh executes successfully.
Main problem appears when we upload report file to Power BI Service and try to schedule refresh times for report. Refreshing in Power BI Service fails with Unable to combine data -error:
There was an error when processing the data in the dataset.
[Unable to combine data]: Table references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
There are two workarounds for this problem. First one was found from multiple topics around Power BI forums but unfortunately this one didn’t solve my problem. According to discussions this seemed to work for some. So after trying this I ended up solving my problem with second option.
WORKAROUND 1: Privacy Settings
One reason for this problem is privacy setting which don’t allow multiple sources in single query. Check privacy settings in your report file and every data source used.
In report file settings you must ignore Privacy settings.
In Data source settings set Privacy level on Organizational for each source.
Upload your report file to Power BI Service and check your data source privacy levels. Change privacy levels to Organizational if needed.
Now your refresh SHOULD work. However, for me this didn’t solve the problem so I used method described in next workaround.
WORKAROUND 2: Using Data Flows
This problem can be solved by moving your query to Data Flow and using Data Flow as a source in report.
So create new Data Flow in your workspace and copy your original query there.
And now for the important part. If you try to refresh your data flow you will end up with another combination error message. You have to change Data Flow Privacy Level as described in picture.
Now refresh executes successfully. For some reason changing Privacy Level settings in Power BI Desktop doesn’t work as it should but you can work your way around that probled via Data Flow.
Now you can go back to your original query in Power BI Desktop and change query source to Data Flow.
Upload updated report to Power BI Service and remember to schedule your Data Flow refresh. Data Flow and Report refreshes have to be scheduled separately.