Forum Discussion

mschoenhub's avatar
mschoenhub
Frequent Visitor
3 years ago
Solved

Cannot apply operator & to types Text and Function

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?

  • 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

5 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion

    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's avatar
      mschoenhub
      Frequent Visitor

      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's avatar
    ImkeF
    Community Champion

    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's avatar
      mschoenhub
      Frequent Visitor

      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 

  • ImkeF's avatar
    ImkeF
    Community Champion

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