Json file : How to access to the entire data?

[Raph]

Hello

I would like to import data from a json file but currently the number of imported rows is limited to 500.

Here is the current code : 

let
Source = Json.Document(Web.Contents("https://www.randomwebiste.com/commerce-sales-report.json?placed%5Bmin%5D=2020-12-01&placed%5Bmax%5D=...")),
#"Converti en table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Column1 développé" = Table.ExpandRecordColumn(#"Converti en table", "Column1", {"order_number", "placed", "ip_address", "field_isrc", "field_upc", })
in
#"Column1 développé"

We see that the current URL retrieves only the perdiod 2020-12-01 to 2020-12-31 but even on this range of dates I should have more rows.

Thank you for your help.

Raphaël

[mahoneypat]

This video shows a similar approach you can use if the API has skip or offset paramters you can use.  You could also adapt it and create a smaller range of data values on each row of a table, and then pass those into the web call.

Power BI - Tales From The Front - REST APIs - YouTube

Pat

[smpa01]

@Raphcould this be because of a pagination issue on the site? maybe the url is xyz.zom/page=1 and it pulls up everything on page 1. If that is the case you need to write a loop utilizing List.Generate so that the query crawls up to the last page to get you what you need

[Raph]

Thank you @smpd  !

Do you have an exemple of code using List.Generate to produce a loop?

Thank you

Raphaël

[smpa01 · Accepted solution]

@RaphI do.

Let's suppose your URL is

https://www.boxofficemojo.com/year/2016/?grossesOption=calendarGrosses

 you want to initiate a loop after https://www.boxofficemojo.com/year/ and dynamically pass on the year value one after another till it fails.

Your basic query is following

let
    Source = Web.BrowserContents("https://www.boxofficemojo.com/year/2016/?grossesOption=calendarGrosses"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(1)"}, {"Column2", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(2)"}, {"Column3", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(3)"}, {"Column4", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(4)"}, {"Column5", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(5)"}, {"Column6", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(6)"}, {"Column7", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(7)"}, {"Column8", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(8)"}, {"Column9", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(9)"}, {"Column10", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(10)"}, {"Column11", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(11)"}}, [RowSelector="TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Rank", Int64.Type}, {"Release", type text}, {"Genre", type text}, {"Budget", type text}, {"Running Time", type text}, {"Gross", Currency.Type}, {"Theaters", Int64.Type}, {"Total Gross", Currency.Type}, {"Release Date", type date}, {"Distributor", type text}, {"Estimated", type logical}})
in
    #"Changed Type"

  You have parameterized it to a function called tx

(p as number)=>
let
    Source= Web.BrowserContents("https://www.boxofficemojo.com/year/"&Number.ToText(p)&"/?grossesOption=calendarGrosses"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(1)"}, {"Column2", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(2)"}, {"Column3", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(3)"}, {"Column4", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(4)"}, {"Column5", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(5)"}, {"Column6", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(6)"}, {"Column7", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(7)"}, {"Column8", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(8)"}, {"Column9", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(9)"}, {"Column10", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(10)"}, {"Column11", "TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR > :nth-child(11)"}}, [RowSelector="TABLE.a-bordered.a-horizontal-stripes.a-size-base.a-span12.mojo-body-table.mojo-table-annotated.mojo-body-table-compact.scrolling-data-table > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Rank", Int64.Type}, {"Release", type text}, {"Genre", type text}, {"Budget", type text}, {"Running Time", type text}, {"Gross", Currency.Type}, {"Theaters", Int64.Type}, {"Total Gross", Currency.Type}, {"Release Date", type date}, {"Distributor", type text}, {"Estimated", type logical}})
in
    #"Changed Type"

  Now, you can initate the loop like this

let
    L = {2020..2028},
    Loop = List.Generate (
                          ()=>
                              [i=0, j= L{i}, return=try tx(j) otherwise null],
                              each [return]<>null,
                              each [i=[i]+1, j=L{[i]+1}, return = try tx(j) otherwise null],
                              each [return]
    )
in
    Loop