Search code examples
.netoauthodatapowerquery

authenticating with Excel Power Query against .Net Odata Web Api

I am trying to use Power Query to download an Odata Feed that I created using .net Web Api 2 and the OData v4 nuget package.

I'm trying to access an Odata feed that requires authentication. When I edit the authentication type in power query, I'm never seeing the authentication key come through in the request.

How do you configure Power Query to use a specific type of authentication?

Bonus: I'm using OAuth, so how would I configure power query to send in a header with auth data that includes "Authorization Bearer:token_here"

enter image description here

Solution

  • Web API credentials are for putting a secret value into to the URL query (i.e. your API key for some website).

    There's currently no way to add your own Bearer token in Power Query from the credential dialog.

    It's less secure and can't be refreshed, but you can hardcode your credential directly using OData.Feed's Header parameter:

    = OData.Feed("http://localhost/", null, [Headers = [Authorization = "Bearer token_here" ] ])

    (Alternatively, it might be easier to configure your server to accept Basic auth, which is supported in Power Query.)