Trying to access and excel file in a SharePoint folder and not getting their

[CamJohnston]

As the subject says I am trying to access an Excel file from a SharePoint site. As per the graphic below I am getting to share point site but not able to any of the individual site. There is probably something simple here that I am missing. Help would be appreciated.

[themistoklis]

For sharepoint,I use 'Data Source Type' = Web and not Sharepoint.
For Authentication Method I select OAuth2

Notes:
It is best to get the link for excel file from MS Teams. The link must be the folder link where the file lives. Then at the end of the link add a backslash and the filename.
See following format:

https://companyname.sharepoint.com/sites/PowerBIDashboards/Shared%20Documents/General/MyFiles/AccountCode_to_Category.xlsx 

Authentication through organisation with the credentilas

In Power Query (Source Step) it will look like the following
= Excel.Workbook(Web.Contents("https://companyname.sharepoint.com/sites/PowerBIDashboards/Shared%20Documents/General/MyFiles/AccountCode_to_Category.xlsx "), null, true)

[CamJohnston]

Thanks for the quick reply. Very much appreciated. Sorry this may be a very basic question, I am not getting how to file from Teams. Can you point me to some guidance for this? I have tried a quick search but no luck.

Thank you and again apologies for the basic questions.

[themistoklis · Accepted solution]

Try the following steps:

1. Go to Teams or SharePoint (OneDrive) and open the file in the DESKTOP app (e.g. Excel for Desktop)
2. Click File, then Info
3. click the "Copy Path" option to copy the link into the Clipboard (see image below)
4. Go to PowerBI Desktop 
5. Get Data, choose Web and paste the link there (removing the last few characters "?web=1").  

[TsukiakariUsagi]

Weeks over weeks of trying and failing, then coming back to it later to try and solve this weird problem and this is the answer that finally did it for me.  Thank you so much for posting this!!!

[CamJohnston]

That worked! Thank you!