Forum Discussion

IvanRy's avatar
IvanRy
New Member
3 years ago

Dynamic columns in web table during grabbing data

Hello!
I want to grab data from the WEB page and make a function (to change adress Source = Web.BrowserContents("https://www.biznesradar.pl/raporty-finansowe-rachunek-zyskow-i-strat/KERNEL,Q") ) from the query, but I have a problem with the step (#"Extracted Table From Html"), because a number of columns can be different, for example sometimes it can be 10, sometimes 30, etc.
{"Column14", "TABLE.report-table > * > TR > :nth-child(14)"}
How to make this part dynamic?

 

 

 

 

 

let
    Source = Web.BrowserContents("https://www.biznesradar.pl/raporty-finansowe-rachunek-zyskow-i-strat/KERNEL,Q"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.report-table > * > TR > :nth-child(1)"},
     {"Column2", "TABLE.report-table > * > TR > :nth-child(2)"}, {"Column3", "TABLE.report-table > * > TR > :nth-child(3)"},
      {"Column4", "TABLE.report-table > * > TR > :nth-child(4)"}, {"Column5", "TABLE.report-table > * > TR > :nth-child(5)"},
       {"Column6", "TABLE.report-table > * > TR > :nth-child(6)"}, {"Column7", "TABLE.report-table > * > TR > :nth-child(7)"},
        {"Column8", "TABLE.report-table > * > TR > :nth-child(8)"}, {"Column9", "TABLE.report-table > * > TR > :nth-child(9)"},
         {"Column10", "TABLE.report-table > * > TR > :nth-child(10)"}, {"Column11", "TABLE.report-table > * > TR > :nth-child(11)"}, 
         {"Column12", "TABLE.report-table > * > TR > :nth-child(12)"}, {"Column13", "TABLE.report-table > * > TR > :nth-child(13)"},
          {"Column14", "TABLE.report-table > * > TR > :nth-child(14)"}, {"Column15", "TABLE.report-table > * > TR > :nth-child(15)"}},
           [RowSelector="TABLE.report-table > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {""}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> ""))
in
    #"Filtered Rows"

 

 

 

 

 

 

2 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion

    Hi IvanRy ,
    that would look like so:


    (myNumber as text) =>

    let Source = Web.BrowserContents("https://www.biznesradar.pl/raporty-finansowe-rachunek-zyskow-i-strat/KERNEL,Q"),

    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.report-table > * > TR > :nth-child(1)"}, {"Column2", "TABLE.report-table > * > TR > :nth-child(2)"}, {"Column3", "TABLE.report-table > * > TR > :nth-child(3)"}, {"Column4", "TABLE.report-table > * > TR > :nth-child(4)"}, {"Column5", "TABLE.report-table > * > TR > :nth-child(5)"}, {"Column6", "TABLE.report-table > * > TR > :nth-child(6)"}, {"Column7", "TABLE.report-table > * > TR > :nth-child(7)"}, {"Column8", "TABLE.report-table > * > TR > :nth-child(8)"}, {"Column9", "TABLE.report-table > * > TR > :nth-child(9)"}, {"Column10", "TABLE.report-table > * > TR > :nth-child(10)"}, {"Column11", "TABLE.report-table > * > TR > :nth-child(11)"}, {"Column12", "TABLE.report-table > * > TR > :nth-child(12)"}, {"Column13", "TABLE.report-table > * > TR > :nth-child(13)"}, {"Column" & myNumber, "TABLE.report-table > * > TR > :nth-child(" & myNumber & ")"}, {"Column15", "TABLE.report-table > * > TR > :nth-child(15)"}}, [RowSelector="TABLE.report-table > * > TR"]),

    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {""}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> ""))

    in #"Filtered Rows"

     

    Just keep in mind that this would probably prevent a refresh in the service, because it's going to be considered as a dynamic data source.

  • The main problem that I don`t know how many columns should be.