Power Query M Language Dynamically Increase Offset Value

Question

I created a web contents object within a function to query an API endpoint in power query. I use a list generate to dynamically increase the records in that list, however, I can't seem to figure out how to increase the offset by 100.

At the moment I get the first 200 records and records 100 - 200 for every API call after that. I want the offset value to increase by 100, however. At the moment it is set to 100. So it should be first API call offset 0, next offset 100, next offset 200 and so on.

Does anyone have a glue?

let
    getJournals = (page as number) =>

            let
                
                // Get Tenant ID from table

                Source = Excel.CurrentWorkbook(),
                #"Expanded Content" = Table.ExpandTableColumn(Source, "Content", {"TenantID", 
                "Client_Name", "ID"}, {"Content.TenantID", "Content.Client_Name", "Content.ID"}),
                #"Content ID" = #"Expanded Content"{2}[Content.ID],
                TenantID = #"Content ID",
                
                // Query database for access token

                tokenQuery = Sql.Database("XXX", "XXX", [Query="Select accessToken, 
                refreshToken from XXX WHERE systemName = 'XXX'"]),
                accessTokenValue = tokenQuery{0}[accessToken],
            
                          
                // initialise request data
                //authorization = "Bearer " & accessTokenValue,
                accept = "application/json",
                contentType = "application/json",
                endPoint = "https://api.xero.com/api.xro/2.0/Journals?offset="& Number.ToText(page),
                
                //Create web element

                data = Json.Document 
                        (
                        Web.Contents(
                            endPoint, 
                        [
                Headers = 
                        [
                    #"Authorization" = authorization,
                    #"Accept" = accept,
                    #"Content-Type" = contentType,
                    #"xero-tenant-id" = TenantID
                        ]
                        ]
                        )
                        )

             in
             
             data,
             increase = 100,
             allJournals = List.Generate(() => getJournals(0),
             each not List.IsEmpty([Journals]),
             each getJournals(increase)),
             #"Converted to Table" = Table.FromList(allJournals, Splitter.SplitByNothing(), null, null, 
             ExtraValues.Error)
in
#"Converted to Table"

I don't know your language, where do you change the value of `increase` so that it is anything other than 100? I see you get the journals with offset 0, then with offset `increase` (which is set to value 100) but where are you changing `increase` to be 200, 300, 400 and so on? Forgive me if that's obvious to anyone who knows the language you are using. — droopsnoot, Apr 21 '21 at 08:09
In your `List Generate`, make your initial function a Record with a counter. You can then increment the counter and change `getJournals` separately, finally extracting what you need with the 4th argument to `List.Generate`. See [Chris Webb' Blog](https://blog.crossjoin.co.uk/2014/06/25/using-list-generate-to-make-multiple-replacements-of-words-in-text-in-power-query/) for an example of using this technique in a different context. — Ron Rosenfeld, Apr 21 '21 at 12:48
@droopsnoot Yes, you are correct I'm not changing the value of increase atm. That is actually my question to make that value dynamic or somehow change the list generate logic. — Luke02, Apr 21 '21 at 22:27
@Ron Rosenfeld. Thank you for that hint. I will explore that solution! — Luke02, Apr 21 '21 at 22:35

score 0 · Answer 1 · answered Apr 21 '21 at 14:35

So the journals in Xero are numbered sequentially.

offset | Offset by a specified journal number.

e.g. journals with a JournalNumber greater than the offset will be returned.

Docs: https://developer.xero.com/documentation/api/journals

To successfully paginate through to get every single journal (they come back in batches of 100) you need to pass in the last array element's journal number until the API responds with 99 or less journals.

For example I think the demo company starts with a journal number of 344. So to get all journals you do:

> GET https://api.xero.com/api.xro/2.0/Journals
=> {"Journals": [
    {
      ...
      "JournalNumber": 344
      ...98 more
      "JournalNumber": 443
    }
  ]}

> GET https://api.xero.com/api.xro/2.0/Journals?offset=443
=> {"Journals": [
    {
      ...
      "JournalNumber": 444
      ...98 more
      "JournalNumber": 543
    }
  ]}

> GET https://api.xero.com/api.xro/2.0/Journals?offset=543
=> { "Journals": [
    {
      ...
      "JournalNumber": 544
      ...52 more
      "JournalNumber": 596
    }
  ]}

The last call has < 100 journals so you know that is the last set.

Power Query M Language Dynamically Increase Offset Value

1 Answers1