Given a column of int64 make a REST call for each value return results to a single table

Question

I am trying to roll my own CMDB report. Given a list of asset IDs, I can pull network information for them singly, or ensemble. I would like to use Excel and Power Query to do the hard work.

The IDs are entered manually

Thus, for a list like this

ID
1001
1002
1003

I can make a single call like this

https://my.cmdb.server/RESTService.pl?method=lst_asset:arg01=1001;arg02=1002;arg03=1003;

Or I can make several calls like this

https://my.cmdb.server/RESTService.pl?method=lst_asset:arg01=1003
https://my.cmdb.server/RESTService.pl?method=lst_asset:arg01=1002
https://my.cmdb.server/RESTService.pl?method=lst_asset:arg01=1001

How can I use the input table to generate one or the other URIs and make the call/ s?

Answer 1

It would be best if you read. https://gorilla.bi/power-query/list-generate-api-calls/ But generally, create a query like this in PowerQuery and give the query call a name. It takes on a purpose. To handle the contents that are returned, modify the code as necessary. Here, I'll suppose that I return a table.

(item) as table => 
let Source = Web.Page(Web.Contents("https://my.cmdb.server/RESTService.pl?method=lst_asset:arg01="&Text.From(item))),
Data = Source{0}[Data]
in Data

Add a column, a custom column, with:

  =call([ID])

In the other table. I hope this helps you.