Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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"),
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.