POST and GET request via Web API. POST request is succesful, but can't perform the GET request

[rwaanders]

Dear all,

I'm trying to perform a GET request after a POST request.

The POST request returns an ID which is needed for the GET request.

The POST request works and it returns the needed ID.

When performing the GET request, I get the "We reached the end of the buffer" issue which I believe is being thrown because the file isn't ready yet.

When creating a new query and performing the POST and GET request seperately, with manually adding the ID in the GET request: the refresh fails the first time.

If I then try to refresh the page for the second time, the GET request is succesful.

I thought the GET request triggers a download which can be retrieved after it is complete, so I tried to perform a InvokeAfter based on @cwebb's blog which was unsuccesful. I added a 10 seconds duration which also returns the error on the first try and returns a succesful dataset the second refresh.

When exploring the failed refresh and opening the file as text, the file is empty after the first refresh and containts the JSON format after the second refresh.

Does anybody know how to resolve this issue?

See below code:

let

//POST request
base_url = "https://*****/api/dataviewresult/"&Dataview&"/json?",
extension = "api_key=*****",
url = base_url &extension,
header= [#"Content-Type" = "application/json"],
content1= "{}",
webdata1 = Web.Contents(url, [Headers=header,Content = Text.ToBinary(content1)]),
response1 = Json.Document(webdata1),
contents = response1[contents],
id = contents[id],

//GET request
get_data = Json.Document(Web.Contents("https://*****/api/dataviewresult/" & Dataview & "/json/" & id & "?api_key=*****")),

in

get_data

what I tried to do to invoke the GET request after 10 seconds (below code is instead of the get_data part):

let

Source = ()=> Json.Document(Web.Contents("https://*****/api/dataviewresult/***dataview-ID***/json/***ID****?api_key=*****")),

Delay = try Function.InvokeAfter(Source, #duration(0,0,0,10)) otherwise 0,

(all parts with *** have been removed)

[lbendlin]

Yes, that "Evaluating"  pause is infuriating.  My solution so far is to let it run its course, go get a coffee, read a nice book etc.  This meta data renewal is thankfully only a one time delay - well, until the next time you make drastic changes 🙂

[rwaanders]

Hmm, the query has ran for about 6 hours without any result. Is it possible to fix this or is it a lost cause?

[rwaanders]

@lbendlin I've changed my query which resolves the cache problem by looping with a function, but now I'm getting stuck on the "evaluate" part. My query loads perfectly in the query editor, but when I close & apply my query gets stuck on the "Evaluating" part. Changing the Privacy levels, ignoring the Privacy levels and disabling "Allow data previews to download in the background" don't work. Any thoughts on what I can do to fix this?

Query:

let

//Generate the ID needed for the function
id = Json.Document(Web.Contents("https://*****/api/dataviewresult/"&Dataview&"/json?api_key=*****", [Headers=[#"Content-Type" = "application/json"],Content = Text.ToBinary("{}")]))[contents][id],
//Call status function which throws back the GET request URL once successful
Source = Status_dataview(Dataview, id),
//Create function to get the data based on the previous function
DownloadFile1 = () => Json.Document(Web.Contents(Source&"?api_key=*****")),
//Call function which starts after 10 seconds
DownloadFile = Function.InvokeAfter(DownloadFile1, #duration(0,0,0,10)),
//Dataprep
#"Converted to Table" = *****
#"Expanded Column1" = *****
in
#"Expanded Column1"

Function (Status_dataview):

(Dataview as text, id as text) =>
let
//Function to get the URL once succes based on fields/parameters: Dataview, id
Data = () => Json.Document(Web.Contents("https://*****/api/dataviewresult/" & Dataview & "/json/" & id & "/status?api_key=*****"))[contents][status],
//Call function after 10 seconds
Invoke = Function.InvokeAfter(Data,#duration(0,0,0,10)),
//Loop the function each 10 seconds, until the status is successful
output =
if Invoke = "success" then Json.Document(Web.Contents("https://*****/api/dataviewresult/" & Dataview & "/json/" & id & "/status?api_key=*****"))[contents][url] else @Status_dataview(Dataview, id)

in

output

[lbendlin]

yes, but you cannot supply "r=1"  to each GET - that defeats the purpose. It needs to be a different number for each call.

[rwaanders]

I tried to add an Index and then reference to the index, like: r=Text.From([Index]). This throws a 404 error.

See attached the API documentation, which might be helpful: manual.yesplan.be/nl/developers/dataviews. I also tried Chris Webbs blog: web-contents-caching-and-the-excludedfromcachekey-option-in-power-bi-and-power-query  by adding a header, but I think I may not add headers/queries to this GET request.

Any ideas on how to tackle the caching problem? I can also send the file personally if that helps.

[lbendlin]

You may be suffering from a bad case of  caching. Add a random parameter to each of your GET requests.

...&r=<random number>

[rwaanders]

You mean like this? (just adding &r=1 for example) Should I only add it to the GET for the status? As this is the value I need ("success") for the query to proceed to the "downloading" fase.

// GET the status 10x after 5 seconds
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Function.InvokeAfter(
() => Json.Document(Web.Contents("https://*****/api/dataviewresult/" & Dataview & "/json/" & id & "/status?api_key=*****"&r=1))[contents][status],
#duration(0,0,0,[Column1]))),

[lbendlin]

If you know for sure that the second GET will work then you could call the GET twice, and use the result from the second call.

Of course it would be good to understand why exactly the first GET fails.

[rwaanders]

Thanks for you reply, I now understand why the first GET fails.

The reason is that my first POST request triggers the creation of the file.

Once the file is generated, i can perform the GET request to retrieve the file.

That's why on the second attempt, 9 out of 10 times I can get the file.

What I tried is build in a InvokeAfter function with a loop of 10 x 5 seconds (50 seconds in total), and return the status (this is a separate GET request I read in the API documentation yesterday). Once one of the rows returns "success", then the query should proceed with the original GET request to retrieve the file.

Unfortunately I keep getting the status "running", no matter if I say to run it 10 times for 5 seconds or 10 times for 1 second. But when I try the link in my browser, the status is already on "success". It looks like something is going wrong with the InvokeAfter function. My intention is that after i did the POST request, the status gets retrieved after 5 - 10 - 15 until 50 seconds. Once one of these result as "success", I can proceed to download the file.

See code:

let
//POST request
base_url = "https://*****.nl/api/dataviewresult/"&Dataview&"/json?",
extension = "api_key=*****",
url = base_url &extension,
header= [#"Content-Type" = "application/json"],
content1= "{}",
webdata1 = Web.Contents(url, [Headers=header,Content = Text.ToBinary(content1)]),
response1 = Json.Document(webdata1),
contents = response1[contents],
id = contents[id],

// WAITING table
WaitForList = List.Repeat({5}, 10),
#"Converted to Table" = Table.FromList(WaitForList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

// GET the status 10x after 5 seconds
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Function.InvokeAfter(
() => Json.Document(Web.Contents("https://*****/api/dataviewresult/" & Dataview & "/json/" & id & "/status?api_key=*****"))[contents][status],
#duration(0,0,0,[Column1]))),

//Add GET URL to check whether the ID/Dataview ID are correct
#"Added Custom2" = Table.AddColumn(#"Added Custom", "Custom2", each Json.Document(Web.Contents("https://*****/api/dataviewresult/" & Dataview & "/json/" & id & "/status?api_key=*****"))[contents][url]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom2",{{"Custom", type text}}),

// wait until the response is COMPLETE, but max 50 secs
WaitForStatus = Table.MatchesAnyRows(#"Changed Type", each [Custom] = "success"),

// download file if WaitForStatus was successful
DownloadFile =
if WaitForStatus then
Json.Document(Web.Contents("https://*****/api/dataviewresult/" & Dataview & "/json/" & id & "?api_key=*****"))
else
null

in
DownloadFile