cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CatParky
Frequent Visitor

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

1 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

View solution in original post

4 REPLIES 4
v-lid-msft
Community Support
Community Support

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 ?

 

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

View solution in original post

CatParky
Frequent Visitor

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 !

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors