Forum Discussion

wdvro's avatar
wdvro
Frequent Visitor
2 years ago
Solved

Importing multiple pages tabular data from web

Hello all,   In Power BI, I want to import data concerning arrivals of cruiseships. This data has been made publicly available on a website (see below), displayed in a table. However, the data in ...
  • lbendlin's avatar
    2 years ago

    Here is a (very) pedestrian approach.  This will not work in a Power BI Service refresh scenario though. Some pages seem to be missing, too.

     

    let
        #"Table 1" = {1..30},
        #"Converted to Table" = Table.FromList(#"Table 1", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Page"}}),
        #"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "getpage", each getpage([Page])),
        #"Expanded getpage" = Table.ExpandTableColumn(#"Invoked Custom Function", "getpage", {"Aankomst", "Vertrek", "Schip", "Rederij", "Website", "Lengte", "Vlag"})
    in
        #"Expanded getpage"

     

    here is the getpage function:

    (p) => let
            Source = Web.BrowserContents("https://www.portofantwerpbruges.com/scheepvaart/cruises/aankomst-en-vertrek-van-cruises-zeebrugge?page=" & Text.From(p)),
            #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE.lg\:w-full.table-auto.\[\&_td\]\:border-none.\[\&_th\]\:border-none > * > TR > :nth-child(1)"}, {"Column2", "TABLE.lg\:w-full.table-auto.\[\&_td\]\:border-none.\[\&_th\]\:border-none > * > TR > :nth-child(2)"}, {"Column3", "TABLE.lg\:w-full.table-auto.\[\&_td\]\:border-none.\[\&_th\]\:border-none > * > TR > :nth-child(3)"}, {"Column4", "TABLE.lg\:w-full.table-auto.\[\&_td\]\:border-none.\[\&_th\]\:border-none > * > TR > :nth-child(4)"}, {"Column5", "TABLE.lg\:w-full.table-auto.\[\&_td\]\:border-none.\[\&_th\]\:border-none > * > TR > :nth-child(5)"}, {"Column6", "TABLE.lg\:w-full.table-auto.\[\&_td\]\:border-none.\[\&_th\]\:border-none > * > TR > :nth-child(6)"}, {"Column7", "TABLE.lg\:w-full.table-auto.\[\&_td\]\:border-none.\[\&_th\]\:border-none > * > TR > :nth-child(7)"}}, [RowSelector="TABLE.lg\:w-full.table-auto.\[\&_td\]\:border-none.\[\&_th\]\:border-none > * > TR"]),
            #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
            #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Aankomst", type datetime}, {"Vertrek", type datetime}},"nl")
        in
            #"Changed Type"

     

     

     

     

  • wdvro's avatar
    2 years ago

    Thanks, that worked!