Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Saving Cookies from POST Request to Rest API

Hello everyone,

 

I am attempting to save cookes from a POST request. I have done this before on Post Man but keep getting this error in Power BI:    DataFormat.Error: We reached the end of the buffer.

 

Here is my code below: 

 

let
TokenUrl = "https://baseurl.com/authentication/sign_in",
TokenOpts = "{""user"":""user123"", ""password"":""pass123""}",
TokenAct = Json.Document(Web.Contents(TokenUrl, [Headers=[#"Content-Type"="application/json"], Content=Text.ToBinary(TokenOpts)]))

in
TokenAct

 

I have have looked at other posts but nothing seems to address this scenerio. 

 

 

  • In my experience, a "we reached the end of the buffer" error indicates that the response isn't in JSON format. It's hard to debug your code since you haven't provided an actual URL, but a few questions:

    1. Are you trying to capture the token returned by the response or the cookie? I know of no way to capture the response cookie using `Web.Contents`.
    2. Can you provide an actual URL and endpoint to test?
    3. If not, can you provide a screenshot of both the working response from the Postman console and the non-working from the Inspector tab in Fiddler?

     

    To help debug, I suggest being more explicit by writing more query steps in your Power Query M code. In other words, each step in your query should contain no more than one Power Query M function. This way, you can step through and try to understand where the failure occurs.

     

    I also suggest creating a record of the contents you're sending in your POST request and using the `Json.FromValue` function to convert the record into a JSON binary format. In your case, I would define `TokenOpts` as a record and then convert that record into a JSON binary format as part of the contents in your POST request.

     

    Here's a sample Power Query M query that illustrates what I'm saying:

     

    let
        TokenUrl = "https://baseurl.com/authentication/sign_in",
        TokenBaseUrl = Text.Combine({Uri.Parts(TokenUrl)[Scheme],"://",Uri.Parts(TokenUrl)[Host]}),
        TokenRelativePath = Uri.Parts(TokenUrl)[Path],
        TokenQuery = Uri.Parts(TokenUrl)[Query],
        TokenOpts = [
            user = "user123",
            password = "pass123"
        ],
        TokenAct = 
            Web.Contents(
                TokenBaseUrl, 
                [
                    Headers=[
                        #"Content-Type"="application/json"
                    ], 
                    RelativePath = TokenRelativePath,
                    Query = TokenQuery,
                    Content = Json.FromValue(TokenOpts)
                ]
            ),
        jsonResponse = Json.Document(TokenAct)
    in
        jsonResponse

     

    My hunch is that the JSON string you've put together is malformed in some way, which is causing the API server to return an error response in some non-JSON format.

2 Replies

  • In my experience, a "we reached the end of the buffer" error indicates that the response isn't in JSON format. It's hard to debug your code since you haven't provided an actual URL, but a few questions:

    1. Are you trying to capture the token returned by the response or the cookie? I know of no way to capture the response cookie using `Web.Contents`.
    2. Can you provide an actual URL and endpoint to test?
    3. If not, can you provide a screenshot of both the working response from the Postman console and the non-working from the Inspector tab in Fiddler?

     

    To help debug, I suggest being more explicit by writing more query steps in your Power Query M code. In other words, each step in your query should contain no more than one Power Query M function. This way, you can step through and try to understand where the failure occurs.

     

    I also suggest creating a record of the contents you're sending in your POST request and using the `Json.FromValue` function to convert the record into a JSON binary format. In your case, I would define `TokenOpts` as a record and then convert that record into a JSON binary format as part of the contents in your POST request.

     

    Here's a sample Power Query M query that illustrates what I'm saying:

     

    let
        TokenUrl = "https://baseurl.com/authentication/sign_in",
        TokenBaseUrl = Text.Combine({Uri.Parts(TokenUrl)[Scheme],"://",Uri.Parts(TokenUrl)[Host]}),
        TokenRelativePath = Uri.Parts(TokenUrl)[Path],
        TokenQuery = Uri.Parts(TokenUrl)[Query],
        TokenOpts = [
            user = "user123",
            password = "pass123"
        ],
        TokenAct = 
            Web.Contents(
                TokenBaseUrl, 
                [
                    Headers=[
                        #"Content-Type"="application/json"
                    ], 
                    RelativePath = TokenRelativePath,
                    Query = TokenQuery,
                    Content = Json.FromValue(TokenOpts)
                ]
            ),
        jsonResponse = Json.Document(TokenAct)
    in
        jsonResponse

     

    My hunch is that the JSON string you've put together is malformed in some way, which is causing the API server to return an error response in some non-JSON format.

  • Any help with AWSLAB/ AWSLABCORS would be extremely helpful.   When I manually copy the generated cookies from Postman->PowerQuery my api call is successful. (see image below with hardcoded current cookies)  However these cookies constantly refresh, so I need a way to get them dynamically.   

     

    here is the sample: