Forum Discussion

ltodaro's avatar
ltodaro
Regular Visitor
2 years ago
Solved

Get Data from Web using key from another table

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 w...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    2 years ago

    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's avatar
PhilipTreacy
Super User
2 years ago

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's avatar
ltodaro
Regular Visitor
2 years ago

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
}