Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
david_piepgrass
Frequent Visitor

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

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?

david_piepgrass_0-1638117466856.png

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".

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

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

View solution in original post

2 REPLIES 2
v-luwang-msft
Community Support
Community Support

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

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.