Dataflow Excel Gateway Connections Issues

[jamesholland198]

Hi,

Really hoping someone can help as this has me stumped and is something that seems to have changed in the last week.

I have a number of existing Dataflows, all with a mixture of On Prem and Excel Web files.  These existing Dataflows work fine and were only setup 1 week ago.

I'm now trying to set up another new Dataflow, again a mixture of On Prem and Excel Web files.  On Prem setup works fine, can select the Gateway and connect.  When I then want to add an Excel Web connection by copying the M code from the working Dataflow into a blank query on the new Dataflow, I then can't authenticate.

I have to select the On Premises Gateway (even though not required as cloud files) otherwise I get a message saying "Changing the on-premises data gateway will apply to all queries in this mashup"

Whenever I try to authenticate through the Gateway no matter which method I try, I get login errors as below.  So puzzling as I'm doing nothing differently to last week when I added about 50 entities across 4 Dataflows, all of whihc same sources as I'm trying to add to the new Dataflow.

Organizational Account:

Windows Account:

Would really appeciate any help at all.

Thanks,

[ibarrau]

Hi. This is confusing. This is quite confusing. The Web.Contents don't need a gateway, you can just enter credentials activating the setting in "Manage Gateways":

Allow user's cloud data sources to refresh through this gateway cluster. These cloud data sources do not need to be configured under this gateway cluster.

If have this checked you don't need gateway for Web.Contents. 

On the other hand if you are sure that you can't access web and it's on premise only, then you might consider using File.Content and a Windows path. I don't think you can configure a windows credentials for web.

Regards, I hope that make sense.

[jamesholland198]

Thanks for your reply.  Yeah I maybe wasn;t clear in my first post, it's difficult issue to explain

I know that you don't need to add web sources to the Gateway, it's just that because my Dataflow already contains an On Prem source, the Gateway is applied to this Dataflow.  And so when I try to add web sources to the Dataflow, it still makes me select the Gateway:

Then when I sign in to Organizational account, I get the below error:

It's just so confusing as I was able to do this with dozens of Web sources last week across numerous Dataflows, yet all of a sudden these authentication issues have started.

If I try to Authenticate using Windows login instead, I just get "Invalid credentials" - even though it works fine when creating a dataset using the same sources and queries directly in Desktop.

Any help would be appreciated, thanks

[ibarrau]

Dataflow will suggest the gateway, but you can change the onpremise gateway to "none" and continue without it. If your excel is in sharepoint it should ask for microsoft professional account (organizational account or auth2).

Have you tried that?

[jamesholland198]

Yes, I can select None as the Gateway when setting up the Web entity, which works fine and connects under Organizational account, until I then add an On Prem source to the Dataflow, at which point PBI then says a Gateway is required - when I do this the On Prem entity is then added OK, and then Web entity stays OK.

But if I try to add another Web entity after this, I have to then select the Gateway and can't select None again - as if I select None I get the below, which then means my On Prem entity stops working

I've created dozens of Dataflow entities with a mix of On Prem/Web and have worked perfectly fine for weeks, it's only the last 2 days that something has changed and now I can no longer create Dataflows with a mixture of On Prem/Web.  Yet the Dataflows already setup in previous weeks (using exact same method as today) still connect and refresh fine, it's only when creating new ones these issues have all started.

As it stands, it looks like the only way around this is to have 2 different Dataflows, one with the On Prem entities and one with the Web entities - just don;t understand why this seems to be the only option now when it didn;t a week ago and I could happily mix different sources within 1 Dataflow.

Thanks,

[_Adrian]

Hi, did you resolve this? I have the exact same issue...