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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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