Forum Discussion

KingCanute's avatar
KingCanute
Regular Visitor
5 years ago

Reduce number of rows imported into Power BI using Html.Table

Hello All,   I am importing a data set for some training information that is available in a HTML table (from the web).   I can connect to the data and everything is working well.   The only iss...
Need Help
jennratten's avatar
jennratten
Icon for Super User rankSuper User
5 years ago

Hello - you can limit the number of rows for HTML.Table by using the Table.FirstN function, like in the example below.  However, there may be a better way to handle this depending on your script.  If you'd like to post that I can check it out.  Otherwise, the example below should help, which limits the rows in the HTML table to the first 10.

 

let
    Source = Html.Table(
            "<div class=""name"">Jo</div><span>Manager</span>", 
            {{"Name", ".name"}, {"Title", "span"}}, 
            [RowSelector=".name"]
    ),
    FirstNRows = Table.FirstN ( Source, 10 )
in 
    FirstNRows

 

KingCanute's avatar
KingCanute
Regular Visitor
5 years ago

Hi Jennratten,

 

Thanks again!

 

I did try something similar before, but the the query tries to download the entire data set before limiting it to say the FristN (10).

 

Here is the beginnig part of the M code

 

let
Source = Web.BrowserContents("https:// Web Adress"),

#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(1)"}, {"Column2", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(2)"}, {"Column3", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(3)"}, {"Column4", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(4)"}, {"Column5", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(5)"}, {"Column6", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(6)"}, {"Column7", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(7)"}, {"Column8", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(8)"}, {"Column9", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(9)"}, {"Column10", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(10)"}, {"Column11", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(11)"}, {"Column12", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(12)"}, {"Column13", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(13)"}, {"Column14", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(14)"}, {"Column15", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(15)"}, {"Column16", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(16)"}, {"Column17", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(17)"}, {"Column18", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(18)"}, {"Column19", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(19)"}, {"Column20", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(20)"}, {"Column21", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(21)"}, {"Column22", "TABLE.ReportTable.SimpleGrid > * > TR > :nth-child(22)"}}, [RowSelector="TABLE.ReportTable.SimpleGrid > * > TR"]),
#"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),