Parameter whole range or table in Excel Power Query

Question

I have an Excel sheet with a column containing IDs of items I want to retrieve
||ID||
|123|
|124|
|125|

The API I am calling can take an array of IDs as input (e.g. https://API.com/rest/items?ID=123&ID=124&ID=125....(up to 50))

and returns one JSON.

    "data": [
        {
            "id": 123,
            "fields": {
                "name": "blah blah",
                "description": "some description",
            }
        },
        {
            "id": 124,
            "fields": {
                "name": "blah bli",
                "description": "some description",
            }
        },
        {
            "id": 125,
            "fields": {
                "name": "blah blo",
                "description": "some description",
            }
        },...

    ]
}

I would like to load data from this JSON in another table or sheet.
||ID||Name||
|123|blah blah|
|124|blahblo|
|125|blahbli|

I would know how to parameterise the query by referencing single cells, but if I am retrieving 100+ items, that's a lot of work. Can't I somehow build a query that gets each value (ID) from the table or range in one simple move?

--edit1-- Found another API endpoint where I can provide an array of IDs. (before I thought it was only possible to send one ID per request to retrieve one JSON at a time)

--edit2-- Maybe I can concatenate all IDs into the request URL already in an Excel cell and parameterise just based on that one cell. (still experimenting)

Alexis Olson Alexis Olson · Accepted Answer · 2018-12-10T18:51:59

If you've got the JSON for each ID, then you just need to pull out the name part.

For example, if you have a column [JSON] with the JSON for each ID, then you can create a custom column using Json.Document([JSON])[data][name] as the formula.

You can likely combine pulling the JSON and parsing it into a single step if you'd like.

Parameter whole range or table in Excel Power Query

1 Answers