Custom Data Connection Returns JSON But Desktop App Says OData Not Service or Feed

[speschka]

I've been working on a custom data connector for Power BI desktop that consumes data from a REST API we've built that is secured by Azure AD. I thought I was finally through the hard part - getting the authentication working - but now that I'm connecting to our REST API and retrieving data, I'm getting the error from Power BI Desktop of "OData: The given URL neither points to an OData service or a feed."

When I look in Fiddler though, I can see data coming back, and it's all correctly formatted JSON. I copied the data from Fiddler and pasted directly into a Visual Studio JSON document and it shows no errors at all. So...I'm not really sure what Power BI wants here. My feed is configured in my custom data connector like this:

source = OData.Feed("https://www.foo.com/api/data/powerbisummarydata")

The only thing I could think of was that perhaps "OData.Feed" isn't the right option, but I could not find another one in the SDK that would appear to work. Any suggestions? I've been looking through the MyGraph sample as well in the SDK samples and cannot find anything it's really doing differently.

Thanks.

[speschka · Accepted solution]

An update here, i.e. answer, for those finding themselves in the same position.  I'll try and be brief so here's the gist:

1.  An out of the box ASP.NET MVC Web API controller returns data when asked for an OData feed, but for whatever reason, Power BI acts like it's no good.

2.  The first part of the fix is to change what your function returns.  In my original connector it was this:

source = OData.Feed("https://www.foo.com/api/data/powerbisummarydata")

Now it looks like this:

DefaultRequestHeaders = [
#"Accept" = "application/json;odata.metadata=minimal", // column name and values only
#"OData-MaxVersion" = "4.0" // we only support v4
];

...

source = Web.Contents("https://www.foo.com/api/data/powerbisummarydata", [ Headers = DefaultRequestHeaders ]),
json = Json.Document(source)

From my reading of things, the DefaultRequestHeaders is not really necessary because Power BI won't recognize it as a feed anyways, but it's here for completeness.

Once this change is implemented, Power BI actually treats it like data, but as a series of "records" without any of the columns associated with them.  So the rest of the fix is done in Power BI itself, after you've deployed your customer connector.

1.  It's best to just start by getting data with a New Query.

2.  The query steps look like this:

//pull the data in from your connector

STEP 1:
= YourSharedFunctionName()  //i.e. MyConnnector.Feed()

//split it into records

STEP 2:
= Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

//expand the fields in your records - greatly simplified here for readability
STEP 3:
= Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"City", "State", "ZIP"}, {"City", "State", "ZIP"})

After Step 3 - voila! - you get rows of data.  Or at least I do; my Web API returns a List<MyDataRecord>.

Hope this helps someone.