Search code examples
jsonpowerbipowerquerym

M Language Multiple json queries for total results

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/deal:(id,title,value,currency)?api_token=12345&limit=500&start=0")),
    Source2 = Json.Document(Web.Contents("https://example.pipedrive.com/v1/deal:(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?

Solution

  • 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"

    GetDeal

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

    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

    list

    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"

    start text

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

    custom function

    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.