Forum Discussion

CatParky's avatar
CatParky
Frequent Visitor
6 years ago
Solved

Looping one row at a time for API POST query

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.kna...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

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's avatar
    CatParky
    Frequent Visitor
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        CatParky
        Frequent Visitor
        6 years ago

        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 !