WHat is the correct way to connect to an excel file in sharepoint online?

[pborah]

I'm trying to connect to an excel file in our organization's online sharepoint (NOT on-prem) using service account credentials. The service account has already been given access to the sharepoint site where the file is located. I've tried all types of authentications -

Anonymous - not applicable here but still tried anyway and nothing.

Windows - neither service account credentials nor my own windows credentials work. 

Basic- credentials don't work

Web API - not applicable

Organizational Account - I tried using my own organizational credentials. They do not work either.

Keep in mind bot the service account and my own account has access to the sharepoint site and I am able to access them on a web browser just fine. I've tried "Sharepoint Folder", and "Web" connections and everytime the authentication fail.s This is incredibly frustrating since we have opther published reports in the service that use this same location and they are working just fine. Sharepoint excel file connections always have been a severe pain for us when setting up.

[pborah · Accepted solution]

Okay solving my own issue. In the latest cloud version of sharepoint along with Azure AD, the service account name has to be fully qualified - e.g. "svc-PowerBI@abcdcompany.com" and you have to use organizational authentication instead of Windows authentication. 

[parry2k]

@pborah this should get you going Loading Excel Files from Sharepoint - (powerbi.tips)

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[pborah]

Sorry @parry2k this didn't work. Authentication fails. We have sharepoint cloud not the on-prem version. Not sure if that has anything to do with it.