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

0 votes

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?

Jan 21, 2023 in Others by Kithuzzz
• 38,010 points
289 views

1 answer to this question.

0 votes

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.

answered Jan 21, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

How to apply zoom animation for each element of a list in angular?

Hey @Sid, do check if this link ...READ MORE

answered Jul 30, 2019 in Others by Vardhan
• 13,190 points
1,193 views
0 votes
1 answer

How to increment the Range of a For Each loop - Excel VBA

Your formula seems to sum 1 single ...READ MORE

answered Jan 7, 2023 in Others by narikkadan
• 63,420 points
2,047 views
0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,420 points
1,792 views
0 votes
1 answer

Divide a range of values for each unique value

With the next available row as the ...READ MORE

answered Feb 2, 2023 in Others by narikkadan
• 63,420 points
206 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
876 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,090 points
813 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,090 points
4,063 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
3,789 views
0 votes
1 answer

Excel - Make a graph that shows number of occurrences of each value in a column

There is probably a better way to ...READ MORE

answered Oct 21, 2022 in Others by narikkadan
• 63,420 points
7,581 views
0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,420 points
1,546 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP