Power BI error when connecting to sharepoint

Question

One of my dataset is failing to refresh with the error :

SharePoint: Request failed: https://bconline.sharepoint.com/sites/xxx/Shared Documents/xxxx_xlsx/_api/contextinfo

This usually is an intermittent issue. I am trying to find the root cause for the issue. Any troubleshooting steps or suggestions is also welcome.

Answer 1

Try deleting the connection and create a new connection from scratch and see if helps.

Answer 2

If you are trying to connect to the excel file in the sharepoint you can use it in a connector like following

let
    Source = Excel.Workbook(Web.Contents("https://abc.sharepoint.com/teams/Analytics/Shared%20Documents/Folder/subFolder/file.xlsx"), null, true),
    Finance_Sheet = Source{[Item="Finance",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Finance_Sheet, [PromoteAllScalars=true])
    in
    #"Promoted Headers"

If you want to connect only to the sharepoint site and select later within the query, you can use the following conncetion

let
    Source = SharePoint.Files("https://abc.sharepoint.com/teams/Analytics", [ApiVersion = 15])
in
    Source

In the latter approach the trick is not use the full path of the file but only the root URL

Answer 3

@smpa01

This is exactly how to get it to work, that's saved me lots of time. In my case I had a csv file on SP that I uploaded from an email attachment each night (using Power Automate).

= Csv.Document(Web.Contents("https://mysite.sharepoint.com/sites/MyFolder/Shared%20Documents/Chargeable%20Time/Chargeable_Time_Report.csv"),
[Delimiter=",", Columns=15, Encoding=1252, QuoteStyle=QuoteStyle.Csv])