authenticating with Excel Power Query against .Net

问题:

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"

回答1:

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

回答2:

This is how it should be done and this is tested and working. Token is always regenerated.

let
GetJson = Json.Document(Web.Contents("https://myservice.azurewebsites.net/oauth/token",
     [
         Headers = [#"Accept"="application/json",
                    #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
         Content = Text.ToBinary("login=MYUSERNAME&password=MYPASSWORD&grant_type=password")
     ])),
    access_token = GetJson[access_token],
    AccessTokenHeader = "Bearer " & access_token,
JsonTable =  Json.Document(Web.Contents(

  "https://myservice.azurewebsites.net/odata/Cities",
  [
   Query=[ #"filter"="", #"orderBy"=""],
   Headers=[#"Authorization" = AccessTokenHeader ]
  ])),
#"Cities" = Table.FromRecords(JsonTable[value])
in
    #"Cities"

// Note, when setting privacy credential, set it to "Organizational", and not private and surely not public.