Forum Discussion
Looping one row at a time for API POST query
Hi CatParky & v-lid-msft ,
As the POST need to be send for every row, I think, this may be a bit optimized code. It avoids indexing and referencing operations. It also may help to avoid possible performance penalty on multiple resolutions of "Promoted Headers" (which is unlikely or minimal in your case, but I guess possibly significant in some other scenarios):
... #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]), //Construct the POST query Source = Table.AddColumn(#"Promoted Headers","RecordData", each Json.Document(Web.Contents(url,[ Headers = [#"X-Knack-Application-Id"=AppID, #"X-Knack-REST-API-Key"=AuthKey, #"Content-Type"="application/json"], Content = Json.FromValue(_) ])))From what I know, the code MUST return something (to your Excel file/table), even as simple as the rowcount on the Source. Otherwise PQ will not bother to run the code on refresh.
Kind regards,
JB
Hi CatParky & v-lid-msft ,
As the POST need to be send for every row, I think, this may be a bit optimized code. It avoids indexing and referencing operations. It also may help to avoid possible performance penalty on multiple resolutions of "Promoted Headers" (which is unlikely or minimal in your case, but I guess possibly significant in some other scenarios):
...
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
//Construct the POST query
Source = Table.AddColumn(#"Promoted Headers","RecordData",
each Json.Document(Web.Contents(url,[
Headers = [#"X-Knack-Application-Id"=AppID,
#"X-Knack-REST-API-Key"=AuthKey,
#"Content-Type"="application/json"],
Content = Json.FromValue(_) ])))
From what I know, the code MUST return something (to your Excel file/table), even as simple as the rowcount on the Source. Otherwise PQ will not bother to run the code on refresh.
Kind regards,
JB
Thank you for the advise, it works fantastically ... I'm now going to try and work out how 😄
As for the refreshing, I've decieded to reference the connection only POST query as a new result query. Now my users can run the macro which will create the result table for validation and in turn causes the connection only query to refresh to build the table !