CODEWITHSUNDEEP
jsonexcelpowerbipowerquery
baruchiro
baruchiro

Reputation: 5841

Use each Power Query JSON array item as parameters to the next Query

I have some REST API endpoints, and I want to fetch a specific view of the data to Excel.

Let say I want to create a table with UserName and it Age. To get this data I need to do two requests:

GET /users will return

[
  {
    "name": "User1",
    "id": 3
  }
  ...
]

Then I need to take the name of each item in the result of GET /users for the UserName column, and for each id, I need to do the next request:

GET /users/3 will return

{
  "age": 52,
  "address": "xyz"
}

And I need to take the age and set it as the second column, right after the name from the first request.

Can I do it? How? (With Excel Power Query, of course)

Upvotes: 0

Views: 366

Answers (1)

Murray Foxcroft
Murray Foxcroft

Reputation: 13795

You need to use the each construct in PowerQuery. Rough scaffolding below:

let Source = ... ,    
Age = Table.AddColumn(Source, "AgeColumnName", each Json.Document(Web.Contents("https://example.com/api/users/" & [id]"))),
in Age

Upvotes: 1

Related Questions