Get Data from Web using key from another table

[ltodaro]

I am trying to pull some project data from an API.  The problem is, the generic project API call returns very little information other than a project key, name, and some basic project status.  If I want to get specific project data, the API requires I send another GET request with the project key.   Is there any way to build a Web Data Source that uses key values from a table to pull additional data?   

https://<api>/Subscribe/Projects

returns json that includes a "Project Key":"hexhexhex-hexhehxhex"

https://<api>/Subscribe/Projects?id=hexhexhex-hexhexhex

returns json with detailed information about project with key "hexhexhex-hexhexhex"

I want to be able to make a table containing the specific detailed information from the second request, using every key from the first request.   Is there a way to do this? 

The original application does not give this information in their native reports.   I'm trying to build a work-in-progress dashboard.    

[PhilipTreacy · Accepted solution]

Hi @ltodaro 

Download example PBIX file

let
    Source = Json.Document(Web.Contents("https://www.myonlinetraininghub.com/cdn/files/response.json")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Value] <> 1)),
    #"Expanded Value" = Table.ExpandListColumn(#"Filtered Rows", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"Id"}, {"Value.Id"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Value1", "Project Info", each Web.Contents("https://<api>/Subscribe/Projects?id=" & [Value.Id]))

in
    #"Added Custom"

Assuming you are receiving the JSON like this you should end up with a List of Records that contain the Project ID's.

You can extract the records from this list, then extract the Project ID's from each record.

You can then create a Custom Column that calls the API again and uses the ID you just extracted.

regards

Phil

[PhilipTreacy]

@ltodaro 

So that JSON contains 2 different projects?

Phil

[ltodaro]

Yes, the first request will bring a list of all the projects.  Then I want to automate pulling the detail with the 2nd request. Basically I need to loop through the IDs returned by the first request to pull detailed information about each project.   

[PhilipTreacy]

Hi @ltodaro 

Yes, can you supply a sample JSON response from the API when you initially send a https://<api>/Subscribe/Projects request.  Change any private info, it's the JSON structure that I need.

From that you can extract the Project Keys and then all a function or another GET for each key to get the data you want.

regards

Phil

[ltodaro]

Here's some sample JSON from the API documentation.   The "Id" tag is the one that has the project key.  

{
  "Projects": [
    {
      "Id": "sample string 1",
      "IntegrationProjectId": "sample string 2",
      "ClientId": "sample string 3",
      "Client": "sample string 4",
      "ClientNumber": "sample string 5",
      "Name": "sample string 6",
      "Number": "sample string 7",
      "Progress": "sample string 8",
      "Approved": true,
      "CONumber": 1,
      "CurrencyCode": "sample string 10",
      "Price": 11.0,
      "ImportedOn": "2024-07-22T14:41:55.1831279+00:00",
      "PublishedOn": "2024-07-22T14:41:55.1831279+00:00",
      "Deleted": true,
      "Archived": true
    },
    {
      "Id": "sample string 1",
      "IntegrationProjectId": "sample string 2",
      "ClientId": "sample string 3",
      "Client": "sample string 4",
      "ClientNumber": "sample string 5",
      "Name": "sample string 6",
      "Number": "sample string 7",
      "Progress": "sample string 8",
      "Approved": true,
      "CONumber": 1,
      "CurrencyCode": "sample string 10",
      "Price": 11.0,
      "ImportedOn": "2024-07-22T14:41:55.1831279+00:00",
      "PublishedOn": "2024-07-22T14:41:55.1831279+00:00",
      "Deleted": true,
      "Archived": true
    }
  ],
  "TotalCount": 1
}