best way to use multiple json queries with M for total records

[davser]

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?

(Sorry the code is making a smiley face it's a ":" and a "(" )

Thank you

[Eric_Zhang · Accepted solution]

---

@davser wrote:

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?

(Sorry the code is making a smiley face it's a ":" and a "(" )

Thank you

---

@davser

Could you try below M code? It is to generate starts 0, 500, 1000, 1500 etc according to the total rows and add a custom column by appending the starts to the API url.

let
    Source =  11001, //the total value from a total rows api?
    Starts = List.Generate(()=>0, each _ < Source, each _ + 500),
    #"Converted to Table" = Table.FromList(Starts, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Json.Document(Web.Contents("https://example.pipedrive.com/v1/dealsSmiley Sadid,title,value,currency)?api_token=12345&limit=500&start="&[Column1])))
in
    #"Added Custom"

[paulob_involves]

Using this solution is it possible to schedule web refresh of data? I have used a similar approach, and after published in the web I can't schedule refresh due to credentials error, and Power BI won't accept "Anonymous" on credentials option.

[Eric_Zhang]

@paulob_involves

The drawback is not able to refresh, per my test.

[davser]

Hi

I was trying other option using function and parameters, but that was I was to have multiple queries.

Your way everything was done in one single powerquery.

Big Thanks