Data Source Error when loading into Power Query

[Zinaida]

Suddenly a few days ago I started getting an error when loading any Excel file from OneDrive Personal into Power Query. I have many Power BI reports with similar data loading. And it all stopped working at one moment. To download I use
Excel.Workbook(Web.Contents("https://onedrive.live.com/download?resid=....&authkey=...." ), null, true )
Access Web content - Anonymous, privacy level - Public. The file is readable by anyone with the link.
As a result I get an error:
DataSource.Error: HTML data loaded that is not the expected type. You may have entered an invalid URL or provided invalid credentials to the server.
Until now everything worked fine with these same files. At the same time I noticed that when I get a URL link on OneDrive via File - Embed - Details, I receive a URL address that is not the same as before "https://onedrive.live.com/view.aspx?resid=....&authkey=...." ("view.aspx" always had to be replaced with "download") . Now the link looks like this "https://onedrive.live.com/view.aspx?resid=.....&migratedtospo=true&redeem=....". And of course, this link does not load data into PowerQuery even if you change "view.aspx" to "download".
Please tell me how to fix the error?

[v-vpabbu · Accepted solution]

Hi @Zinaida,

Thank you for reaching out to Microsoft Fabric Community Forum.

Microsoft has recently changed the URL structure for OneDrive Personal shareable links, which may be causing the issue.

If possible, move the file to OneDrive for Business and use the SharePoint Folder connector in Power BI. Go to Get Data > SharePoint Folder, enter your SharePoint or OneDrive for Business site URL, locate your file, and load it into Power Query. When prompted, sign in with your Microsoft credentials. This ensures a stable connection without relying on changing OneDrive links.

If that’s not an option, you can manually download the file to a local folder and connect Power Query to it.

For reference: https://answers.microsoft.com/en-us/msoffice/forum/all/onedrive-embed-url-format-change-breaks-all/4... 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Vinay Pabbu

[Akash_Varuna]

Hi @Zinaida Could you try these please 

1. Generate a direct download link:

   • Share the file in OneDrive and copy the link.
   • Replace the older link in your query with the copied link.

2. Update Power Query:

Excel.Workbook(Web.Contents("copied-link-here"), null, true)

• Set privacy level:

  • In Power BI, go to Data Source Settings and set Privacy Level to Public.

[Zinaida]

Hi, I have already tried all these options. Nothing works.