Solved: Re: Help with filtering the KnowBe4 "training/enro...

foxyox

I am pretty new the "API" side of PowerBI, although have extensive experience in transforming data and building reports once they have been setup.

I am attempting to build a data model which includes data from this endpoint: "https://eu.api.knowbe4.com/v1/training/enrollments"

Without any API filters applied it takes a long time to pull back this data into PowerBI - but we only really need data where the [enrollment_date] parameter is within the last 90 days.

The KnowBe4 API documentation does not state that you can apply this particular filter to the request, so I am asking here in case anyone has achieved this or similar. Link to the documentation for this endpoint: https://developer.knowbe4.com/rest/reporting#tag/Training/paths/~1v1~1training~1enrollments/get

This is my query code currently, which I trial and errored to get working from a previous post from the users endpoint, and how to paginate successfully

let
Source = List.Numbers(1,10000,1),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each
Json.Document(
Web.Contents(
"https://eu.api.knowbe4.com/v1",
[
RelativePath= "training/enrollments?page="& [Column1] & "&per_page=500",
Timeout=#duration(0, 16, 40, 0),
Headers=[Authorization="Bearer <redacted>"]
]
)
)
),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"enrollment_id", "content_type", "module_name", "user", "campaign_name", "enrollment_date", "start_date", "completion_date", "status", "time_spent", "policy_acknowledged", "score"}, {"enrollment_id", "content_type", "module_name", "user", "campaign_name", "enrollment_date", "start_date", "completion_date", "status", "time_spent", "policy_acknowledged", "score"}),
#"Expanded user" = Table.ExpandRecordColumn(#"Expanded Custom1", "user", {"id", "first_name", "last_name", "email"}, {"id", "first_name", "last_name", "email"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded user",{{"enrollment_date", type datetime}, {"start_date", type datetime}, {"completion_date", type datetime}, {"time_spent", type number}})
in
#"Changed Type1"

v-dineshya

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

View solution in original post

foxyox

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.

View solution in original post

v-dineshya