Attempting my first import to power bi using a JSON Rest API and am now trying to use pagination loops to get over the 1000 row limit that I have hit. What i seem to have accomplished though is to make the query count the number of pages and then cycle through the first set of 1000 rows for the number of pages - obviously creating duplicates.
Can anyone offer any advice - code below:
let
BaseUrl = "THISISAURL/client/?page_size=1000",
Token = "Token THISISAKEY",
EntitiesPerPage = 1000,
Options = [Headers=[Authorization="Token THISISAKEY" ]],
Url = BaseUrl,
GetJson = (Url) =>
let
Json = Json.Document(Web.Contents("THISISAURL/client/?page_size=1000", [Headers=[Authorization="Token THISISAKEY"]]))
in Json,
GetEntityCount = () =>
let Url = BaseUrl & "$count=true&$top=0",
Json = GetJson(Url),
Count = Json[count]
in Count,
GetPage = (Index) =>
let Skip = "$skip=" & Text.From(Index * EntitiesPerPage),
Top = "$top=" & Text.From(EntitiesPerPage),
Url = BaseUrl & Skip & "&" & Top,
Json = GetJson(Url),
Value = Json[results]
in Value,
EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
#"Converted to Table" = Table.FromList(Pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "url", "title", "firstname", "lastname", "organization_name", "customer_type", "vat_number", "register_number", "home_department", "due_date_delay", "street_address", "street_address_2", "street_address_3", "zip_code", "city", "state", "email", "alt_emails", "id_number", "old_client_id", "critical_notes", "critical_accounting_notes", "remarks", "archived", "country", "no_sms", "no_email", "external", "referring_organization", "parent_referring_organization", "referring_vet", "imported", "date_imported", "patients", "invoicing_client", "tags_rel", "created", "created_user", "modified", "modified_user", "phone_numbers", "status_type", "fields_rel", "farm_code", "holdingplacenumbers", "communication_preferences"},{"id", "url", "title", "firstname", "lastname", "organization_name", "customer_type", "vat_number", "register_number", "home_department", "due_date_delay", "street_address", "street_address_2", "street_address_3", "zip_code", "city", "state", "email", "alt_emails", "id_number", "old_client_id", "critical_notes", "critical_accounting_notes", "remarks", "archived", "country", "no_sms", "no_email", "external", "referring_organization", "parent_referring_organization", "referring_vet", "imported", "date_imported", "patients", "invoicing_client", "tags_rel", "created", "created_user", "modified", "modified_user", "phone_numbers", "status_type", "fields_rel", "farm_code", "holdingplacenumbers", "communication_preferences"} ),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column2",{{"id", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"id", Order.Ascending}})
in
#"Sorted Rows"
