Help with filtering the KnowBe4 "training/enrollments API endpoint

Hi foxyox ,

Thank you for reaching out to the Microsoft Community Forum.

You are trying to filter the KnowBe4 training/enrollments API endpoint to only retrieve data from the last 90 days based on the enrollment_date field, but the official documentation does not mention support for such filtering directly in the API request URL. Since the API doesn’t support filtering by enrollment_date on the server side.

Instead of trying to filter in the API call, continue fetching all pages, but filter the data in Power Query after import to only keep rows from the past 90 days.

Please refer below M code to filter the data from the past 90 days.

let
Source = List.Numbers(1, 10000, 1),
PageTable = Table.FromList(Source, Splitter.SplitByNothing(), {"PageNumber"}),


AddApiCall = Table.AddColumn(PageTable, "ApiResponse", each
Json.Document(
Web.Contents(
"https://eu.api.knowbe4.com/v1",
[
RelativePath = "training/enrollments?page=" & Text.From([PageNumber]) & "&per_page=500",
Timeout = #duration(0, 16, 40, 0),
Headers = [Authorization = "Bearer <redacted>"]
]
)
)
),

ExpandEnrollments = Table.ExpandListColumn(AddApiCall, "ApiResponse"),
ExpandRecords = Table.ExpandRecordColumn(ExpandEnrollments, "ApiResponse", {
"enrollment_id", "content_type", "module_name", "user", "campaign_name",
"enrollment_date", "start_date", "completion_date", "status", "time_spent",
"policy_acknowledged", "score"
}),

ExpandUser = Table.ExpandRecordColumn(ExpandRecords, "user", {"id", "first_name", "last_name", "email"}, {"user_id", "first_name", "last_name", "email"}),

ChangeTypes = Table.TransformColumnTypes(ExpandUser, {
{"enrollment_date", type datetime},
{"start_date", type datetime},
{"completion_date", type datetime},
{"time_spent", type number}
}),


FilterRecent = Table.SelectRows(ChangeTypes, each [enrollment_date] >= DateTime.LocalNow() - #duration(90, 0, 0, 0))

in
FilterRecent

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

Thank you, I think this is the best outome considering the lack of filterability on the API endpoint - whilst it may not help with the data refresh time, it certainly keeps the report smaller and more usable.