Importing multiple pages tabular data from web

[wdvro]

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 the table is divided over several webpages (about 30), each showing 10 lines of data:

https://www.portofantwerpbruges.com/scheepvaart/cruises/aankomst-en-vertrek-van-cruises-zeebrugge?pa...

https://www.portofantwerpbruges.com/scheepvaart/cruises/aankomst-en-vertrek-van-cruises-zeebrugge?pa...

https://www.portofantwerpbruges.com/scheepvaart/cruises/aankomst-en-vertrek-van-cruises-zeebrugge?pa...

... and so on.

How can I import the data? 

[lbendlin · Accepted solution]

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 · Accepted solution]

Thanks, that worked! 

[lbendlin · Accepted solution]

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"