Resource Allocation API Data Extraction Made Simple

If resource allocation data is to be used as single column data fields, use Excel or other data tools to get only what you need in the Work Hours column for each resource without the detail in the roll-up data. We’ll use Excel as our example.

We’ll use our API token along with the following query string below to retrieve the unformatted data for all resources with weekly allocations in November of 2019.

https://foo.projectinsight.net/api/resource-allocation?StartDateLocal=11/3/2019&EndDateLocal=12/07/2019&TotalWeekly

Power Query in Excel should look like this.

You will retrieve Total, Children and Weeks into your Power Query Editor.

We want to get the Children: List by clicking List above.

The example has two Records, which are Resources. Convert these records To Table

In the Column 1 header you will see the option to select data to display.

We will choose Object Name and Weeks

We now get to choose the Column.Weeks1 to expand the data for each week in our range.

Choose Expand to New rows.

The Resources are now expanded to one row for each week.

Choose to expand the data from Column1.Weeks again and you will see new options.

Use the Load More link to expand out for selection of Date, WorkHours and Planned Value

The raw data for resources each week will look like the following with the availability of the familiar tools to format, calculate, merge, etc. in Excel.