none
How to Power Query to an Excel file located on a Sharepoint site? RRS feed

  • Question

  • Hello,

    I want to Power Query an excel file located on a Sharepoint site.

    What I try to do unsuccessfully is to take the URL of the Excel file from the browser when I open that excel file and I try to power query from Web and from Sharepoint List using that URL and it both return errors. When I try to authenticate it returns a blank excel sheet called Documents that is is no way related to the file I am trying to query.

    Any clues how to achieve this?

    Thanks

    Wassim

    Friday, December 12, 2014 10:30 AM

Answers

All replies

  • The URL in the browser is the wrong URL -- it's the URL of the web-based display, not the URL of the file. What I usually do is to view the file in the browser, then pick “Edit in Excel”. Inside Excel, click on “File” to bring up the file information. Then, click on the file’s location near the top and choose “Copy link to clipboard”. Paste the link somewhere; it should look like this:

    https://microsoft-my.sharepoint.com/personal/curth_microsoft_com/Documents/SqlDataTypes.xlsx?web=1

    Now, edit the link by removing the ?web=1 from the end. This is the URL for the file.

    Friday, December 12, 2014 3:08 PM
  • Hi,

    Thank you for these instructions. I was able to find this link to the file using your instructions.

    However, when I try to link it in Power BI (using table import wizard), I get the error message "The file you have chosen doesn't exist. Select an existing file."

    If I click on the Test Connection button, it says "Failed to connect to the server. Reason: Failure creating file."

    Any idea where this goes wrong? The URL looks about right and by copy-pasting it to a browser, I end up in the right place (i.e. it tries to download the correct file, and download succeeds - thus link is ok and server connection works fine).

    Tuesday, June 16, 2015 8:45 AM