Having problems getting data from sharepoint/Onedrive with Power Query (CSV -> Excel tru Web content) @ the authentication dialog box

Question

Trying to get csv data in Excel from sharepoint/onedrive with power query.
All steps are good, but from the moment i'm at the authentication dialog box, it is giving me errors.

If i use basic it is not recognizing the mail and password.
If i'm using the organizational account it is saying: "The WWW-Authenticate header does not contain a valid authorization URI. Header value: 'Bearer realm="OneDriveAPI", error="invalid_token", error_description="Malformed auth token"".

Below is the link with what i'm trying to do. In my case csv with excel.

https://www.myonlinetraininghub.com/get-data-from-onedrive-or-sharepoint-with-power-query

Answer 1

Dear GuidoPeters,

Thank you for the reply.

Since in the title you mentioned "getting data from sharepoint/Onedrive", we assume you use a business account. And from your reply, you are trying to get data from your personal OneDrive. The steps to connect personal OneDrive and business OneDrive are different. You can follow the below steps to see if you could get data from your personal OneDrive successfully:

1. Go to OneDrive, click on the three dots next to the file name, and selectEmbed from the menu.
2. Click the Generate button in the pane, copy the generated code, and paste it into Notepad.
3. Remove parts of the code: <iframe src="https://onedrive.live.com/embed?cid=xxxxxxxxxxxxxxx& resid=xxxxxxxxxxxxxxx%xxxxxx&authkey=xxxxxxxxxxxxxxx&em=2" width="402" height="346" frameborder="0" scrolling="no"></iframe>
4. The final URL looks like this: https://onedrive.live.com/****download****?cid=xxxxxxxxxxxxxxx&resid=xxxxxxxxxxxxxxx%xxxxxx&authkey=xxxxxxxxxxxxxxx&em=2
5. In Excel, click Data > From Web, and paste the above URL into the URL box.
6. Choose Anonymous or Basic to connect, and click OK.

Sincerely,

Jazlyn | Microsoft Community Moderator

Answer 2

Dear GuidoPeters,

Good day! Thank you for posting your query in our community.

Based on your description, when you try to get data from SharePoint/OneDrive with Power Query, an error will pop up when you use the organizational account in the authentication dialog box.

Please first go to Data > Get Data > Data Source Settings > Global permissions > click “Clear Permissions” , this will help to clear existing permissions to prevent the connection remembering any problematic credentials. Then try to test getting data from a SharePoint file to see the result(not OneDrive file).

If the problem persists, may I confirm whether your company Enables MFA or the third-party authentication?

Sincerely,

Jazlyn | Microsoft Community Moderator

Answer 3

Dear Guido,

I agree with you, Microsoft 365 Business is easier to use than the personal OneDrive. You could follow the steps in the article you found to get data from Onedrive for Business in fewer steps.

Sincerely,

Jazlyn | Microsoft Community Moderator

Answer 4

Hi,

Sorry for the confusion with the title.
But i think we can say that by using 365 Business Premium Account would solve the issue and keep it simple in doing stuff like this.
It was what i suspected yesterday and you confirmed it in your comment.

Workaround is nice to know, but i like to keep it simple.

Thanks for the help.

Guido

Answer 5

Hi,

I did clear all permission and did the test again and the issue was the same.
You mentioned to try it first in the Sharepoint, but i don't have that within the 365 personal version.
Then i would have to change from Personal to Business Standard and i'm only willing to do so if that would solve my problem, but not if it would stay the same.

You think Business Standard would solve it?

I have Microsoft Authenticator on my phone yes, but that is only limited used.

But i also saw this page https://learn.microsoft.com/en-us/onedrive/developer/rest-api/getting-started/msa-oauth?view=odsp-graph-onlineThere it is also mentioning Azure Information Protection with the Business Premium Account. If that should be the issues, then it is clear but very limited.