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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.