Connecting your organisation's live data to Excel via an API
Overview
Aptem is Microsoft Azure hosted and each MWS client has its own separate database. This article describes how you can connect Excel to analyse your data in Aptem.
Security and GDPR warning
This method allows you to create a file which provides direct read-only access to the live data in your Aptem system. The data in your Aptem system cannot be altered.
Anyone with access to the Excel file will have access to personal data regardless of which computer they use. The Excel file you create must only be shared with those who are authorised to use the data in your Aptem system. Not following secure procedures could result in a breach of GDPR regulations.
How to connect to your data
Create a new Excel file.
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 the new query – called Query 1.
A new query - Query1 will be seen in the ‘Queries’ pane, on your left.
Click the Advanced Editor icon in the query section of the ribbon – the Query editor box will be displayed.
Select the text in the box, and replace it with one of the sample queries at the end of this document.
You will need to edit the sample query and (1) replace the text ‘YOURTENANT’ with web address of your Aptem account (2) replace the text YOURAPIKEY with the API key provided to you.
The API key that can ONLY be generated by a member of the MWS / Aptem Admin team (such as your implementation consultant or a member of the support team). An API token will be generated for a named user in your organisation. This user must have administrator-level privileges assigned to them.
Next click Done on the Query editor.
Click Edit Credentials.
Click Connect on the pop up screen. If this fails then the key and/or tenant is incorrect.
On the query properties pane on your right, rename the query to some more useful.
By default only the first ten columns are loaded. You need to manually select the columns you need. This step is particularly necessary for Users and ILRAims tables where more than ten columns are needed.
To manually select the columns you need, click ‘Choose Columns’ on the top panel.
The Choose Columns popup screen is displayed. Select/unselect the fields as per your requirement. Click OK.
The first query is completed. In the query editor, click Refresh Preview to confirm data loads.
How to add additional data tables
For each table you wish to add, right-click on the existing query in the Queries section.
Then click Duplicate option to copy it. A new query will be shown in the list.
Under the Properties pane ‘on the right, double click Navigation.
A table will appear – simply click on the link text ‘table’ next to the required data table.
In earlier versions of Power Query, the screen looks like the one below – click on the required table then click OK.
You will then need to select relevant columns. Click Choose Columns to select the columns.
Note that if you see a “The column ## of the table wasn’t found” error, then in the Query Settings- ‘Properties’ pane, click on the cog icon to next to the ‘Removed Other columns’ line to select the relevant columns.
The Choose Columns screen will be displayed. Click OK after selecting the required column and the error will be resolved.
Repeat the steps for any additional tables.
Loading Queries into Excel
Click Close & Apply and the data will load. Each query will be loaded into a separate worksheet.
If data does not load into a worksheet, navigate to the Data tab, and click Queries & Connections.
Remember to save the Excel file.
The data you have connected in the steps above will be your organisation’s live data. Data in Excel is read-only and will not be written back to your Aptem system. If any data appears to be missing/incomplete it will be because this data has not been sufficiently entered into your Aptem system.
Sample queries
Users Table
|
let Source = OData.Feed("https://YOUR_TENANT_NAME.aptem.co.uk/odata/1.0", [#"X-API-Token"="YOUR_API_KEY"], [Implementation="2.0"]), Users_table = Source{[Name="Users",Signature="table"]}[Data], #"Removed Other Columns" = Table.SelectColumns(Users_table,{"Id", "RefNumber", "FirstName", "LastName", "FullName", "SubscriptionStatus", "Type", "UserGroups_GroupLevel0", "UserGroups_GroupLevel1", "UserGroups_GroupLevel2" , "UserILRSummary_ProgrammeStartDate", "UserILRSummary_PlannedEndDate", "UserILRSummary_ActualEndDate", "UserILRSummary_RestartDate" }), // temporarily rename the date columns #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"UserILRSummary_ProgrammeStartDate", "ILRProgrammeStartDate"}, {"UserILRSummary_PlannedEndDate", "ILRPlannedEndDate"}, {"UserILRSummary_ActualEndDate", "ILRActualEndDate"}, {"UserILRSummary_RestartDate", "ILRRestartDate"}}), // change date column types to text format
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"ILRProgrammeStartDate", type text}, {"ILRPlannedEndDate", type text}, {"ILRActualEndDate", type text}, {"ILRRestartDate", type text}}), // convert temporary date columns from text date back to date - fixes daylight saving time glitch
#"Date1" = Table.AddColumn(#"Changed Type", "UserILRSummary_ProgrammeStartDate", each Date.From(Text.Middle([ILRProgrammeStartDate], 6,4) & Text.Middle([ILRProgrammeStartDate], 3, 2) & Text.Start([ILRProgrammeStartDate], 2)), type date),
#"Date2" = Table.AddColumn(#"Date1", "UserILRSummary_PlannedEndDate", each Date.From(Text.Middle([ILRPlannedEndDate], 6,4) & Text.Middle([ILRPlannedEndDate], 3, 2) & Text.Start([ILRPlannedEndDate], 2)), type date),
#"Date3" = Table.AddColumn(#"Date2", "UserILRSummary_ActualEndDate", each Date.From(Text.Middle([ILRActualEndDate], 6,4) & Text.Middle([ILRActualEndDate], 3, 2) & Text.Start([ILRActualEndDate], 2)), type date),
#"Date4" = Table.AddColumn(#"Date3", "UserILRSummary_RestartDate", each Date.From(Text.Middle([ILRRestartDate], 6,4) & Text.Middle([ILRRestartDate], 3, 2) & Text.Start([ILRRestartDate], 2)), type date), // Now remove tmp columns #"Removed Columns" = Table.RemoveColumns(Date4,{"ILRProgrammeStartDate", "ILRPlannedEndDate", "ILRActualEndDate", "ILRRestartDate"}) in #"Removed Columns" |
Compliance Table
|
let Source = OData.Feed("https://YOUR_TENANT_NAME.aptem.co.uk/odata/1.0", [#"X-API-Token"="YOUR_API_KEY"], [Implementation="2.0"]), ComplianceDocuments_table = Source{[Name="ComplianceDocuments",Signature="table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(ComplianceDocuments_table,{"Id", "Name", "LearnerId", "ProgramName", "Status", "Date", "LearnerSignedDate", "FullySignedDate"}) in #"Removed Other Columns" |
ILR Aims table
|
let Source = OData.Feed("https://YOUR_TENANT_NAME.aptem.co.uk/odata/1.0", [#"X-API-Token"="YOUR_API_KEY"], [Implementation="2.0"]), IlrAims_table = Source{[Name="IlrAims",Signature="table"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(IlrAims_table,{"Id", "ReferenceNumber", "Name", "Level", "LearnerId", "ProgramId", "ProgramName", "AimType", "StartDate", "OriginalStartDate", "PlannedEndDate", "ActualEndDate", "ProgType", "FundModel", "FworkCode", "PwayCode", "StandardCode", "PlannedHours", "EpaCode", "Status", "Outcome", "AchievementDate", "Sof", "Res", "Act", "TotalTrainingPrice", "TotalAssessmentPrice"}) #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"StartDate", "tmpStartDate"}, {"OriginalStartDate", "tmpOriginalStartDate"}, {"PlannedEndDate", "tmpPlannedEndDate"}, {"ActualEndDate", "tmpActualEndDate"} , {"AchievementDate", "tmpAchievementDate"}}),
// convert text date back to date - fixes daylight saving time glitch
#"Date1" = Table.AddColumn(#"Renamed Columns", "StartDate", each Date.From(Text.Middle([tmpStartDate], 6,4) & Text.Middle([tmpStartDate], 3, 2) & Text.Start([tmpStartDate], 2)), type date),
#"Date2" = Table.AddColumn(#"Date1", "OriginalStartDate", each Date.From(Text.Middle([tmpOriginalStartDate], 6,4) & Text.Middle([tmpOriginalStartDate], 3, 2) & Text.Start([tmpOriginalStartDate], 2)), type date),
#"Date3" = Table.AddColumn(#"Date2", "PlannedEndDate", each Date.From(Text.Middle([tmpPlannedEndDate], 6,4) & Text.Middle([tmpPlannedEndDate], 3, 2) & Text.Start([tmpPlannedEndDate], 2)), type date),
#"Date4" = Table.AddColumn(#"Date3", "ActualEndDate", each Date.From(Text.Middle([tmpActualEndDate], 6,4) & Text.Middle([tmpActualEndDate], 3, 2) & Text.Start([tmpActualEndDate], 2)), type date), // Now remove tmp columns
#"Date5" = Table.AddColumn(#"Date4", "AchievementDate", each Date.From(Text.Middle([tmpAchievementDate], 6,4) & Text.Middle([tmpAchievementDate], 3, 2) & Text.Start([tmpAchievementDate], 2)), type date),
// Now remove temporary columns
#"Removed Columns" = Table.RemoveColumns(Date5,{ "tmpStartDate", "tmpOriginalStartDate", "tmpPlannedEndDate", "tmpActualEndDate","tmpAchievementDate" }),
in #"Removed Columns" |
FAQs
Some columns in my imported data are empty. Why?
By default only the first ten columns of an OData feed are imported. Although the column headers are visible, no data will be imported. You need to use the ‘Select Columns’ icon to edit the query for each table and select (tick) the columns which you wish to use. Click Refresh Preview’ to confirm that the data has been imported. Next, click Close and Apply.
Note that some fields or columns will be empty depending on the information your organisation collects.
Some dates from the ILR aims table are shown as a day earlier when imported into Excel/PowerBI during the summer months. Why?
When daylight saving is active on your local computer the server providing the OData feed can be in a different time-zone to the current time zone on your local computer. The result is that date-time value set at midnight can be reinterpreted as minus one hour resulting in the previous day being recorded in your excel/Power BI file. This behaviour occurs automatically in Excel and Power BI – and we have no control over it. The sample queries (Users table, ILR aims table) above overcome this issue by converting the date-time fields to text format and then returning just the correct date.