Incremental load refresh with 2 different sources not working

[Anonymous]

Hi All,

First post, yey 🙂

 I am working on setting up my first dataset with incremental load. It merges data from a MSSQL table and an excel file hosted on my company's sharepoint. It works fine under PBI desktop but when i publish it, the refresh of the dataset doesnt function in PBI Service. I don't always get an error message but when I do it is something like :

'Unable to connect to the data source undefined'

'Underlying error message: Sharepoint: Request failed'

I have made sure I edited my authentication settings and they are both fine (for the excel file, I use Oauth2) 

I have tried different things:

-publishing 2 apps each containing one of the data source. They both work independantly.

-when I take reproduce the excel file directly in the .pbix (so no connection is needed) and merge it with the MSSQL table and publish it on PowerBi services, the refresh works fine.

-I played with the privacy level of the source by setting them to 'public' and 'none' but it didn't work.

-For the excel document i tried using 2 different connectors: 'web' and 'sharepoint'

I am now running out of ideas... anyone has faced a similar issue or has any leads?

'Help me PBI community, you are my only hope' 

[v-danhe-msft]

Hi @Anonymous ,

How could you get data from your sharepoint? Could you mean you have merged your data in an excel and upload it to the sharepoint, the get data from it with Power BI Desktop? And from your description, For the excel document i tried using 2 different connectors: 'web' and 'sharepoint', could you have configured gateway for your web source when you want to refresh your data?

Regards,

Daniel He

[Anonymous]

Thank you for your answer. Sorry if i wasn't clear. Let me rephrase:

I have an excel file stored on sharepoint and a table hosted on mssql server. In PBI desktop, in edit query, i merge these two data sources and it works. However, when I publish the .pbix on PBI Services. The refresh of the dataset won't work. I have a gateway for the data hosted on MSSQL server but since the excel is on sharepoint, i dont think it requires a gateway correct?

[v-danhe-msft]

Hi @Anonymous ,

Could you have checked the credential for your dataset for your sharepoint?

Regards,

Daniel He

[Anonymous · Accepted solution]

Indeed, I was using already using the Organizational credential. I have finally uncovered why after asking around in my company. The issue already happened in other services and  it seems that there is a bug (or undocumented behavior) due to differences in engines in Power BI Desktop and our Gateway. Not much we can do about it sadly so I had to find a workaround. 

Thank you for your help 🙂