none
M Language rest api multiple querys for total records RRS feed

  • Question

  • Hi

     

    I have a rest api query  that returns me a maximum number of 500 records. I can also get with the api the total number of records.

     

    Because of that I need to merge several queries, to get all my records. Also I need that query to be dynamic and always up to date with the total records.

     

    This is an example code for maximum 1000 records:

     

    let
        Source = Json.Document(Web.Contents("https://example.pipedrive.com/v1/deals:(id,title,value,currency)?api_token=12345&limit=500&start=0")),
        Source2 = Json.Document(Web.Contents("https://example.pipedrive.com/v1/deals:(id,title,value,currency)?api_token=12345&limit=500&start=500")),
        data = Source[data],
        data2 = Source2[data],
        #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Converted to Table 2" = Table.FromList(data2, 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"}),
        #"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "title", "value", "currency"}, {"Column1.id", "Column1.title", "Column1.value", "Column1.currency"}),
        #"Appended Query" = Table.Combine({#"Expanded Column1", #"Expanded Column2"})
    in
        #"Expanded Column1"

    My total records are about 11000 and are changing every week.

     

    I can get the total number of records using data.additional_data.total_records.

     

    Can I use this value on a variable, and use some sort of a while, to make a total of querys of (total records / 500)?

     

    Any Ideas?

    Thank you

    Sunday, November 19, 2017 3:59 PM

Answers

All replies