Adding excel as an Entity in Dataflows, failing authentication

[jpperez]

I'm trying to create a new entity from an excel file, every time, the issue seems to be authentication. 

When I try it on the desktop, it works fine, but using dataflows the sign-in pop up never shows up.

I have tried using files on my desktop or stored in Sharepoint and it still doesn't work. once I leave the query, it shows the connection, but not the actual table.

I have also tried copying the M-code from PBI desktop, it loads but also fails authentication.

It is my conclusion that something is wrong with the service.

[jpperez · Accepted solution]

I realized I never posted my question.

I found a "Solution", I had to:

1 use the Web API connector

2 Copy the file location that is provided within excel desktop.

3 Delete the excess text after the file format when pasting the link

Ex:    https://....sharepoint.com/sites/ModelWarehouse/Shared%20Documents/Reference%20Data/Postal%20Codes.xlsx?web=1)

4 not use an on premises gateway and sign in with my microsoft account, it should look like below:

after using this configuration it worked.