How to specify an API key name in Excel - A web API key can only be specified when a web API key name is provided

Question

I have a standard MVC API OData enabled service which works under anonymous authentication. I would like to pass a web API key to the application...which works fine when added through code or postman..

Unfortunately my users would prefer to use Excel and I can't I find anywhere in Excel to enter this data...

I've checked Fiddler and Excel is not even trying to send data to the client and I am trapped in one of the errors below.

What is a valid key in Excel and how do it enter it?

Solution

In Excel, open the Power Query Editor, and insert the code below, or adjust the default code: add ApiKeyName = "ApiKey" as part of the 3 parameter of OData.Feed.

let
    Source = OData.Feed("https://localhost/odata", null, [Implementation="2.0", ApiKeyName = "ApiKey"]),
    Table_table = Source{[Name="Table",Signature="table"]}[Data]
in
    Table_table

Now, you can use the Web API credentials dialog. Your key will be part part of the http query string that is sent to the server: ?ApiKey=abcdefg

See also authenticating with Excel Power Query against .Net Odata Web Api

Comments

[Geoff]

Could you expand your answer to explain how to do the same thing if the API token must be passed as a header? I have tried [Headers=[ApiKeyName="Authorization"]] but I get the same error as the OP.