Dataflow: cannot add/use Excel from SharePoint when using on-premise SQL Server

[bmvankessel]

I have an existing dataflow retrieving data from an on-premise SQL Server through a data gateway.

Now I want to add a query that retrieves data from an Excel file stored in a SharePoint location. I MUST use the gateway to keep the other queries working as changing the gateway to "(None)" gives me the warning "Changing the on-premises data gateway will apply to all queries in this mashup.".

I enter the correct url for the Excel file name and location but when I want to connect to it with the Organizational account I have to sign in. The login screen pops up but closes immediately showing the error "There was an error in the OAuth popup window (Session ID: da68e155-6833-475d-9f07-8f7dacfbc44f)" under connection credentials.

Any suggestions?
I can load the data in another dataflow and than load the data from that dataflow in the one using the gateway but that is a workaround I do not want to use.

[ibarrau]

Hi I think this might help. Go to Manage Gateways in Power Bi Service and activate the checkbok in step 3.

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-mashup-on-premises-cloud

If you want to refresh or merge cloud and on premise data you need this options activated.

Hope it helps.

[bmvankessel]

Thanks for the reaction.

This option is already checked and works for the datasets that get their data 'directly' from the gateway, both SQL Server and Excel SharePoint queries. Within the dataflow however I cannot sign in with the Organizational Account option for Excel SharePoint queries. I can however sign in to another dataflow that only gets its data from Excel files stored on SharePoint. But that is the workaround I want to avoid.