Forum Discussion
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 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.
Hi ltodaro
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
5 Replies
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
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 }
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.
Hi ltodaro
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
