New query based on other query's output

[rfolkers]

I'm pretty new to Power BI, so please bear with me.

Here's the thing. I have a data feed which returns an XML-feed which I can load into Power BI, this works great. The URL I use determines which data is returned, for example:

https:/url.com/api/?apikey=THISmyKEY&service=list_clients

The URL above returns all clients. Now here comes my challenge. The URL above outputs a table with clientname and a clientID.

I want to feed all clientID's into a new URL, which will return information about that client into a new table, so that would look something like:

https:/url.com/api/?apikey=THISmyKEY&service=list_devices_at_client&clientID={all clientID's from previous URL}

I'm pretty sure that this is doable, but since I'm inexperienced, Im hitting a wall right now, so any advice or help is welcome.

[Greg_Deckler]

Well, you are probably talking a custom function here at least. In any event, what you would likely have is one query that gives you your table the way you have it now if you want that table that way. You would then copy that query and write a custom function to feed the client names to your URL and do whatever processing you want and end up with your new table. All this being said, I know that I have seen @ImkeF solve this sort of thing in other posts, so hopefully she will be along shortly to sort it all out for you.

[ImkeF · Accepted solution]

In your new table with client ID's in each row, add a new column where you combine your URL with the field with the client ID.

Like this:

=Web.Page(Web.Contents("https:/url.com/api/?apikey=THISmyKEY&service=list_devices_at_client" & [clientID]))

Where "clientID" is the name of the column with your client IDs 🙂

That will produce 1 API call per row and return the results (table) in each cell. You can then just expand that column.

[rfolkers]

Thank you very much for pointing me in the right direction and you where right that I should create a new column.

The formula I had to use was:

=Web.Page(Xml.Tables("https:/url.com/api/?apikey=THISmyKEY&service=list_devices_at_client" & [clientID]))

Thanks again, you helped me big time!