Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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
Thank you @smpd !
Do you have an exemple of code using List.Generate to produce a loop?
Thank you
Raphaël
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.