Forum Discussion

Zinaida_299's avatar
Zinaida_299
Copper Contributor
Mar 17, 2025

Error loading Excel files from OneDrive Personal into Power Query

Suddenly a few days ago I started getting an error when loading any Excel file from OneDrive Personal into Power Query. 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?

BI & Data Analysis
excel

2 Replies

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Mar 19, 2025

    Please consider this:

     

    • Verify the URL Format:
      • Ensure that the URL you are using is in the correct format for downloading files. The new URL structure with migratedtospo=true might indicate a backend change in OneDrive's file hosting.
      • Try generating a new download link by sharing the file and selecting the "Anyone with the link can view" option. Replace view.aspx with download in the URL.
    • Use the Correct Privacy Level:
      • Double-check that the privacy level in Power Query is set to "Public" for the data source. Go to Data Source Settings in Power Query and confirm this.
    • Test with a Different File:
      • Upload a new test file to OneDrive Personal and generate a fresh link. This can help determine if the issue is specific to certain files.
    • Alternative Method:
      • If the above steps don't work, consider using the OneDrive API or a different method to access the file. For example, you can sync the OneDrive folder to your local machine and load the file directly from the synced folder.
    • EDMD's avatar
      EDMD
      Copper Contributor
      Apr 06, 2025

      Hey Kidd_lp
      Have you tried this solution?
      Does this work for you?

Resources