Cannot apply operator & to types Text and Function

[mschoenhub]

Dear all, 

I am trying to use a bearer token which I get from a function in a query.

Function: 

() =>
let
    // Get the API Token
    api_url = "https://api.xxxxxxx.com",
    token_path = "/oauth2/access_token",
    ClientID = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx", 
    Secret = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx", 

    EncodedCredentials = "Basic " & Binary.ToText(Text.ToBinary(ClientID & ":" & Secret), BinaryEncoding.Base64),

    Token_Response = Json.Document(Web.Contents(api_url,
    [
        RelativePath = token_path,
        Headers = [#"Content-Type"="application/x-www-form-urlencoded",#"Authorization"=EncodedCredentials],
        Content=Text.ToBinary("grant_type=client_credentials")
    ]
    )
    ),

    // Get the token from the API response
    token = Token_Response[access_token]

in
   Token_Response

Query: 

let
api_url = "https://api.xxxxxx.com",
path = "/data/v2/libraryEntries",
fields = "all",

Data = Json.Document(Web.Contents(api_url,
[
    RelativePath = path,
    Headers = [#"Authorization"="Bearer " &#"GET Access Token",#"Content-Type"="application/json"]
    ] )),
    #"Converted to Table" = Record.ToTable(Data),
    Value = #"Converted to Table"{2}[Value],
    #"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "questions", "answer", "languageCode", "creator", "lastUpdatedBy", "createdDate", "lastUpdatedDate", "lastReviewedDate", "lastReviewedBy", "libraryEntryOwner", "inlineImages", "status", "tags", "scores", "location", "attachmentCount"}, {"id", "questions", "answer", "languageCode", "creator", "lastUpdatedBy", "createdDate", "lastUpdatedDate", "lastReviewedDate", "lastReviewedBy", "libraryEntryOwner", "inlineImages", "status", "tags", "scores", "location", "attachmentCount"}),
    #"Expanded lastReviewedBy" = Table.ExpandRecordColumn(#"Expanded Column1", "lastReviewedBy", {"id", "name"}, {"id.1", "name"})
in
    #"Expanded lastReviewedBy"

The query throws the following error:

I got this from the following tutorial https://www.myonlinetraininghub.com/connecting-to-an-oauth-api-like-paypal-with-power-query where it seems to work. Can someone help me please?

[ImkeF]

I would suggest to use a web monitoring tool like Fiddler to compare the 2 requests sent to determine what might be wrong here. 

[ImkeF · Accepted solution]

Ha @mschoenhub ,
that was a tricky one 🙂
In the function for the token, you are not returning the last step.
Please change it to this:

() =>
let
    // Get the API Token
    api_url = "https://api.xxxxxxx.com",
    token_path = "/oauth2/access_token",
    ClientID = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx", 
    Secret = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx", 

    EncodedCredentials = "Basic " & Binary.ToText(Text.ToBinary(ClientID & ":" & Secret), BinaryEncoding.Base64),

    Token_Response = Json.Document(Web.Contents(api_url,
    [
        RelativePath = token_path,
        Headers = [#"Content-Type"="application/x-www-form-urlencoded",#"Authorization"=EncodedCredentials],
        Content=Text.ToBinary("grant_type=client_credentials")
    ]
    )
    ),

    // Get the token from the API response
    token = Token_Response[access_token]

in
   token

[mschoenhub]

We are making progress. Thank you very much! In Postman I have access to the resource. Now it could be that I need to specify a scope (scope = "library:read"). How would I do that?

But if I insert the token directly then it actually works. So maybe the scope is not the issue. 

[ImkeF]

Hi @mschoenhub ,
you have to call the function (although it doesn't have any arguments) like so:

let api_url = "https://api.xxxxxx.com",

path = "/data/v2/libraryEntries",

fields = "all",

Data = Json.Document(Web.Contents(api_url,

[ RelativePath = path,

Headers = [#"Authorization"="Bearer " & #"GET Access Token"(),

#"Content-Type"="application/json"] ] )),

#"Converted to Table" = Record.ToTable(Data), Value = #"Converted to Table"{2}[Value],

#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"id", "questions", "answer", "languageCode", "creator", "lastUpdatedBy", "createdDate", "lastUpdatedDate", "lastReviewedDate", "lastReviewedBy", "libraryEntryOwner", "inlineImages", "status", "tags", "scores", "location", "attachmentCount"}, {"id", "questions", "answer", "languageCode", "creator", "lastUpdatedBy", "createdDate", "lastUpdatedDate", "lastReviewedDate", "lastReviewedBy", "libraryEntryOwner", "inlineImages", "status", "tags", "scores", "location", "attachmentCount"}), #"Expanded lastReviewedBy" = Table.ExpandRecordColumn(#"Expanded Column1", "lastReviewedBy", {"id", "name"}, {"id.1", "name"})

in

#"Expanded lastReviewedBy"

[mschoenhub]

Thank you ImkeF, 

it looks like I am getting back the values from the function now but formatting still is an issue: 

I got that from this video and it looked so easy there.  https://youtu.be/2RZkc_qrV1g?t=782

Kind regards, Max