M Language Multiple json queries for total results

Instead of getting the data and merging the query one by one, you can parameterize the query and convert it into a function.

Let's assume your original query as follows:

let    Source = Json.Document(Web.Contents("https://example.pipedrive.com/v1/deal:(id,title,value,currency)?api_token=12345&limit=500&start=0")),    data = Source[data],    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "title", "value", "currency"}, {"Column1.id", "Column1.title", "Column1.value", "Column1.currency"})in    #"Expanded Column1"

You can parameterize the start parameter and change the query to a GetDeal function as such:

(start as text) =>let    Source = Json.Document(Web.Contents("https://example.pipedrive.com/v1/deal:(id,title,value,currency)?api_token=12345&limit=500&start=" & start)),    data = Source[data],    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "title", "value", "currency"}, {"Column1.id", "Column1.title", "Column1.value", "Column1.currency"})in    #"Expanded Column1"

Let's say you have the total_records stored as a value from another query by navigating to data.additional_data.total_records:

You can now create a new query and generate a list from 0 up to total_records, with an increment of 500:

let    Source = List.Generate(() => 0, each _ <= (total_records), each _ + 500)in    Source

Convert it to table and change the column to text:

let    Source = List.Generate(() => 0, each _ <= (total_records), each _ + 500),    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "start"}}),    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"start", type text}})in    #"Changed Type"

Now we can invoke the custom function GetDeal with the column start as :

You shall have a list of tables as a new column and can work from there. You can remove the start column as it's not needed anymore.