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 issue is that there are about 20,000 records and everytime I make a change to one of the existing queries, it takes ages to refresh and implement the query changes.

 

Is there any way that I can limit the number of rows that are imported at the Html.Table(Source,...level in the applied steps.

 

If this is possible, it would allow me to limit the data to say 10 rows, so that I can efficiently test the queries that I am developing.

 

Any suggestions and help would be appreciated.

 

Thanks

 

Need Help

4 Replies

  • 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]),

  • KingCanute's avatar
    KingCanute
    Regular Visitor
    5 years ago

    Hi jennratten,

     

    Just saw your message. Thanks you for responding! I will try this in the morning...my bain if fried today!

     

    Thanks again...I will report back soon

  • KingCanute's avatar
    KingCanute
    Regular Visitor
    5 years ago

    Is it possible to nest the  Table.FirstN ( Source, 10 ) code inside the Html.Table parameter? So that the numbers of rows definded is extracted?