Creating a nested REST Request

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.