Use PowerQuery to iterate over list of record and call an API

Question

In Excel I am trying to call an API for a list of IDs.

The list of ids are stored as a Table in Power Query:

ID
00001
00002
00003

I want to call an API:

    Source = Json.Document(Web.Contents("https://localhost:80890/data/datasets/timeseries/00001")),
    metadata = Source[metadata]
in
    metadata

which returns a table in Power Query like:

ID	MetaDataField1	MetaDataField2
00001	x	a
00002	y	b
00003	z	c

Issues:

• How do I look through the list of IDs
• Call the API subsisting the ID in the url
• Collage the results returned by the API call into one power query table

Answer 1

The short answer is a function query. To achieve that, start first by creating a new "static" query to retrieve the metadata for the first ID in your list (00001). Once this works and gives you the expected results, then post here the definition of your query and we'll make together some adjustments to your "static" query (the one retrieving just the metadata for ID 00001) in order to change it into a function that takes the ID as an input by defining it as a parameter. Finally you'll be able to call this function in a custom column and then expand it to show your MetaDataField1, MetaDataField2, etc.