Forum Discussion
Need help consuming API data with a dynamic path
I can read data from a REST API with this URL: finance/agreements/
The resulting set returns an ID for each agreement. I can then use that ID to find additions specific to that agreement. This example shows only additions for ID 21: finance/agreements/21/additions
Is there a way to consume all of the additions using a single Power Query? I'm assuming that I will need to loop thru all of the agreements, capture the ID's, then download the additions as separate API calls.
Thank you for reading.
Yes. You can make your first web call, expand that if needed to get an [ID] column, make it type text if needed, and then add a custom column with another Web.Contents( ) with most of the url hardcoded, but dynamically use the [ID] column.
=Web.Contents("... finance/agreements/" & [ID] & "/additions")
You can then expand the resulting column of Tables to combine all the data.
Pat
2 Replies
Yes. You can make your first web call, expand that if needed to get an [ID] column, make it type text if needed, and then add a custom column with another Web.Contents( ) with most of the url hardcoded, but dynamically use the [ID] column.
=Web.Contents("... finance/agreements/" & [ID] & "/additions")
You can then expand the resulting column of Tables to combine all the data.
Pat
Thank you for the feedback mahoneypat . I took your paging example on another ticket, and used it to solve this issue. My code is below. You rock!
source = Json.Document(Web.Contents(url & "/finance/agreements/count", [ Headers = DefaultRequestHeaders ])), count = (source[count]), pages = List.Numbers(0,count/PageSize, PageSize), #"Converted to Table" = Table.FromList(pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}), #"Added Custom" = Table.AddColumn( #"Changed Type", "Custom", each Json.Document(Web.Contents(url & "/finance/agreements?fields=id&pageSize=" & Number.ToText(PageSize) & "&page="&[Column1], [ Headers = DefaultRequestHeaders ]))), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Expanded ID" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"id"}, {"id"}), #"Added Custom2" = Table.AddColumn( #"Expanded ID" , "Custom2", each Json.Document(Web.Contents(url & "/finance/agreements/"&Number.ToText([id])&"/additions?pageSize=" & Number.ToText(PageSize), [ Headers = DefaultRequestHeaders ]))), #"Expanded Custom2" = Table.ExpandListColumn(#"Added Custom2", "Custom2"),
