Refresh issue with combined API and excel dataset

[etyler]

Hi All,

I'm using a dataset which is a combination of an Excel file (stored locally) and some API connections to a third party site. In the desktop app, I've joined these sources with a date table I created using the CALENDARAUTO() command to create the final tables, calculated columns and measures I've used for visualizations.

In the online service I've set up a data gateway connection to update the Excel file, set up the API credentials separately (as it says these are optional to add to the gateway) and set up the schedule refresh.

When I click refresh on the dataset, it seems successful (I get 'connection successful' messages in the refresh history) but when I go to the report online, the visualisations are not displaying the information correctly. Almost all data is displaying as 0.

I've tested the update separately for the excel file through the data gateway (it works) and the API connections without using the gateway (they work). When I do the refresh of the whole dataset within the desktop app it all works fine. 

I'm at a complete loss now, any ideas on what I'm doing wrong?

Thanks

[Anonymous]

HI @etyler,

It sounds like you use the additional connection to the external data sources in your excel file, right? If this is a case, power bi service side, it only traces the change of your excel file. These refresh requests not trigger and activate the internal connection that stored in your excel file. (so power bi can not get records from excel file even if refresh operation processing succeed)

Regards,

Xiaoxin Sheng

[etyler]

No sorry @Anonymous I haven't used an additional connection to them within the excel. The excel file only contains manually entered data.

[Anonymous]

HI @etyler,

Did your report mix with multiple types of data sources? (e.g both on-premise and online datasource)  Any advanced operations in your query tables? (e.g. merge, combine, reference other queries...) They may affect the refresh operations and Datasource configurations.
Regards,

Xiaoxin Sheng

[etyler]

Hi @Anonymous 

Yes the report mixes on-premise excel file and online connections to API. How does this affect the configuration I should use for refresh?

At the moment I've got a gateway connection set up for the excel file and entered web data source credentials separately for the API connections as per below (I have entered credentials as it prompts below):

What's the problem with this- is there something wrong with this configuration? As I said, when I tested each of the datasets separately (local and web) they worked fine. And the report refreshes fine in the desktop version.

Thanks!

[Anonymous]

Hi @etyler,

Please refer to the following document to turn on 'Allow user's cloud data sources to refresh through this gateway cluster' option to use the on-premise gateway to handle cloudy data sources. (it suitable to manage the report with mixed data source types)

Merge or append on-premises and cloud data sources 

Regards,

Xiaoxin Sheng

[JirkaZ]

@etyler Do you have the privacy levels set up correctly (to identical values) in the service?

[etyler]

Hi @JirkaZ , thanks for the suggestion but yes they're all the same...