Forum Discussion
Creating a nested REST Request
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?
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
GetMaterialInfo3. 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.
1 Reply
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
GetMaterialInfo3. 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.