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

Question

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)

Murray Foxcroft · Accepted Answer · 2020-07-14 06:26:57Z

1

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

answered Jul 14, 2020 at 6:26

Murray Foxcroft

13.9k7 gold badges66 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related