Creating a nested REST Request

[Eitum]

Hi there,

I'm quiet new to this topic and I'm currently playing around to get some data per REST API into excel.

I choose  the GW 2 API to play around and this is where my Problems start. 

If you read the information from the materials you receive a Material ID and an amount.

This works:

    // Schritt 1: ID-Liste abrufen
    Quelle = Web.Contents("https://api.guildwars2.com/v2/account/materials?access_token="API-Key""),
    JSONData = Json.Document(Quelle),
    #"In Tabelle konvertiert" = Table.FromList(JSONData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Erweiterte Column1" = Table.ExpandRecordColumn(#"In Tabelle konvertiert", "Column1", {"id", "count"}, {"ID", "Anzahl"}),

Now I want to use the List of IDs to request for each ID the material specific information:

    IDList = #"Erweiterte Column1"[ID],
    Items_URL = "https://api.guildwars2.com/v2/items", //Web Url 
    QuelleItems = Json.Document(Web.Contents(Items_URL, [Query = [ids = IDList]])), 

    // number represented by a string 
    ListOfRecords = QuelleItems [results]

and add the end I want a table with the combined informations from these two requests. 

I tried several approches, non worked. 

Can someone point me please to the right direction?

[123abc · Accepted solution]

To achieve your goal of combining information from two REST API requests into a table in Excel Power Query, you can use Power Query's capabilities to perform a nested request and merge the results. Here's a step-by-step guide:

1. Get the List of Material IDs:
- You've already obtained the list of Material IDs in your first step. Make sure you have a table that contains these IDs in a column named "ID."

2. Define a Function to Get Material Information:
- In Power Query, you can define a custom function that takes an ID as a parameter and returns the material information for that ID. Go to the "View" tab and click on "Advanced Editor." Here's an example of how you can define the function:

Powerquery
let
GetMaterialInfo = (ID) =>
let
Items_URL = "https://api.guildwars2.com/v2/items",
QuelleItems = Json.Document(Web.Contents(Items_URL, [Query = [ids = Text.From(ID)]])),
MaterialInfo = QuelleItems{0}
in
MaterialInfo
in
GetMaterialInfo

3. Use the Function to Get Material Information for Each ID:
- Apply the custom function you defined in step 2 to each ID in your list. This will create a list of material information records.

Powerquery
MaterialInfoList = List.Transform(#"IDList"[ID], each GetMaterialInfo(_))

4. Convert the List of Records into a Table:
- You now have a list of records, and you can convert it into a table.

Powerquery
MaterialInfoTable = Table.FromRecords(MaterialInfoList)

5. Combine Information with the Original Table:
- Combine the material information table with the original table that contains the IDs and counts.

Powerquery
CombinedTable = Table.CombineColumns(#"Erweiterte Column1", {"ID"}, MaterialInfoTable, {"id"}, JoinKind.LeftOuter)

This step ensures that you combine the information based on the "ID" column.

6. Result:
- At this point, you should have a table (CombinedTable) that contains the combined information from the two requests.

The key to making this work is defining a custom function to retrieve material information for a single ID and then using List.Transform to apply that function to each ID in your list. This process enables you to perform a nested REST API request and merge the results into a single table.

Make sure to adjust the column names and details as needed based on the actual structure of the API responses and your desired output.