@Samson217
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"
