Forum Discussion

NuriaZ's avatar
NuriaZ
New Member
2 years ago
Solved

Creating multiple queries from a list of values

I hope that someone can help me.
I have an Excel sheet with a list of names golf players and their license numbers.
There's a website where I can get their game data but I have to search by license number and by game, I cannot do a query that gives me all the players data and all the games in just one go.

So. basically, I would have to create a query for each player and for each game to be able to show their results in a table.
Is there anyway that I can automate this in Power Query?

As an example

 

I have a list like this

NameLicencse
AlbertXS0001
MaryXH0035
James

XJ9882

And then, I have an url that goes like this

https://www.golfpage.com/search/license=XS001&game=23453

 

This webpage will provide the data for that particular game (game id 23453) in JSON format.

The result would be somethin like

{"code":0,"data":{"docs":[{"clubCode":"X","federatedCode":"123333","license":"XS0001","firstName":"ALBERT","surname":"SMTIH","fullName":"ALBERT SMITH","gender":"M","hcpExact":17.2,"private":{},"extra":{"photo":null,"email":"a*******@*****.com"},"__licenseInUse":false,"__isFederatedActive":true,"__isValidForActionPlay":true,"_id":"4hf3457e7433a4"}],"totalDocs":1,"offset":0,"limit":50,"totalPages":1,"page":1,"pagingCounter":1,"hasPrevPage":false,"hasNextPage":false,"prevPage":null,"nextPage":null}}

I do have to transform the data to get to the value I want and crear a table like this

NameLicencseHPC
AlbertXS000117,2
MaryXH003522,6
James

XJ9882

15,9

Is there anyway that I can do what I want

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    2 years ago

    parry2k has a good answer related to this: Calling multiple APIs based on a column

     

    Here's a simplified example I've created using a dummy API service:

    let
  Source = #table({"ID"}, {{1}, {3}, {6}}), 
  BaseURL = "https://jsonplaceholder.typicode.com/comments?postId=", 
  ID_Result = Table.AddColumn(
    Source, 
    "API_Result", 
    each Json.Document(Web.Contents(BaseURL & Number.ToText([ID]))), 
    type list
  ), 
  Extract_Field = Table.AddColumn(
    ID_Result, 
    "FirstEmail", 
    each List.First([API_Result])[email], 
    type text
  )
in
  Extract_Field

     

3 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    2 years ago

    parry2k has a good answer related to this: Calling multiple APIs based on a column

     

    Here's a simplified example I've created using a dummy API service:

    let
  Source = #table({"ID"}, {{1}, {3}, {6}}), 
  BaseURL = "https://jsonplaceholder.typicode.com/comments?postId=", 
  ID_Result = Table.AddColumn(
    Source, 
    "API_Result", 
    each Json.Document(Web.Contents(BaseURL & Number.ToText([ID]))), 
    type list
  ), 
  Extract_Field = Table.AddColumn(
    ID_Result, 
    "FirstEmail", 
    each List.First([API_Result])[email], 
    type text
  )
in
  Extract_Field

     

    • NuriaZ's avatar
      NuriaZ
      New Member
      2 years ago

      I will try to adapt your code to what I need. It looks like it is what I'm asking for. 
      I'll see if I can make it work because I'm quite new at this. 
      Thank you.

    • NuriaZ's avatar
      NuriaZ
      New Member
      2 years ago

      I have to say that it has been a bit challenging because, unlike your example, my JSON was a bit more complicated than your example, and it had to be expanded three times.
      I finally got it to work (with the help of the Power Query interface in the end) and your code was a great guideline to achieve what I needed. 
      Thank you