API GET Request returning {STATUS=Processing} response instead of requested data

[Anonymous]

I am trying to retrieve data using an API that first requires a POST request passing the desired table and columns as parameters in the request. This returns a JSON file with a key which I then use to make a GET request for the columns of the table specified in the POST request.

See code below:

let
    content = "{

  ""name"": ""Test"",

  ""tables"": [

    {

      ""tableName"": ""W_PAYMENT_D"",

      ""columns"": [

        ""PAYMENT_OBJECT_ID"",

        ""PROJECT_OBJECT_ID"",

		""CONTRACT_OBJECT_ID""

      ] 

    }        /*Parameter defining the table and columns to retrieve in the GET request*/

  ]

}",

options = [#"Content-type"="application/json",
          #"Authorization"="Basic <64 bit encoded username & password>"],    /*Credentials*/

Source = Json.Document(Web.Contents("https://services.texturacorp.com/ebis/api/v1/dataservice/longrunquery", [Content=Text.ToBinary(content),Headers = options])),    /*POST Request passing desired columns and authentication as parameters*/

ParsedJSON = Json.Document(Source),

key = ParsedJSON[#"LongTime running query key"],    /*Key returned from POST request*/

Data = Json.Document(Web.Contents(Text.Combine({"https://services.texturacorp.com/ebis/api/v1/dataservice/longrunquery/result/",key}),[Headers = options])),    /*GET Request using key in URL*/

data = Data[data],   /*Formatting process to convert returned JSON into table */

W_PAYMENT_D = data[W_PAYMENT_D],

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

#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"PAYMENT_OBJECT_ID", "PROJECT_OBJECT_ID", "CONTRACT_OBJECT_ID"}, {"PAYMENT_OBJECT_ID", "PROJECT_OBJECT_ID", "CONTRACT_OBJECT_ID"}),

#"Removed Blank Rows" = Table.SelectRows(#"Expanded Column1", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))

in
    #"Removed Blank Rows"

This code initially worked and when returning the variable Data, and would return the desired data in a JSON file. However, as I added more code to format the data as a table, I began getting the following error:

Expression.Error: The field 'W_PAYMENT_D' of the record wasn't found.
Details:
STATUS=Processing

"STATUS=Processing" appears to be the interim response for the API call that my query is returning for some reason. How do I ignore this interim response and return the data I am requesting?

[v-jingzhang · Accepted solution]

Hi @Anonymous 

It seems the API hasn't completed the query processing so it returns "Processing". You may try using Function.InvokeAfter in your code to recall it after some time if it returns "Processing".

This post may be helpful:

Solved: Web.Contents - Timeout=#duration - Microsoft Power BI Community

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Update: After increasing the time duration to 2 minutes in the Function.InvokeAfter parameters, I was finally able to get the data to load into PowerBI.

Thank you for your help!

[Anonymous]

I've tried using Function.InvokeAfter to recall the same GET Request. I can see the data in the Preview in Power Query, but when I click "Apply & Close" and the query runs, it once again returns STATUS=Processing. Perhaps I'm not using Function.InvokeAfter correctly. Here is my updated code:

let
    Query1 = let
    Query1 = let
    content = "{

  ""name"": ""Test"",

  ""tables"": [

    {

      ""tableName"": ""W_PAYMENT_D"",

      ""columns"": [

        ""PAYMENT_OBJECT_ID"",

        ""PROJECT_OBJECT_ID"",

		""CONTRACT_OBJECT_ID""

      ] 

    }

  ]

}",
options = [#"Content-type"="application/json",
              #"Authorization"="Basic <64 bit encoded Username:Password"],
Source = Json.Document(Web.Contents("https://services.texturacorp.com/ebis/api/v1/dataservice/longrunquery", [Content=Text.ToBinary(content),Headers = options])),
key = Source[#"LongTime running query key"],
Data = Json.Document(Web.Contents(Text.Combine({"https://services.texturacorp.com/ebis/api/v1/dataservice/longrunquery/result/",key}),[Headers = options])),
    data = Data[data],
Data2 = Function.InvokeAfter( () => (Web.Contents(Text.Combine({"https://services.texturacorp.com/ebis/api/v1/dataservice/longrunquery/result/",key}),[Headers = options])), #duration(0,0,0,15)),
    data2=Data2[data]
    
in
    Data2,
    #"Imported JSON" = Json.Document(Query1,1252)
in
    #"Imported JSON",
    data = Query1[data],
    W_PAYMENT_D = data[W_PAYMENT_D],
    #"Converted to Table" = Table.FromList(W_PAYMENT_D, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"PAYMENT_OBJECT_ID", "PROJECT_OBJECT_ID", "CONTRACT_OBJECT_ID"}, {"PAYMENT_OBJECT_ID", "PROJECT_OBJECT_ID", "CONTRACT_OBJECT_ID"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Expanded Column1", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"

I call Web.Contents for the GET Request twice, and on the second call I use Function.InvokeAfter. I also tried using Function.InvokeAfter on both GET requests (Data and Data2), and I'm still running into the same issue. 

[Anonymous]

Thank you for your response. I just tested the GET request with Postman, and it works as it should. It returns a JSON with the data requested using the POST request. I have included a screenshot of some of the API documentation that mentions "STATUS=Processing" being returned by the GET request. 

This is what gave me the suspicion that my API call in Power Query was returning an interim response rather than the actual data. It seems that other applications know to ignore this, but Power Query does not.

[PhilipTreacy]

Hi @Anonymous 

An API shouldn't provide an interim response because it would mess up calls like this.  Are you sure that's what's happening?

Have you tested the GET request with Postman?

Have you examined the JSON data that is returned to see if the formatting of the JSON has changed?

Regards

Phil