Forum Discussion
Need some help using the KnowBe4 rest API and PowerBI
I am attempting to pull KnowBe4 data into PowerBI and unfortunatley the data is paginated and the only variables available are page and per_page. There is no way to tell how many total records are in the dataset.
I can pull each page back individually back using the following code but wanted to see if anyone coudl help me write some code which would bring back all of the records in one query.
The code below pulls back the first 500 records. If i replace page = 1 with page = 2 it will bring back reocords 501-1000. Please advise as to write code which will bring back all of the records efficiently.
let
Source = Json.Document(Web.Contents("https://us.api.knowbe4.com/v1/users?per_page=500&page=1", [Headers=[Authorization="TOKEN"]])),
Custom1 = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
in
#"Custom1"
7 Replies
msgeorgie
Not familar with KnowBe4, you may have a look at below articles to see if they helpful to your scenario:Loop through Multiple Web Pages using Power Query
Iterating over multiple pages of web data using Power Query
Paul Zheng _ Community Support TeamDid you ever figure this out? Trying to do the same thing.
jpwgc, did you ever figure this out? I have been asked to look at doing the same in terms of getting our Knowb4 data into PowerBI
You can start with a table made from a list of numbers (for you page numbers), and then add a custom column in which you use your web call with the page number concatenated in as text. Please see this video for an example.
Power BI - Tales From The Front - REST APIs - YouTube
Pat
This isn't a perfect solution, but I got this working.
let //This will return up to 5000 users. Change the 2nd arguement in List.Numbers to query more or less users. Source = List.Numbers(1,10,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://us.api.knowbe4.com/v1", [ RelativePath= "users?page="& [Column1] & "per_page=500", Headers=[Authorization="Bearer INSERT_YOUR_TOKEN_HERE"] ] ) ) ), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"id", "employee_number", "first_name", "last_name", "job_title", "email", "phish_prone_percentage", "phone_number", "extension", "mobile_phone_number", "location", "division", "manager_name", "manager_email", "provisioning_managed", "provisioning_guid", "groups", "current_risk_score", "aliases", "joined_on", "last_sign_in", "status", "organization", "department", "language", "comment", "employee_start_date", "archived_at", "custom_field_1", "custom_field_2", "custom_field_3", "custom_field_4", "custom_date_1", "custom_date_2"}, {"id", "employee_number", "first_name", "last_name", "job_title", "email", "phish_prone_percentage", "phone_number", "extension", "mobile_phone_number", "location", "division", "manager_name", "manager_email", "provisioning_managed", "provisioning_guid", "groups", "current_risk_score", "aliases", "joined_on", "last_sign_in", "status", "organization", "department", "language", "comment", "employee_start_date", "archived_at", "custom_field_1", "custom_field_2", "custom_field_3", "custom_field_4", "custom_date_1", "custom_date_2"}), #"Sorted Rows" = Table.Sort(#"Expanded Custom1",{{"Column1", Order.Descending}}), #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each [id] <> null and [id] <> ""), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1"}) in #"Removed Columns"I've been struggeling with the same and found that there is a syntax error in the code above.
After fixing that the paginating works as described.
You should add an '&' in this line:
RelativePath= "users?page="& [Column1] & "per_page=500"which will result in this:
RelativePath= "users?page="& [Column1] & "&per_page=500"That will fix the querie and result in max 10 pages of 500 results per page.
Because of the missing '&' it did default to 10 pages of 100 results per page.
Hope this helps you (and others in the future).
For ease, below is the complete fixed code:
let //This will return up to 5000 users. Change the 2nd arguement in List.Numbers to query more or less users. Source = List.Numbers(1,10,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://us.api.knowbe4.com/v1", [ RelativePath= "users?page="& [Column1] & "&per_page=500", Headers=[Authorization="Bearer INSERT_YOUR_TOKEN_HERE"] ] ) ) ), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"id", "employee_number", "first_name", "last_name", "job_title", "email", "phish_prone_percentage", "phone_number", "extension", "mobile_phone_number", "location", "division", "manager_name", "manager_email", "provisioning_managed", "provisioning_guid", "groups", "current_risk_score", "aliases", "joined_on", "last_sign_in", "status", "organization", "department", "language", "comment", "employee_start_date", "archived_at", "custom_field_1", "custom_field_2", "custom_field_3", "custom_field_4", "custom_date_1", "custom_date_2"}, {"id", "employee_number", "first_name", "last_name", "job_title", "email", "phish_prone_percentage", "phone_number", "extension", "mobile_phone_number", "location", "division", "manager_name", "manager_email", "provisioning_managed", "provisioning_guid", "groups", "current_risk_score", "aliases", "joined_on", "last_sign_in", "status", "organization", "department", "language", "comment", "employee_start_date", "archived_at", "custom_field_1", "custom_field_2", "custom_field_3", "custom_field_4", "custom_date_1", "custom_date_2"}), #"Sorted Rows" = Table.Sort(#"Expanded Custom1",{{"Column1", Order.Descending}}), #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each [id] <> null and [id] <> ""), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1"}) in #"Removed Columns"I used this code for my query and I was able to pull 1,000 rows where I had been only getting 500. I did an export from within the KnowBe4 app of the users and I have over 5,000 in the dataset. Does anyone know how to query the full dataset?
