Making multiple API calls with PowerQuery

[mav353]

I have a list of a few thousand company IDs from OpenCorporates and I'd like to use their API to return officers of those companies. I want to do this in excel with PowerQuery, and I've been successful using the From Web function to retrieve the data for one call at a time. However, I can't figure out how to loop over my list of companies to make a few hundred calls at a time.

Does anyone have experience with making multiple API calls in PowerQuery? So far my forum searching hasn't yielded productive results. I'm pretty inexperienced with M but have been successful modifying pre-existing code to fit my purposes in the past.

I've been using the following 3 calls as examples:

https://api.opencorporates.com/companies/gb/07444723 https://api.opencorporates.com/companies/us_fl/F06000000438 https://api.opencorporates.com/companies/us_fl/P14000045784 Thanks for your help!

 

[horseyride]

I'd probably do something like converting your API call into a function

(Item as text) =>
let
Source = Json.Document(Web.Contents("https://api.opencorporates.com/companies/" & Item)),
results = Source[results],
company = results[company],
officers = company[officers],
#"Reversed List" = List.Reverse(officers),
#"Reversed List1" = #"Reversed List"{0},
officer = #"Reversed List1"[officer],
name = officer[name]
in
name

then importing Table1, header row column name = List, containing one column and one row per company IDs (here "gb/07444723" or "us_fl/F06000000438") and inserting a column that calls the function for each row/ID

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each WebQuery(

• ))
  in
  #"Added Custom"