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:


        Source = Json.Document(Web.Contents(",title,value,currency)?api_token=12345&limit=500&start=0")),
        Source2 = Json.Document(Web.Contents(",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.title", "Column1.value", "Column1.currency"}),
        #"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "title", "value", "currency"}, {"", "Column1.title", "Column1.value", "Column1.currency"}),
        #"Appended Query" = Table.Combine({#"Expanded Column1", #"Expanded Column2"})
        #"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


All replies