Excel Odata authentication

%3CLINGO-SUB%20id%3D%22lingo-sub-1792302%22%20slang%3D%22en-US%22%3EExcel%20Odata%20authentication%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1792302%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3CBR%20%2F%3E%3CBR%20%2F%3ETrying%20to%20connect%20to%20an%20OData%20feed%2C%20but%20having%20problems%20getting%20authenticated.%20I%20am%20able%20to%20do%20a%20(not%20sure%20if%20this%20is%20the%20right%20term)%20multi%20step%20authentication%20using%20Postman%20against%20the%20feed%2C%20where%20I%20first%20send%20a%20Post%20from%20which%20a%20token%20is%20returned%20which%20I%20use%20for%20subsequent%20Gets.%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20I%20do%20this%20sort%20of%20thing%20in%20Excel%3F%20I%20know%20there%20are%20some%20fairly%20advanced%20possibilities%20using%20the%20PowerQuery%20editor%20(and%20its%20Advanced%20Settings)%2C%20but%20I'm%20unable%20to%20find%20examples%20and%2For%20tutorials%20showing%20how%20to%20do%20this%20kind%20of%20authentication.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20tried%20the%20various%20authentication%20options%20that%20the%20standard%20dialog%20exposes.%3CBR%20%2F%3E%3CBR%20%2F%3EAdmittedly%2C%20I%20may%20not%20even%20be%20asking%20the%20right%20question%2C%20but%20being%20able%20to%20authenticate%20against%20the%20needed%20feed%20would%20make%20my%20month.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3CBR%20%2F%3E%3CBR%20%2F%3ECheers%2C%3CBR%20%2F%3EJeppe%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1792302%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1793340%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Odata%20authentication%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1793340%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F836148%22%20target%3D%22_blank%22%3E%40jjespersen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EJeppe%2C%20not%20sure%20what%20exactly%20you%20are%20doing.%20With%20Power%20Pivot%20procedure%20is%20quite%20simple.%20On%20Excel%20ribbon%20Data-%26gt%3BGet%20Data-%26gt%3BFrom%20Other%20Sources-%26gt%3BFrom%20OData%20feed.%20Here%20enter%20OData%20feed%20endpoint%20URL%20and%20on%20next%20step%2C%20when%20asked%2C%20login%2Fpassword%20for%20Basis%20authentication.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi,

Trying to connect to an OData feed, but having problems getting authenticated. I am able to do a (not sure if this is the right term) multi step authentication using Postman against the feed, where I first send a Post from which a token is returned which I use for subsequent Gets.

Can I do this sort of thing in Excel? I know there are some fairly advanced possibilities using the PowerQuery editor (and its Advanced Settings), but I'm unable to find examples and/or tutorials showing how to do this kind of authentication.

I have tried the various authentication options that the standard dialog exposes.

Admittedly, I may not even be asking the right question, but being able to authenticate against the needed feed would make my month.

Cheers,
Jeppe

3 Replies
Highlighted

@jjespersen 

Jeppe, not sure what exactly you are doing. With Power Pivot procedure is quite simple. On Excel ribbon Data->Get Data->From Other Sources->From OData feed. Here enter OData feed endpoint URL and on next step, when asked, login/password for Basis authentication.

Highlighted

@Sergei Baklan I can "see" the endpoint and all the exposed entities, but when trying to preview or load data from them, I am prompted for authentication, and when using Basic authentication, I am not succesful, unfortunately.


Highlighted

@jjespersen 

Sorry, then I don't know, not a specialist in OData. We actively use OData feed within organization, both with Excel and Power BI it works with Basic authentication. Plus couple of public resources, they are with Anonymous authentication. Don't know which other options exist.

 

I'd check with admins if you have correct credentials for Basic authentication. In opposite to your own creds you will never know about Basic auth creds change if admins don't inform you.