get data from web which some column is display:none

[Theocritus]

I need to get a table from web page.

On that page there are javascript buttons switch th and td tag style to display:table-cell or display:none.

Every data is in the html but Power BI Desktop seems just ignore those display:none columns.

Is it possible to fetch those display:none cloumn?

[Anonymous]

Hi Theocritus,

can you post this web page? So I can try to help you to fix this problem.

[Theocritus]

http://www.cpbl.com.tw/web/team_playergrade.php?&gameno=01&team=A02&year=2014&grade=1&syear=2014

[pqian · Accepted solution]

 Hey,

I'm sorry but the requirement for the element to be visible is hardcoded into the Web.Page function. Looking at the web page, it doesn't seem they have a way to select the first half or the second half of the table through URL input. So your only option left is to do some manual text processing. You can open the web page with Lines.FromBinary, instead of Web.Page

let
    Source = Table.FromColumns({Lines.FromBinary(Web.Contents("http://www.cpbl.com.tw/web/team_playergrade.php?&gameno=01&team=A02&year=2014&grade=1&syear=2014"))}),

Headers = let
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Column1], "<th ")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows","Column1",Splitter.SplitTextByEachDelimiter({"</"}, QuoteStyle.Csv, false),{"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter","Column1.1",Splitter.SplitTextByEachDelimiter({">"}, QuoteStyle.Csv, true),{"Column1.1.1", "Column1.1.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Column1.1.1", "Column1.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.1.2", "Value"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1)
in #"Added Index",

Values = let
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Column1], "<td align=""center""")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows","Column1",Splitter.SplitTextByEachDelimiter({"</"}, QuoteStyle.Csv, false),{"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter","Column1.1",Splitter.SplitTextByEachDelimiter({">"}, QuoteStyle.Csv, true),{"Column1.1.1", "Column1.1.2"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Column1.1.1", "Column1.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Column1.1.2", "Value"}}),
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, Table.RowCount(Headers) ), type number}}),
    #"Added Index1" = Table.AddIndexColumn(#"Calculated Modulo", "Index.1", 0, 1e-6 + 1 / Table.RowCount(Headers)),
    #"Rounded Down" = Table.TransformColumns(#"Added Index1",{{"Index.1", Number.RoundDown, Int64.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Rounded Down",{{"Index.1", "RowNumber"}})
in
    #"Renamed Columns1",

    #"Appended Query" = Table.Combine({Headers, Values}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Appended Query", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Appended Query", {{"Index", type text}}, "en-US")[Index]), "Index", "Value"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Pivoted Column")
in
    #"Promoted Headers"

This should give you a refreshable query. From there you can select all columns and hit "Detect Type" to type them.

Note the pivot hack has a limitation...It's bounded by double arithmetic accuracy (the 1e-6 weight is added to count this), at some point (I'd imagine millions of rows into the document), you'll run into a jagged row 😕 But for your purpose, this method should be sufficient. 

Regards,

PQ