Forum Discussion
Looping one row at a time for API POST query
let
AppID = "REDACTED",
AuthKey = "REDACTED",
url = "https://api.knack.com/v1/objects/object_38/records",
TableSource = Excel.CurrentWorkbook(){[Name="TableUpload"]}[Content],
#"Demoted Headers" = Table.DemoteHeaders(TableSource),
#"Removed Top Rows" = Table.Skip(#"Demoted Headers",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{"field_417", type date}),
//Construct the POST query
Source = Json.Document(Web.Contents(url,[
Headers = [#"X-Knack-Application-Id"=AppID,
#"X-Knack-REST-API-Key"=AuthKey,
#"Content-Type"="application/json"],
Content = Json.FromValue(#"Promoted Headers"{0})
]
))
in
Source
The recieving website will only accept one row at a time so how can I iterate through x number of rows to send the payload
Additionally, how can I refresh this query when it doesn't need to be outputted to a table ? If it's connection only I can't refresh it ?
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
4 Replies
Hi CatParky ,
We can create a index column and use addcolumn function to call api for each row:
let AppID = "REDACTED", AuthKey = "REDACTED", url = "https://api.knack.com/v1/objects/object_38/records", TableSource = Excel.CurrentWorkbook(){[Name="TableUpload"]}[Content], #"Demoted Headers" = Table.DemoteHeaders(TableSource), #"Removed Top Rows" = Table.Skip(#"Demoted Headers",1), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{"field_417", type date}), AddIndex = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), //Construct the POST query Source = Table.AddColumn(AddIndex,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(#"Promoted Headers"{[Index]}) ] )) in SourceDo you want to call the api every time the report scheduled and do not want to show the result of each call in tables?
Best regards,Thank you so much, this really helped ! I had to make some minor changes as Table.AddColumn was missing a the New Column Name and I added another closing bracket ... but it works !
For reference (waves at me in the future) here's the working code
let AppID = "REDACTED", AuthKey = "REDACTED", url = "https://api.knack.com/v1/objects/object_38/records", TableSource = Excel.CurrentWorkbook(){[Name="TableUpload"]}[Content], #"Demoted Headers" = Table.DemoteHeaders(TableSource), #"Removed Top Rows" = Table.Skip(#"Demoted Headers",1), #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{"field_417", type date}), AddIndex = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), //Construct the POST query Source = Table.AddColumn(AddIndex,"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(#"Promoted Headers"{[Index]}) ] ))) in SourceWith regards to running the query, I don't need to see the record data - so is it something I can create with a macro button push ?
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
