Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
davser
New Member

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

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

1 ACCEPTED SOLUTION
Eric_Zhang
Employee
Employee


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

View solution in original post

4 REPLIES 4
Eric_Zhang
Employee
Employee


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

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.

@paulob_involves

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

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

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.