Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone,
I made a PowerBI report that retrieves data from a public web API.
In order to do that, it needs to provide as API parameters latitude and longitude of a point.
I retrieve those coordinates from an Excel file, which is located on a folder in the company Sharepoint.
If I refresh the report from Power BI Desktop, it works.
If I refresh the report dataset from Power BI Service, after the report is published, it does not work, providing the following error message: [Unable to combine data] Section1/Eventi/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
I already looked for this issue and found this blog post, which is often mentioned as solution for this kind of issues:
https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/
I applied all the suggestions of that post, by loading the Excel file in a staging query and then referencing it from the query where I perform the web call, but I still get the error.
I set the privacy level for the Sharepoint folder to "Organizational" and for the API as "Public" (but I tried also with "Organizational", whith the same result).
Last note: if I embed in a query the coordinates that I want to use as parameters and reference this query from the one that retrieves API data the refresh works also from the service, but of course this is not the behavior that I desire.
Hi @fpennisi17 ,
My suggestion is to set the privacy level of both data sources to None.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-lionel-msft wrote:Hi @fpennisi17 ,
My suggestion is to set the privacy level of both data sources to None.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I tried by setting both data sources to None but it did not worked as well.
Hi @fpennisi17 ,
Try to change this setting:
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-lionel-msft wrote:Hi @fpennisi17 ,
Try to change this setting:
Options --> CURRENT FILE --> Privacy then setting the Privacy Level to "ignore the privacy levelsBest regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sorry, probably I was not clear. The report on my PC works (it's enough to set the sources to None), but I need it to work on the Power BI Service.
And I don't have that option on the service (or, at least, I don't know where to find it).
Hi @fpennisi17 ,
Yes, you're right, this setting does not take effect in Power BI Service.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You likley will need to encapsulate that staging query in a function then reference it from the query where you perform the web call. I have ran into this many time, particularly when using web service api calls
I can't take credit for this work around as I found a very good article here: https://www.poweredsolutions.co/2019/03/12/data-privacy-and-the-formula-firewall/ , where I found this "Embedding or masking your Data Sources inside Functions – this method will make your data sources not visible to the Data Privacy Levels at first, but you can tune your queries to define the data source at first and then apply a function."
This ultimately worked for me
Good luck,
@blopez11 wrote:You likley will need to encapsulate that staging query in a function then reference it from the query where you perform the web call. I have ran into this many time, particularly when using web service api calls
I can't take credit for this work around as I found a very good article here: https://www.poweredsolutions.co/2019/03/12/data-privacy-and-the-formula-firewall/ , where I found this "Embedding or masking your Data Sources inside Functions – this method will make your data sources not visible to the Data Privacy Levels at first, but you can tune your queries to define the data source at first and then apply a function."
This ultimately worked for me
Good luck,
I tried to apply your suggestion, but it still does not work.
The web call is encapsulated into a function.
The loading of the Excel Sheet that determines the coordinets used as input for the web call is encapsulated in a function as well.
I call the function in a table and the reference this last table as the origin of the query with the web call, as shown in the data source view below:
Am I missing something?