Need some help using the KnowBe4 rest API and PowerBI

[msgeorgie]

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"

[Samson217]

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"

[bigdatakev]

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? 

[mahoneypat]

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

@jpwgc 

Pat

[jpwgc]

Did you ever figure this out? Trying to do the same thing. 

[V-pazhen-msft]

@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 Team