cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Please Help: Trying to encode/decode Access Token (Rest API)

Hi Folks,

 

I'm new to Power BI and was hoping I could ask someone over here for some advice.  I'm working with the Rest API.  And well, I've got most of what I need working within the Power Query when I hardcode an access token (retrieved from PostMan).  However, when I try to replicate the PostMan code and retrieve the key within PowerBI it fails.

 

If the value isn't hardcoded it returns an error about converting a value of type function to type logical.  I'm a bit at a lost.  Maybe I need to parse/decode the data being passed back?  PostMan does this automatically.  Any guidance or point of direction would be much appreciated! 

 

The snippet of code in question is the following:

 

 

 

 

    GetPages = () =>
        let
            Source = 
                Json.Document(
                    Web.Contents(
                        "https://REMOVED/api/Token",
                        [ 
                            Headers = 
                                [
                                    #"Content-Type"="application/x-www-form-urlencoded" //,
                                ],
                            Content = Text.ToBinary("Username=REMOVED&Password=REMOVED&grant_type=password&Integrated=N&database=REMOVED&Client_Id=REMOVED=&client_secret=REMOVED",BinaryEncoding.Base64,Binary.Compress)                        ]
                    )
),
 
//This fails
            apikey = Source[access_token],
 
//This works (copied and pasted from PostMan
            apikey = "INSERT_POSTMAN_CODE",

 

 

 

 

 

 
POST MAN CURL DETAILS:
curl --location --request POST 'https://REMOVED/api/token' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode 'Username=REMOVED' \
--data-urlencode 'Password=REMOVED' \
--data-urlencode 'grant_type=password' \
--data-urlencode 'Integrated=N' \
--data-urlencode 'database=REMOVED' \
--data-urlencode 'Client_Id=REMOVED' \
--data-urlencode 'client_secret=REMOVED'
 
This returns the raw data below... where I pull VALUE1 and see it working: {"access_token":"VALUE1","token_type":"bearer","expires_in":1799,"refresh_token":"REMOVED"}
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper IV
Helper IV

Re: Please Help: Trying to encode/decode Access Token (Rest API)

For those curious this eventually was determined to be a server bug.  However, I did clean up the final code.  Attached is the working code for anyone that wants it...

(environment as text) as text =>

let
    sand_UserID = [REMOVED],
    sand_Database = [REMOVED],
    sand_Password = Uri.EscapeDataString([REMOVED]),
    sand_Client_Secret = Uri.EscapeDataString([REMOVED]),
//EscapeDataString is required for any phrase that includes special characters, although probably doesn't hurt to have on all of them.
  
    prod_UserID = [REMOVED],
    prod_Database = [REMOVED],
    prod_Password = Uri.EscapeDataString([REMOVED]),
    prod_Client_Secret = Uri.EscapeDataString([REMOVED]),

    Client_ID = Uri.EscapeDataString([REMOVED]),

    UserID = if environment = "sand" then sand_UserID else prod_UserID,
    Database = if environment = "sand" then sand_Database else prod_Database,
    Password = if environment = "sand" then sand_Password else prod_Password,
    Client_Secret = if environment = "sand" then sand_Client_Secret else prod_Client_Secret,


        token_url = "https://[REMOVED]/api/",

        body="Username="&UserID&"&Password="&Password&"&grant_type=password&Integrated=N&database="&Database&"&Client_Id=" & Client_ID & "&Client_Secret=" & Client_Secret,

        params =
        [ 
            Content = Text.ToBinary(body),
            RelativePath = "Token",
            Headers = [#"Content-Type"="application/x-www-form-urlencoded"]
        ],

        WebSource = Json.Document(Web.Contents(token_url,params)),

        apikey = WebSource[access_token]
in
    apikey

View solution in original post

12 REPLIES 12
Highlighted
Microsoft
Microsoft

Re: Please Help: Trying to encode/decode Access Token (Rest API)

The bug is not in access_token, it is in your source variable. When you reference Source it is evaluated, as such when you hard code the access token in Source is never evaluated. The actual bug is in Text.ToBinary where you pass the 3rd parameter as a function (without invoking it), but what it expects is a byte order either ByteOrder.BigEndian or ByteOrder.LittleEndian.

Highlighted
Helper IV
Helper IV

Re: Please Help: Trying to encode/decode Access Token (Rest API)

Thank you for the prompt follow-up!  

 

I'm a bit rusty to programming, and not at all familar with Power Query / M.  However, I tried your recommendation.

 

When I invoked:

Content=Text.ToBinary(body,BinaryEncoding.Base64, ByteOrder.BigEndian)

 

It responded:

Expression.Error: We cannot convert the value 1 to type Logical.
Details:
    Value=1
    Type=[Type]

 

Likewise, LittleEndian, responded with "value 0" and, as you noted, Binary.Compress, responds with "Function" in place of the value in the error message.  I think we are on the right path, as this change definitely updated the error.  However, going this path, I guess I'm a bit thrown by why the error happens so far upstream (although it sounds like the language just ignores "Source" if I don't reference the value after storing it, that's a bit wierd?!?).  The next line is to set the session's API to the token returned.  If I try just random garbage, the system realizes the credentials have failed and will not pull the API call for the data request.  However, if I set the string to the value obtained from PostMan, it works just fine and pulls the data.  I guess I'm just surprised, it isn't responding saying bogus credidentials and/or with that 400 error message. 

 

Definitely heading the right direction... any other thoughts or advice?  Seriously... been pulling my hair out looking at this one all day...  Thank you so much in advance.

 

 

Highlighted
Helper IV
Helper IV

Re: Please Help: Trying to encode/decode Access Token (Rest API)

I'll add if I hardcode either false or true as that third parameter, then I do get the 400: Bad Request.  Again, it all matches with PostMan.

Highlighted
Microsoft
Microsoft

Re: Please Help: Trying to encode/decode Access Token (Rest API)

I think the issue is probably your second parameter: 

BinaryEncoding.Base64

I think you want a text encoding, not a binary encoding there. Maybe TextEncoding.Ascii is fine? BinaryEncoding is for converting binary to text.

Highlighted
Helper IV
Helper IV

Re: Please Help: Trying to encode/decode Access Token (Rest API)

Ok... getting a little warmer...

 

I'm getting the (400): Bad Request response when I execute the following code.  I cleaned it up a bit since my first post.  I also, have (for now) put true after Base64, but not sure what whether that is right or wrong.  As far as I'm aware this is identical to Post-Man (which works), but I don't know either tool well, so perhaps I'm missing something?!?!? 

 

 

token_url = "https://DOMAIN/api/Token",
    body="Username=VALUE1&Password=VALUE2&grant_type=password&Integrated=N&database=VALUE3&Client_Id=VALUE4=&client_secret=VALUE5",
    params =
        [ 
            Headers = [#"Content-Type"="application/x-www-form-urlencoded", #"Accept"="*/*", #"Accept-Encoding"="gzip, deflate"],
            Content = Text.ToBinary(body,BinaryEncoding.Base64,true)
        ],
    WebSource = Json.Document(Web.Contents(token_url,params)),
    apikey = WebSource[access_token]
    

 

 

WIth regards to Power BI, I tried with and without the cookie.  Post-Man sends Cookie, Postman-Token, Content-Length, and Host within the Header.  If I "uncheck" Host, Post-Man fails (not with 400, just in general), but if I try to override Host in my code, it says I'm not allowed (and to use the appropriate method).  I read it might be sending it automatically, or something?!? 

 

curl --location --request POST 'https://DOMAIN/midtowncg/api/token' \
--header 'Cookie: AWSELB=VALUEA; AWSELBCORS=VALUEB' \
--data-urlencode 'Username=VALUE1' \
--data-urlencode 'Password=VALUE2' \
--data-urlencode 'grant_type=password' \
--data-urlencode 'Integrated=N' \
--data-urlencode 'database=VALUE3' \
--data-urlencode 'Client_Id=VALUE4' \
--data-urlencode 'client_secret=VALUE5'

Highlighted
Helper IV
Helper IV

Re: Please Help: Trying to encode/decode Access Token (Rest API)

@artemus, thank you for the reply!  I tried that and same issue.  Honestly, inheirted this code, not truly entirely sure what all should be there.  Kind of learning along the way...  Although, something about this is jacking up with the request.  If I try just removing the conversion all together (and send body), it says it is unable to convert to binary?!?!?  Not sure why it is even trying to do so...

Highlighted
Microsoft
Microsoft

Re: Please Help: Trying to encode/decode Access Token (Rest API)

How about just removing the 2nd and 3rd parameter. If you want to see what PowerBi is sending you can just create a new query with:

 

Text.FromBinary(<code after Content=>)

 

and then put that into your favorate base64 decoder.

 

As for removing it all, Content expects a binary type, not a text type.

 

As to what should be there is described here: https://docs.microsoft.com/en-us/powerquery-m/web-contents

 

Highlighted
Helper IV
Helper IV

Re: Please Help: Trying to encode/decode Access Token (Rest API)

Removing the 2nd and 3rd parameters results in the same issue.  It says Bad Request.  

 

Is there something about my code verse the Post-Man code that is different?  I'm just trying to follow why it would work in PostMan, but not here.  PostMan doesn't mention anything about converting it to Binary (although, I trust you when you say it just expects it there).

Highlighted
Microsoft
Microsoft

Re: Please Help: Trying to encode/decode Access Token (Rest API)

Might be an issue with the password using a special character.

 

Try:

 

body = Uri.BuildQueryString(
[
   Username="VALUE1",
   Password="VALUE2",
   grant_type="password",
   Integrated="N",
   database="VALUE3",
   Client_Id="VALUE4",
   client_secret="VALUE5"
])

 

 

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors