OData Connection Errors - Solution

Connecting to OData sources is a great feature of PowerBI. However, sometimes it is not a slam dunk.

Here are some items to consider if an OData feed gives you problems.  

Using "Get Data" - "Sharepoint List"...

It is possible to run into errors if you give a URI that is too specific (i.e. the URI of the List, giving the list name). Note that you can truncate this to get a complete list of all tables you can access. 

Let's say your URL is (dummy URL below):

https://microsoft.sharepoint.com/teams/SAMPLEWeb/Lists/SAMPLE%20Calendar/calendar.aspx 

Entering this into PowerBI as the URL will get rejected. You might try to remove calendar.aspx  from the end and access the data using:

https://microsoft.sharepoint.com/teams/SAMPLEWeb/Lists/SAMPLE%20Calendar/ 

...this gets rejected - sample error is along these lines

Details: "SharePoint: Request failed:

OData Version: 3 and 4, Error: The remote server returned an error: (404) Not Found. (Not Found)

OData Version: 4, Error: The remote server returned an error: (404) Not Found. (Not Found)

OData Version: 3, Error: The remote server returned an error: (404) Not Found. (Not Found)"

What to do? Truncate to the URL ending just before /Lists

https://microsoft.sharepoint.com/teams/SAMPLEWeb/

This gives all the tables that are accessible. You can then select SAMPLE Calendar - and the data populates.

The second error that can block things is related to the version of the API. To deal with this - connect to the source data and then use the advanced query editor to edit the API version (you will see the ApiVersion listed in the source line).

Example - if you try to connect and get an error along these lines:

"Expression.Error: The key didn't match any rows in the table.
Details:
    Key=Record
    Table=Table"

You can still correct this and get at the data - here's how:

The query will still exist - use "Home - Edit Queries" - and you will see the connection as a query. Select the query and open the Advanced Editor and you will see something like this:

let
    Source = SharePoint.Tables("https://microsoft.sharepoint.com/teams/SAMPLEWeb", [ApiVersion = 15]),

In some cases, that ApiVersion can be a problem - try changing ApiVersion from 15 to 14 in the Advanced Editor:

let
    Source = SharePoint.Tables("https://microsoft.sharepoint.com/teams/SAMPLEWeb", [ApiVersion = 14]),

And click done. In some cases, this can resolve the issue - the table will populate and you have your query in place - so things should be in order moving forward with OData.