cancel
Showing results for 
Search instead for 
Did you mean: 

[DataSource.Error] SharePoint /_api/contextinfo

(posting as issue as suggested by desktop forum)

 

Hello,

 

I have a dataset that imports from multiple excel files within a SharePoint Folder (~10 tables from ~20 files).  I am using the transform file method as columns can vary.  It has worked fine for many months.  Within the last month or so, I started to get periodic and now persistent "SharePoint: Request Failed" local desktop refresh errors.

 

The specific error is "OLE DB or ODBC error: [DataSource.Error] SharePoint: Request failed: https://xxxxxxx.sharepoint.com/sites/.../xFileName_xlsm/_api/contextinfo".

 

I can see almost everything loads (appears each file has been accessed at some point okay) and then the error is thrown.  I have tried several times to clear/reset SharePoint credentials, clear cache, etc..  It does not necessarily repeat with the same file or even query in the error message.  I had this with July version as well as September and now the latest October releases.

 

Thankfully the published dataset refreshes daily as expected.  If I download the same published PBIX file, it usually refreshes locally okay on desktop the first time.  However, I then get persistent errors regardless if I make any changes.  I am stuck if I need to make any changes at the moment.

 

Note the error metions API, and I have been using API Version 15 without issues in the past.  However, I get the same errors using either 14 or auto.  Again, the published dataset refreshes daily with no issues using API Version 15.

 

Some other posts mention clearing credentials, etc., however this has not helped.  Again, the first refresh after downloading the PBIX works once after downloading, so it doesn't seem some credential related.

 

Has anyone experienced this?  Any ideas?  I appreciate any help.

Status: New
Comments
v-lili6-msft
Community Support

hi  @AgTooOldForThis 

This error usually needs to clear SharePoint credentials in power bi desktop, You may also create a support ticket for assistance if necessary.

 

 

Regards,

Lin

AgTooOldForThis
Frequent Visitor

@v-lili6-msft thank you for your reply.  I have tried many times to delete credentials with unfortunately no change.  I can also see it connecting and loading from all of the files with the error only being thrown at the very end.  I will try the support ticket as you mention.

mraass
Frequent Visitor

Hello,

 

i have the same problem - did you find any solution for this?

 

 

AgTooOldForThis
Frequent Visitor

Hi, yes. I hope this helps you out. 

 

Basically there were too many requests to SharePoint that resulted in throttling and eventually failure with HTTP response 429 Too Many Requests.

 

I had initial queries that imported the data from SharePoint, however I then had subsequent referenced m-queries that worked off the initial imports.  I could see with Fiddler that although the these queries were referencing the initial SharePoint imports, they repeated all of the original SharePoint calls again.  I even had a few places that referenced-a-reference of an initial query, which compounded the number of calls even more.  What should have only been <80 SharePoint calls was now in the 1000s.  I did not realize at the time that a referenced query in desktop m-query does not always reuse the original query (even with disabled parallel loading of tables).

 

I reduced the number of calls two different ways.  Fortunately one report uses a premium enabled workspace so I able to setup a dataflow for the initial SharePoint calls which then eliminates the subsequent referenced queries that duplicated the calls over and over.  This was actually a very easy copy/paste change.  I had another report where dataflows cannot not be enabled, so I ended up rewriting the subsequent m-queries that repeated the SharePoint calls in DAX.

AgTooOldForThis
Frequent Visitor

FYI, I was asked privately for some more details on the problem/solution.  I'm pasting it here in case it were to help anyone else.  Regards

 

If you have a query that pulls information from several XLS files in a SharePoint folder, each one of these import steps requires a SharePoint API call.  However... the fundamental problem for me... if you have another query that references this initial query, Power BI desktop will repeat the entire process of SP API calls again instead of just passing on the existing data. 

For example, if my initial query had 100 API calls, and I referenced it twice in Power Query... I would end up with 300 API calls.  For my particular problem it was in the 1,000s, because I was importing many different XLS tables from many XLS forms and then manipulating the data with several queries in Power Query.  Using fiddler eventually opened my eyes as to how many calls were actually happening.

I solved it two different ways for two different projects.  Both of them are just trying to prevent the repeated API calls (i.e. the extra 200 in my example above). 

#1 Dataflow - One workspace I have allows dataflows.  This was a pretty easy fix because you can literally just copy/paste over the m-code.  So I just made a dataflow for the initial import queries.  Then in my project, I just replaced the original import queries with the dataflow output.   This prevented the subsequent queries from triggering a repeat of the API calls again.

#2 Move to DAX - another workspace I had did not allow data flows.  This was a bit more work, however it worked in the end.  Instead of manipulating the data in Power Query, I rewrote the these queries in DAX.  It was a bit of a pain because some things are very difficult to replicate in DAX that is easy with M-code.  By moving these to DAX, it also prevented the triggering of repeating the API calls again. 

martigas
Frequent Visitor

@AgTooOldForThis  Hello, did you find any solution for this? I need Help

AgTooOldForThis
Frequent Visitor

@martigas Hi. Yes, the two solutions I used are posted above.  Thanks 

martigas
Frequent Visitor

Let me share the way to solved the issue that I already found

 

When I checked the M code for Excel´s Sharepoint that was work I found that the url was:

 --> https://xxxxxxx.sharepoint.com/sites/.../xFile%20Name_xlsm/_api/contextinfo

 

But when I checked the source that was giving troubles, the url was: 

--> https://xxxxxxx.sharepoint.com/sites/.../xFile Name_xlsm/_api/contextinfo

 

Notice that the space character " " is replaced with "%20"

 

I made a new conecction wiht the source that was giving trouble and (I don´t know why) the new URL was:

 

--> https://xxxxxxx.sharepoint.com/sites/.../xFile Name_xlsm/_api/contextinfo

 

I substituted all the M code lines and it works again 🙂