Overview
The OData tables and fields in your account, can be viewed by using the URL: https://your_tenant_name.aptem.co.uk/odata/1.0/$metadata
A number of the tables contain ‘collections’ which are not single data points, but lists of additional information – for example the Users table contains a column called “Markers_Markers” which lists all of the markers tagged against an individual learner.
In the metadata page, single data points are labelled <Property> whereas ‘collections’ are labelled <NavigationProperty>. The image below shows a screenshot of the collections available in the Users table:
How to access a collection
Excel and PowerBI both use Microsoft PowerQuery to import data. If one of the columns is a collection then the column needs to be expanded which will result in multiple rows of data for each record.
It is recommended that a separate query is used to import each collection which can then be joined to the main Users table using Lookups.
Note that collections can contain a large amount of data which generate large data tables that can take time to load into Excel and PowerBI.
Excel Version
- On the Excel ribbon, click the Data tab.
- Next, click Get Data, then click From Other Sources > Blank Query.
- This will open the Power Query Editor and will display a blank query.
- Paste and save the relevant sample query from the examples below.
PowerBI Version
- On the Power BI Desktop ‘Home’ ribbon. Click Transform Data.
- Click New Source > Blank Query.
- This will open the Power Query Editor and will display a blank query
- Paste and save the relevant sample query from the examples below.
Sample Queries
All the queries below are designed to expand the relevant ‘collection’ whilst mapping it against an Id field that can be used to cross-reference the data with other information.
Each query works by importing the Id field and the collection, which is then expanded. The first step is to import the relevant columns (for example, in the Users table there will be one Evidences record per learner).
In the data preview mode you will see this icon . Manually clicking on the icon will expand the data into multiple rows – for example, one row for each evidence for each user. The number of rows will depend on the number of items in the collection.
Note: Clicking on the expand icon has the same action as using the Expand option on the Transform ribbon.
The resulting data table (image below) will contain multiple rows, and new columns containing the detailed information stored within the collection. For example, for each Evidence item collection includes: Id, Name, "SubmissionDate", the relevant"ComponentId" from the LearningPlanComponents table, compoent name, due date and latest feedback date.
Sample Query for Evidences (Users Table)
Contains a list of all evidence items submitted for for each user’s (learner’s) current Aptem programme.
This query should be used with caution as the table produced can be very large.
let
myAPI = "YOUR_API_KEY",
myTennant = "https://YOUR_TENANT_NAME.aptem.co.uk/odata/1.0" ,
Source = OData.Feed(myTennant, [#"X-API-Token"=myAPI], [Implementation="2.0"]),
Users_table = Source{[Name="Users",Signature="table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Users_table,{"Id", "Type", "UserEvidences_Evidences"}),
// only including user id , type and evidences collection,
#"Expanded UserEvidences_Evidences" = Table.ExpandTableColumn(#"Removed Other Columns", "UserEvidences_Evidences", {"Id", "Name", "SubmissionDate", "ComponentId", "ComponentOwnerId", "ComponentOwnerName", "ComponentName", "ComponentDueDate", "LatestFeedbackDate", "LatestStatus"}, {"Evidences.Id", "Evidences.Name", "Evidences.SubmissionDate", "Evidences.ComponentId", "Evidences.ComponentOwnerId", "Evidences.ComponentOwnerName", "Evidences.ComponentName", "Evidences.ComponentDueDate", "Evidences.LatestFeedbackDate", "Evidences.LatestStatus"})
in
#"Expanded UserEvidences_Evidences"
Sample Query for Secondary Owners (Users Table)
Contains a list of all secondary owners for each user’s (learner’s) current Aptem programme.
let
myAPI = "YOUR_API_KEY",
myTennant = "https://YOUR_TENANT_NAME.aptem.co.uk/odata/1.0" ,
Source = OData.Feed(myTennant, [#"X-API-Token"=myAPI], [Implementation="2.0"]),
Users_table = Source{[Name="Users",Signature="table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Users_table,{"Id", "Type", "UserLearningPlanDetails_SecondaryOwners"}),
#"Expanded UserLearningPlanDetails_SecondaryOwners" = Table.ExpandTableColumn(#"Removed Other Columns", "UserLearningPlanDetails_SecondaryOwners", {"Id", "FirstName", "LastName"}, {"Owner.Id", "Owner.FirstName", "Owner.LastName"})
in
#"Expanded UserLearningPlanDetails_SecondaryOwners"
Sample Query for Group Membership (Users Table)
Contains a list of all groups assigned to each Aptem user.
let
myAPI = "YOUR_API_KEY",
myTennant = "https://YOUR_TENANT_NAME.aptem.co.uk/odata/1.0" ,
Source = OData.Feed(myTennant, [#"X-API-Token"=myAPI], [Implementation="2.0"]),
Users_table = Source{[Name="Users",Signature="table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Users_table,{"Id", "Type", "Groups"}),
#"Expanded Groups" = Table.ExpandTableColumn(#"Removed Other Columns", "Groups", {"Name", "Id"}, {"Group.Name", "Group.Id"})
in
#"Expanded Groups"
Sample Query for Markers (Users Table)
Contains a list of all markers (if any) assigned to each Aptem user.
let
myAPI = "YOUR_API_KEY",
myTennant = "https://YOUR_TENANT_NAME.aptem.co.uk/odata/1.0" ,
Source = OData.Feed(myTennant, [#"X-API-Token"=myAPI], [Implementation="2.0"]),
Users_table = Source{[Name="Users",Signature="table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(Users_table,{"Id", "Type", "Markers_Markers"}),
#"Expanded Markers_Markers" = Table.ExpandTableColumn(#"Removed Other Columns", "Markers_Markers", {"Id", "Name"}, {"Marker.Id", "Marker.Name"})
in
#"Expanded Markers_Markers"