PowerBI connection to Excel in Personal OneDrive suddenly stopped working

[cjwogan]

I have been using a PowerBI dashboard for the past three years to connect to my personal finance workbook via personal OneDrive. To do this, I used the source line in my PowerQuery: 

= Excel.Workbook(Web.Contents("https://onedrive.live.com/download?resid=[FILEID]%212626&authkey=![AUTHKEY]&em=2&app=Excel"), null, true)

This line worked completely fine until mid-July, when it suddenly started throwing an error:

"DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server."

I changed nothing about the file or access, so I'm stumped as to why it broke. I attempted to retrace original steps to get the file set up, which involved getting the Embed link from the file and making minor modifications, but the Embed link no longer has the same format as it did before, and it's unclear whether some part of the file ID or auth key changed. The process I followed before just doesn't work.

Also of note, if I take away the "Excel.Workbook" part of the M code, it finds the file and has the file size correct. Every option to load data fails except opening the file as text, which provides some boilerplate HTML info. Anyone have ideas?

[odraode83]

Hi all, does anyone have found a solution to this? I'm stack here from august and I would like to fix my architecture with a workaround.

Thanks a lot!

[PowerBICouple]

I am having this issue as well. Nothing seems to be able to force it to generate a onedrive.live.com url it's all shortened. ChatGPT is no help. Switch to Google Drive really the only solution?

[cjwogan]

I really appreciate the response. I tried clearing credentials and resetting to anonymous, but the problem I have is that the embed link no longer has the same format. Here's the embed link format I get: 

"https://1drv.ms/x/c/<FILEID>/<SOMETHINGRANDOM?>AAAAAAMl_<CREDENTIALS>?em=2&wdAllowInteractivity=False&wdHideGridlines=True&wdHideHeaders=True&wdDownloadButton=True&wdInConfigurator=True&wdInConfigurator=True"

[OwenAuger]

No problem 🙂

I'm not 100% sure I can replicate the behaviour you're seeing. Would you be able to post back screenshots of how you navigated to the 1drv.ms URL you're currently getting?

Below  are two methods I tried that produced the resid/authkey URL:

1. From OneDrive, open the Excel file in edit mode > File > Share > Embed.
URL then appears within the embed code in resid/authkey format.

2. From OneDrive file navigation > Open Excel files "..." menu > Embed.

URL initially appears in 1drv.ms format.

Click "Customize how this embedded workbook will appear to others".

Then Embed code includes URL in resid/authkey format.

[cjwogan]

I'm sorry for being so dense on this but I still can't get thing working correctly! Here's as much detail as I can give:

I'm testing on a brand new workbook, called TestPBIs.xlsx:

From this file, I'm following the steps outlined to get to the embed screen:

When I generate HTML code to embed the file, I see the following:

Then, when I try the alternate version, going in from the OneDrive folder without opening the file, I see this:

When I click the customize button, I'm taken to a new screen that has no source HTML at all!

Beyond confusing.

One other tidbit that may or may not be relevant: The file that I am looking to create the link for is a .xlsm. Again this was not a problem in any way before mid-July, and then it just died. 

[OwenAuger]

No need to apologise! 🙂

The exact behaviour does seem to differ between accounts for some reason.

In that last screen you posted, could you copy out the entire contents of the "Embed code" box, beginning with <iframe... and see if that contains a useful URL?

In the meantime, I'll do some more testing at my end!

All the best 🙂

[whitespot635]

Hello OwenAuger:

After I made multiple tried within my own Onedrive account, I think the issue is OneDrive Embed Code is difference between the file create by your own and other owners.

The method you showing to OP is only work If the documents are uploaded by yourself and shared with others. However if the file is shared from others then it won't able to covert to the URL link with "resid/authkey" format

For example, below is Shared Files under my onedrive account and the there is no option as "Customize how embded code appear to other" link.

The Url code I got by clicking the info icon show inside the preview window is:

https://onedrive.live.com/view.aspx?resid=6EE99A0280F5653A!7347&migratedtospo=true&redeem=aHR0cHM6Ly...

However when I paste this code inside Excel.Workbook{web.content("[URL Link]")} as url link the power query respond with the file is HTML rather than Excel

[OwenAuger]

Thank you @whitespot635, that's useful additional information!

I note that the OP ( @cjwogan ) did some tests with newly uploaded files in OP's own account, and also mentioned that it worked pre July. So I'm not sure if there's some other explanation for OP. 

@cjwogan Have you had any luck with this since your last post?

[cjwogan]

Unfortunately, no dice:

<iframe width="402" height="346" frameborder="0" scrolling="no" src="?wdAllowInteractivity=False&wdHideGridlines=True&wdHideHeaders=True&wdDownloadButton=True&wdInConfigurator=True&wdInConfigurator=True"></iframe> 

I checked on multiple browsers and multiple files, and the result is the same. 

[OwenAuger]

😞 Hmm that's weird, the iframe code doesn't include any URL at all after src= !

Is there text being missed when copying out of the (annoyingly small) text box?

As it is, it would be an empty iframe I assume.

Idea 1: Could you try that again with your actual file, and right-click the Embed code box > Inspect?

This is what I see in Chrome:

Idea 2:

Alternatively, could you try this other avenue with your actual file:

1. Generate a 1drv.ms embed link by any means. Just to try something different, I did the following:

(a) Open the Excel file in the browser

(b) Select Editing > Open in Desktop

(c) In Excel Destop application, File > Share > Share with People > Share with People

(d) Configure as "Anyone with the link can view" and select Copy

(e) This creates a 1drv.ms link:

2. Paste the 1drv.ms link to the browser.

For me, this redirected from

https://1drv.ms/x/s!Aujav0_KPzBhgesudAcfkGDbMJVx2g?e=lobMac

to

https://onedrive.live.com/view.aspx?resid=61303FCA4FBFDAE8%2130126&authkey=!AHQHH5Bg2zCVcdo

3. If you end up with such a link, change view.aspx to download

https://onedrive.live.com/download?resid=61303FCA4FBFDAE8%2130126&authkey=!AHQHH5Bg2zCVcdo

[cjwogan]

Maybe making some progress.

I checked the inspect window and didn't see anything different from what was in the embed box on the UI, and I confirmed that the embed code wasn't missing anything. Just bizarre there.

Using your second idea, I did get to a browser window that has the resid but not the authkey: 

https://onedrive.live.com/edit?id=<FILEID>!2626&resid=<RESID>!2626&cid=3bf03d64bf512582&ithint=file%2Cxlsx&redeem=aHR0cHM6Ly8xZHJ2Lm1zL3gvYy8zYmYwM2Q2NGJmNTEyNTgyL0VZSWxVYjlrUGZBZ2dEdENDZ0FBQUFBQlU2Rno4MDN6Y0xWODFjelpZVk1PdEE_ZT1SUG9Bd0s&migratedtospo=true&wdo=2

The link opens with "edit?" instead of "view.aspx" at the beginning, and I still don't see any authkey. I played around with the string and didn't have luck there. I also tried disabling browser extensions, to no avail.

[OwenAuger]

Thanks for the update 🙂

When creating a sharing link from Excel Desktop, could you check that it is set to "Can view" rather than "Can edit"?

For me, the resulting 1drv.ms URL redirects to

https://onedrive.live.com/view.aspx?resid=<ItemID>&authkey=<AuthKey>

If that doesn't work then I'm really not sure.

There are ways to create an embed URL for OneDrive Personal using the Graph API but that's beyond me at the moment.

Hope you can get it working somehow! 🙂

[cjwogan]

Update: I couldn't get it to work at all, so I deployed a work-around. I used a Flow to copy my workbook to Google Drive, and then I used a direct download link from Drive as the revised source for my PBI workbook. Not ideal, but successful!

[bye]

Hi @cjwogan, I'm on the same boat and this issue has caused me significant stress. Could you share the steps to copy a workbook to Google Drive? This is my hope to find a solution. Many thanks.

[Anonymous]

Hi, @cjwogan 

You can mark your reply as a solution so that other members of the community can quickly find your post when they have a similar problem, saving them time searching for answers.
Your cooperation will work with us to promote the development of the community. Thank you again for your cooperation!

Best Regards

Jianpeng Li

[Anonymous]

Your solution is great OwenAuger

Hi, @cjwogan 

Current error: The downloaded data is HTML, which isn't the expected type.
You can check the solutions linked below:

Excel "GetData" from file or web (OneDrive) - Microsoft Community Hub

DataSource.Error: The downloaded data is HTML, whi... - Microsoft Fabric Community

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[OwenAuger]

Hi @cjwogan 

That sounds a bit odd!

It's a while since I've connected to a OneDrive Personal file but I just tried it with one of my own and was able to connect.

Here are some screenshots from my attempt, in case it helps (feel free to connect to my file):

1. Open Excel file in onedrive.live.com

2. File > Share > Embed

3. Generate > Copy Embed code

<iframe width="402" height="346" frameborder="0" scrolling="no" src="https://onedrive.live.com/embed?resid=61303FCA4FBFDAE8%2130126&authkey=%21APNVSFKzcP9oZCU&em=2&wdAllowInteractivity=False&wdHideGridlines=True&wdHideHeaders=True&wdDownloadButton=True&wdInConfigurator=True&wdInConfigurator=True"></iframe>

4. Extract URL and modify.

I didn't bother keeping &em=2, and also didn't add &app=Excel since it seemed to work without these.

https://onedrive.live.com/download?resid=61303FCA4FBFDAE8%2130126&authkey=%21APNVSFKzcP9oZCU

5. Connect in Power Query:

= Excel.Workbook(Web.Contents("https://onedrive.live.com/download?resid=61303FCA4FBFDAE8%2130126&authkey=%21APNVSFKzcP9oZCU"), null, true)

One thing: try clearing the credentials and resetting to Anonymous. At least that worked for me.

Not sure if this will help diagnose. Please post back if needed.

Regards