Web connector returning excel file - Multiple API calls on refresh

[Anonymous]

Hi,

We're working to set up an integration from PowerBI (desktop for the time being) against a REST api. The REST API responds with an excel file (xlsx).

PowerBI designer works great in that it's able to see that the response is an excel file and will show the appropriate interface to create a table per sheet etc on set-up.

However, whenever we refresh the data it seems to be doing an API call to the endpoint for every sheet in the excel file for which we acknowledged a table, which obviously creates for a lot of unnecessary calls to our server.

1) This seems unexpected behaviour; are there any plans to change this?
2) How will a refresh on PowerBI server be handled? How many calls? (PowerBI server acknowledges "Datasets" more explicitly)

Thanks!

[Nidhi_Sonar_123]

Hi, 

I am looking for a solution to the similar issue where my api responds with an excel file, but I am stuck in reading and processing the response in my power query

Could you please share a sample code how you achieved this.

Thanks in advance 

[v-yuezhe-msft]

@Anonymous ,

I am afraid that the above behavior is by design currently.

Power BI will request to get schema and data loading for each sheet, which makes multiple API calls on refresh. And if you turn on backgroud data option in Power BI Desktop, you will get additional API calls. You can disable this option to mitigate this issue.

Regards,
Lydia

[Anonymous]

Are there any plans to change this?

A motivation for us to go with this approach was because of the ease of use of the set-up and the ability to deliver all of the data at once.
As owners of the endpoint we planned on writing instructions for users to connect; having them write M (or copy/paste) is a whole different story.

Could you explain the behavior for PowerBI online (once published)? We didn't test yet, but should we also expect it to make multiple calls?

[v-yuezhe-msft]

@Anonymous ,

I am afraid that there is no plan to change this. In your scenario, you can use fiddler to check the web call after publishing PBIX file to Power BI Service.

Regards,
Lydia