Datasource error (404): Fail to connect sharepoint excel file

[Anonymous]

Below is the screenshot of the datasoruce error. I have checked the URL is valid but fail to connect to the URL powerbi.

[edhans]

Question - why are you using the Web.Contents function vs connecting to SharePoint via SharePoint.Files or SharePoint.Contents?

[Anonymous]

Because I would like to connect a data to a specific excel file in the sharepoint. I have done a research that if we do so, we should get data from Web.

[edhans · Accepted solution]

No. Use the SharePoint connector. I do it all the time. If you are going for one file, connect via SharePoint. Then, once the files are listed, change the SharePoint.Files() function in the formula bar to SharePoint.Contents(). (It is case sensitive)

then drill down to your file. Power query will open it and show you everything in the file, not just one tab. It will also refresh more reliably in the service. 

[Russo]

I'm using the Web.Contents to conncect to a sharepoint spreadsheet also.  I have a fair amount of applied steps after the source.  What would be the best way to change it to sharepoint connection?

[edhans]

Make a new connection to the file using SharePoint, then navigate to the file until you have the table of data. Copy that M code and replace all of the M code in your original query up to the point where you are starting to transform the data. You will have to change the table name in that first transformation step to point to the last step from the SharePoint connection.

If you want more help, post some code examples of the first 10 or so lines of both code - your original plus the navigation to the table in SharePoint.