I'm new to Power Query, and I'm having the following issue:
I have two separate API endpoints that I'm pulling data from. Both are returning data in JSON via PowerQuery, formatting it as a table, and outputting it into sheets.
The first query pulls down a list of all entities of a specific type - let's pretend they're People. For each record, I have some metadata and an ID #. It looks like this:
{
"Records":
[ "type" : "Person", "name" : "Joseph Joestar", "parent" : null, "ID" : "JoJo1", "url": "https://person.data/v1/JoJo1"]
}
The second query connects to a second API endpoint (listed as a url in the first query), which gives me more information about a specific individual. In this query, I need to pass the ID # as part of the GET to pull down additional information, which returns the following:
{ "Person" :
[ "ID" : "JoJo1",
"name" : "Joseph Joestar",
"History" : ["history1" : "Born in England", "event2": "Fought Dio the vampire", "event3": "Died en route to America"]
]
}
What I'm trying to do is create a table where I run the second query for each ID # returned in the first query, and then display the history of each entity as its own row. Final output for each excel row might look something like:
Joseph Joestar - JoJo1 - Born in England
Joseph Joestar - JoJo1 - Fought Dio the Vampire
Joseph Joestar - JoJo1 - Died en-route to America
I'm not sure how to put these queries together in this manner, or if it's even possible in Power Query. Does anyone have any suggestions for features to use or an approach to take? Happy to do any self-teaching that's necessary, really just need to be pointed in the right direction.