Forum Discussion
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...
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
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.
