Iterations in API calls to ITM Platform with Excel and Power Query

When using the API, sometimes you have to make a call for each record from a previous query. For example:

1. Getting the project list
2. To get the budget for each project, you will need to get all project budgets from the list above

Here is the conceptual schema of the calls:

Step 1: Identifying methods and basic access to the API

For everything about basic access to the ITM Platform API, you can refer to the first part of this guide: In the case of the example used in this article, it is necessary to use three methods. The first two had already been used to access ITM Platform and generate the project list.

1. Authentication
2. Budget for each project
3. Based on the query in the previous article, only the ProjectID and ProjectName columns are selected. The result is a correctly identified list of projects.

To verify that the query is correct, the advanced editor should display code with a structure similar to that of the example:

let
    myToken = Json.Document(Web.Contents(«https://api.itmplatform.com/company/login/elon.musk@itmplatform.com/12345»)),
    Source = Json.Document(Web.Contents(«https://api.itmplatform.com/company/projects/?ProjectStartDate=lt:2017-05-08T00:00:00Z&ProjectEndDate=gt:2017-05-06T00:00:00Z», [Headers=[token=myToken[Token]]])),
    #»Converted to Table» = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #»Expanded Column1″ = Table.ExpandRecordColumn(#»Converted to Table», «Column1», {«ProjectID», «ProjectName»}, {«ProjectID», «ProjectName»}),
in
    #»Expanded Column1″

Step 2 – Create the second query and make it a function

The second query in the example retrieves the detailed budget for each project.
The query is made according to the same procedure, with a significant difference: the URL varies by call:
https://api.itmplatform.com/company/projects/{ProjectID}/budget
In other words, you have to replace the value of the project with its value: the ID of each project. To do this automatically, you need to create a new query and transform it into a function. The syntax is very similar.
Note: Before proceeding, you should change the privacy level of the current workbook to “none” in the privacy options of the Power Query options. Otherwise, there might be errors when combining queries.

1. Creating a blank query
2. Creating the function. See syntax:

   (p_id) =>
   let
       myToken = Json.Document(Web.Contents(«https://api.itmplatform.com/company/login/elon.musk@itmplatform.com/12345»)),
       Source = Json.Document(Web.Contents(«https://api.itmplatform.com/company/projects/»& Number.ToText(p_id) & «/budget», [Headers=[token=myToken[Token]]]))
   in
       Source
   

Differences with generic queries:

• at the beginning it opens with:

  (p_id) =>

  This formula indicates that a function will replace Number.ToText(p_id) in each URL it generates with the project ID.

• When you save the query, the system indicates that it is a function. In this case, it is named “Budget”

Step 3 – Join the above steps

Once the function is created, you need to go back over the initial query to:

• Add a column as a custom function
• Choose the newly created feature
• Choosing the ProjectID column as a parameter

The rest of the steps are the usual:

• Expand the columns to select the ones that are of interest
• Add convenient custom columns
• Save and use any of Excel’s data exploitation media, such as PowerPivot.
  The example added two custom fields:
• Total Budget
• Actual cost
  The two fields have been compared to a bar chart.

Note: If the result of the first table contains many records, calls to the second query may be delayed, as many qeries will be made as there are records. Try to narrow your search to what interests you.