Impossible to load an xlsx file from Sharepoint. Getting various errors.

[hooodoo]

Hi,

I am trying to achieve what, in my opinion, should be a very simple thing - to fetch an xlsx file from Sharepoint and load it into PowerBI.

Somehow it doesn't work for me. Here's what I've tried:

First of all, I am using the "SharePoint folder" data source in "Get Data" in PowerBI. If I copy the link to the Sharepoint group / team I want to access the file from, then I get the following error:

Details: "The input URL is invalid. Please provide a URL to the root of a SharePoint site, or to the service document or entity of its OData feed (with no query or fragment part)."

So what I do is shorten the link. If initially it looked something like this (modified the link for security reasons):

(I modified the beginning part of the links, because I kept running into "incorrect HTML detected" when trying to post this post, which I couldn't fix. Seems like a Community page bug.)

https : //ourcompanydomain . sharepoint . com/:u:/r/sites/The_Teams_Group/SitePages/ProjectHome.aspx?csf=1&web=1&share=randomcharacters1234567890 

Then I shorten it to:

https : //ourcompanydomain . sharepoint . com/:u:/r/sites/The_Teams_Group/ 

Then it seems that I can get to the next step and PowerBI I get a prompt about which credentials / login do I want to use. I can choose between Anon, Windows or Microsoft Account. With the first 2 it says it can't authenticate with my details, but when trying to log in with Microsoft Account, it says "We are unable to connect because this credential type isn't supported for this resource". I'm not sure why is that, as in our company our windows account, microsoft account, sharepoint access etc. is all linked. I can easily access the Teams Group and the file I want to load into PowerBI through either Teams or Sharepoint directly. Moreover, I am already logged into PowerBI desktop with my PPU account.

What I also tried and which seems to work till some extent is to further modify the link like this:

https : //ourcompanydomain . sharepoint . com/sites/The_Teams_Group/ 

Then I again get a popup in PowerBI about the credentials I want to use and I manage to sign in with the Microsoft Account option. After that I get a popup about wheather I want to combine, transform or load the data. The problem here, however, is that when I choose the file I want to load from that teams group, it says "Pdf file failed to load with result: Corrupted". I don't know why would it say that, because 1) that is an xlsx not a pdf file; and 2) the file works well on Sharepoint or in Teams. It is not a huge file either with <1mb of size.

Any ideas of what I might be doing wrong or if it's just a bug would be appreciated.

Thanks!

[Anonymous · Accepted solution]

Hi @hooodoo ,

After my test, you can try this way:
Use OneDrive for work or school links in Power BI Desktop - Power BI | Microsoft Learn

Files in Sharepoint and Onedrive should be interoperable.
Open Onedrive > My files > Find the .xlsx file which you want connect to:

Find details:

Copy path in the right-hand pop-up bar:

In Power BI Desktop, Get Data > Web:

Paste in the link you just copied, select Organizational Accounts to authenticate with the account you use to log in to SharePoint:

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

[Mori_mori]

Thank you! I've been failing to connect for so long and it worked with this link!

[AbhinavJoshi]

Hello @hooodoo. When connecting to SharePoint use Micorosoft Account and make sure you are signed in on Power BI as well. Instead of using SharePoint folder as a Source use Web/CSV. Here are the steps.

1. Navigate to the xlsx file in SharePoint.
2. Click on Open in Dekstop App.
3. When in Excel, click info and copy path (make sure it is a web path not a local path to your machine).
4. Enter the path into the diaglog box of web/csv.
5. Make sure you remove the ?web=1 from the link. It should only be upto yourfile.xlsx

I hope this helps.

Please mark it as a solution if it helped you.

Thank you,

Abhinav