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.
