Power Query: How to join queries with one query run for each row returned

Question

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.

You make it sound like you are already connecting to the the two endpoints successfully. If so, you would want to expand the results of your first API endpoint (to the point you have the ID), then add a custom column that contains the connection code used in the source of your second API endpoint. You then modify that connection string to use the ID returned from your first API. If you need a specific example that would be easy to show with some sample code posted. — Wedge
Hey @Wedge, thanks for this. I am indeed connected to the API's, I'm not able to post any of the code publicly due to some restrictions (the API in question isn't public) - do you have any resources you could suggest that might provide an overview of what you're suggesting? I'm sure they exist, I'm just not sure what language to use to describe my problem - any help would be appreciated. — Owen Henry

Wedge Wedge · Accepted Answer · 2019-07-11T18:33:31

Absent any sample code from your end, I can post a fudged up psuedo-codey version of a query where I'm using the Sharepoint REST API to get a list of folders in a directory and then get information on each of those folders.

Source = Json.Document(Web.Contents("https://sharepointstuff/Items?$Folders", [Headers=[Accept="application/json;odata=nometadata"]])),
#"Converted to Table" = DidStuffToConvertTheJsonToATable(Source),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "ID_Requests", each 
Json.Document(Web.Contents("https://sharepointstuff/getfolderdata('"& [ID_FROM_THE_TABLE] &"')?$FolderInfo", 
[Headers=[Accept="application/json;odata=nometadata"]]
)))

If you get a basic understanding of how custom columns work it should be fairly apparent how you can send a request for each ID returned from your first request. Search a combination of terms like "Power Query" + "Custom Column" + "Web Request" or "REST API" or something like that should get a lot of results about people doing things similar to this. Given that the nuances of getting data like this from different APIs can vary wildly I haven't seen a "generic" tutorial on the topic, but there are a lot of examples of people working with specific APIs that would be applicable.

Power Query: How to join queries with one query run for each row returned

1 Answers