Multiple pages from website into one excel sheet

[lynzlou]

I have been trying to import data from a website into power query (or one excel worksheet). The data I need extends over 5 "pages"

http://flyeia.com/flights/departures#sm.0000ysr85e9vnctez8n2h6y6ywlq0
http://flyeia.com/flights/departures?page=1#sm.0000ysr85e9vnctez8n2h6y6ywlq0
http://flyeia.com/flights/departures?page=2#sm.0000ysr85e9vnctez8n2h6y6ywlq0

and so on (usually 5-7 pages). I tried using a formula in power query like

(page as number) as table =>
let
Source = Web.Page(Web.Contents("http://flyeia.com/flights/departures#sm.0000jr36lyj9nd66r981iljc7caia")),
Data0 = Source{0}[Data]
in
Data0

but all that does is copy the first page over and over and over again. Is there a way I can do get all 5 (or 7) pages of data into one worksheet?? in a perfect world, I would be able to refresh this data in excel as the flights are delayed, canceled or depart as I pull this data 2x per shift and right now copy and paste each page into excel (there must be a better way!!!)

 

[citizenbh]

You need variable for Pages
In Power Query use function as

let
    Source =  Web.Page(Web.Contents("http://flyeia.com/flights/departures?page="&  Number.ToText(page)&"#sm.000000dsuvhuayud9kxdrhkb7277b")),
    Data = Source{0}[Data]
in
    Data

and in new Query insert code (for 5 pages):

let


    Source = {0..4},
    ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Renamed = Table.RenameColumns(ToTable,{{"Column1", "Page"}}),
    Added = Table.AddColumn(Renamed, "Custom", each GetData([Page])),
    #"Expanded Custom" = Table.ExpandTableColumn(Added, "Custom", {"Flight", "Airline", "To", "Scheduled Time", "Revised Time", "Status", "Date", "Gate"}, {"Flight", "Airline", "To", "Scheduled Time", "Revised Time", "Status", "Date", "Gate"})
 in
     #"Expanded Custom"

That is all

 

[lynzlou]

I don't know what I am doing wrong but I just keep getting this error


Expression:Error: The import page matches no exports. Did you miss a module reference?

then if I click Edit Settings it says:

The name "page" wasn't recognized. Make sure it's spelled correctly.

 

[gazpage]

Citizenbh has created a parameter called “home”, which it looks like you haven’t.

Manage parameters in the ribbon and create a number parameter called “page”

 

[lynzlou]

ok well that will take some googling... beginning to think I will stick with copy and paste LOL

 

[lynzlou]

I think I got it figured out other than I can see the data in power query but when doing Load and Close it keeps timing out from website...

 

[citizenbh]

Name of function (1. query) is GetData.

 

[lynzlou]

I feel like I am SOOO close but I keep getting a HTTP connection timeout after 100 seconds error when I try to actually load the data into Excel

 

[citizenbh]

Execute query. Wait a minute. Then, on the Home ribbon and Data source settings, Clear Permissions for http://flyeia.com. The data is uploaded to me after that. Sorry to my english!

 

[citizenbh]

For GetData function in Web.Contents, you can pass in the timeout as part of the parameter's options:

(page as number) as table =>
let
    Source = Web.Page(Web.Contents("http://flyeia.com/flights/departures?page="& Number.ToText(page),[Timeout=#duration(0, 0, 10, 0)])),
    Data = Source{0}[Data]
in
    Data

where is the timeout 10 minutes.