Forum Discussion
Looping API calls
Hi guys,
Here's a challenge for you:
I wish to generate a list of registered customer contacts from my economy system. If i enter something similar to below i get all the contacts from customer 1.
HTTps://webpageAPI.com/customers/1/contacts
However, i have hundreds of customers. So, i need to make some kind of API call, that loops through all customers and returns all the contacts from each customer in one table. I am able to generate a list of all customer numbers, but i dont now how to get further from there.. Hope you can help me 🙂
/Nicolai
Hi NicolaiW ,
if you turn your list of customer numbers into a table, you can add a column with the following formula:Web.Contents("https://webpageAPI.com/customers/" & [PageColumName] & "/contacts")However, while this would work in Excel and Power BI Desktop, you'd get a dynamic data source error when you try to refresh this in the service.
There, you would have to modify like so:Web.Contents("https://webpageAPI.com/customers", [RelativePath= [PageColumName] & "/contacts")Check out this article for more details about it:
Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code (crossjoin.co.uk)
2 Replies
Hi NicolaiW ,
if you turn your list of customer numbers into a table, you can add a column with the following formula:Web.Contents("https://webpageAPI.com/customers/" & [PageColumName] & "/contacts")However, while this would work in Excel and Power BI Desktop, you'd get a dynamic data source error when you try to refresh this in the service.
There, you would have to modify like so:Web.Contents("https://webpageAPI.com/customers", [RelativePath= [PageColumName] & "/contacts")Check out this article for more details about it:
Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code (crossjoin.co.uk)Awesome - Thank you 🙂
