Looping one row at a time for API POST query

[CatParky]

I have written some M that takes date from a table, converts to a Json format and posts to a website (knack)

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 ?

[v-lid-msft]

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
    Source

Do 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,

[CatParky]

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
    Source

With 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 ?

[Anonymous · Accepted solution]

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

[CatParky]

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 !