Does the "OData feed" connector support "Web API" authentication?

[david_piepgrass]

The OData connector's Web API mode always says "A web api key can only be specified when a web api key name is provided." Is this broken? Or is there a way to make it do something?

I hacked up some code in our server to find out what headers are sent by PowerBI; looks like "Web API" mode doesn't even send a request. Since our normal Azure Active Directory authentication is not supported, it looks like realistically the only mode we can use is Basic (though I know we could also use a custom connector); For future reference, the headers I receive from PowerBI in Basic mode is:

Headers on /odata/entities?$top=1000:
Accept = application/json;odata.metadata=minimal;q=1.0,application/json;odata=minimalmetadata;q=0.9,application/atomsvc+xml;q=0.8,application/atom+xml;q=0.8,application/xml;q=0.7,text/plain;q=0.7
Accept-Encoding = gzip, deflate
Authorization = Basic dXNlcm5hbWU6cGFzc3dvcmQ=
Host = localhost:5001
User-Agent = Microsoft.Data.Mashup (https://go.microsoft.com/fwlink/?LinkID=304225)
MaxDataServiceVersion = 3.0
OData-MaxVersion = 4.0

 where the username and password are base64-encoded after "Authorization = Basic".

[v-luwang-msft · Accepted solution]

Hi @david_piepgrass ，

You can pass ApiKeyName in the OData.feed() use Power Query. For more information, see:

authenticating with Excel Power Query against .Net Odata Web Api
API Strategies with Power BI

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[david_piepgrass]

So, fiddling around, the answer seems to be that the "OData feed" connector does not support "Web API" authentication, but there's a workaround involving the M function called OData.Feed:

1. Instead of Get Data | OData feed, click Get Data | Blank query
2. In the query line (beside "fx") write a query of the form `= OData.Feed("https://your_domain/odata", null, [ApiKeyName = "YourCustomKeyName"])` and press Enter to run it
3. A little yellow bar should appear that says "Please specify how to connect." with an "Edit Credentials" button. Click the button. Now the "Web API" option will work (Note: Power BI caches credentials and will not ask for credentials again if it has successfully logged in earlier)
   
4. Instead of a navigation tree, you get a table with three columns. The first column is "Name" and has a list of OData table names. The second column is "Data" and each cell says "Table". For each table you want to use, right-click the word "Table" and choose "Add as New Query" from the menu. This will create a new query to the list on the left. Unlike normal OData feeds, tables created this way will have a "_table" suffix.
5. Close the window. Choose "Yes" when asked to "apply your changes". Your tables should appear in the Fields list on the right, as usual.

But wait! What does the "Web API" option actually do? Apparently all it does is add a key-value pair to the URL (twice?). Here is the relative URL and headers that my app received from Power BI when I set the "Key" to "YourWebApiKey":

Headers on /odata/entities?$top=1000&YourCustomKeyName=YourWebApiKey&YourCustomKeyName=YourWebApiKey:

Accept = application/json;odata.metadata=minimal;q=1.0,application/json;odata=minimalmetadata;q=0.9,application/atomsvc+xml;q=0.8,application/atom+xml;q=0.8,application/xml;q=0.7,text/plain;q=0.7
Accept-Encoding = gzip, deflate
Host = localhost:5001
User-Agent = Microsoft.Data.Mashup (https://go.microsoft.com/fwlink/?LinkID=304225)
MaxDataServiceVersion = 3.0
OData-MaxVersion = 4.0

Lucien, the domain of the second link (angryanalyticsblog.azurewebsites.net) is currently inaccessible.