Importing a Webpage Table

[ipon70]

Good Morning,
I am trying to import a website that Power Bi see's with no issue, but Excel doesn't.
The site is www.skyharbor.com and the section I need to import is the "security wait times".
According to PowerBi its Table 3 according to Excel nothing exists on the page that is useful. I am at the end of my rope on how to import this, it seems like it should be simple but alas here I am and I can't get it done.

All I am really looking for in the grand scheme of things is the name of the checkpoint and the wait times.

Thanks for any help or ideas.

 

[Kerryx]

Use
https://ent-api-management.azure-api.us/avn-wait-times/raw?Key=86765a737b77411c86fa460d370fe29a
in excel Power query

Book1
	A	B
1	Column1.queueName	WaitTime.mins
2	T3 General	8
3	T4 Checkpoint A General	22
4	T4 Checkpoint B General	8
5	T4 Checkpoint C General	10
6	T4 Checkpoint D General	12
raw_Key=86765a737b77411c86fa460

Just note when loaded to PQ you get Min wait max wait and Proj wait ( which is in seconds), so you can take average of min and max or jusydivide 480 by 60 to get wait time in mins as per website display time

 

[ipon70]

Use

Use
https://ent-api-management.azure-api.us/avn-wait-times/raw?Key=86765a737b77411c86fa460d370fe29a
in excel Power query

Book1
	A	B
1	Column1.queueName	WaitTime.mins
2	T3 General	8
3	T4 Checkpoint A General	22
4	T4 Checkpoint B General	8
5	T4 Checkpoint C General	10
6	T4 Checkpoint D General	12
raw_Key=86765a737b77411c86fa460

Just note when loaded to PQ you get Min wait max wait and Proj wait ( which is in seconds), so you can take average of min and max or jusydivide 480 by 60 to get wait time in mins as per website display time
View attachment 91508

https://ent-api-management.azure-api.us/avn-wait-times/raw?Key=86765a737b77411c86fa460d370fe29a
in excel Power query

Book1
	A	B
1	Column1.queueName	WaitTime.mins
2	T3 General	8
3	T4 Checkpoint A General	22
4	T4 Checkpoint B General	8
5	T4 Checkpoint C General	10
6	T4 Checkpoint D General	12
raw_Key=86765a737b77411c86fa460

Just note when loaded to PQ you get Min wait max wait and Proj wait ( which is in seconds), so you can take average of min and max or jusydivide 480 by 60 to get wait time in mins as per website display time
View attachment 91508

Oh man this is great, seems like it works on your end. Can you step me through what you did, because I am still getting nothing back.
Its a different nothing back, but still missing the main data. I am wondering if its a limitation of our systems or something.
Thank you for this awesome solution though!!! A little more explanation would be great.

Thanks

 

[Kerryx]

In excel
go to Data->Get Data->From Web
Paste link ( above) into box then OK

then Connect

PQ opens up
Click on List

go to list Tools tab Transform and click on To Table

pop up box just click ok

xpand the Tableby click on the arrow as shown below then Ok if you want all the columns of just deselect what you dont want, if you select all you can delete later

It will load this info

I assume you can manage the rest but here is the PQ code

let
    Source = Json.Document(Web.Contents("https://ent-api-management.azure-api.us/avn-wait-times/raw?Key=86765a737b77411c86fa460d370fe29a")),
    current = Source[current],
    #"Converted to Table" = Table.FromList(current, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"airportCode", "airportName", "queueId", "queueName", "projectedWaitTime", "projectedMinWaitMinutes", "projectedMaxWaitMinutes", "localTime", "time"}, {"Column1.airportCode", "Column1.airportName", "Column1.queueId", "Column1.queueName", "Column1.projectedWaitTime", "Column1.projectedMinWaitMinutes", "Column1.projectedMaxWaitMinutes", "Column1.localTime", "Column1.time"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"Column1.airportCode", "Column1.airportName", "Column1.queueId", "Column1.localTime", "Column1.time"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "ProjWaitTimeMinutes", each [Column1.projectedWaitTime]/60),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Column1.queueName", "Column1.projectedWaitTime", "ProjWaitTimeMinutes", "Column1.projectedMinWaitMinutes", "Column1.projectedMaxWaitMinutes"})
in
    #"Reordered Columns"

to give you this

Finally Close and Load back to Excel

 

[ipon70]

In excel
go to Data->Get Data->From Web
Paste link ( above) into box then OK
View attachment 91511
then Connect
View attachment 91512
PQ opens up
Click on List
View attachment 91513
go to list Tools tab Transform and click on To Table
View attachment 91514
pop up box just click ok
View attachment 91515
xpand the Tableby click on the arrow as shown below then Ok if you want all the columns of just deselect what you dont want, if you select all you can delete later
View attachment 91516 View attachment 91517
It will load this info
View attachment 91518

I assume you can manage the rest but here is the PQ code

let
    Source = Json.Document(Web.Contents("https://ent-api-management.azure-api.us/avn-wait-times/raw?Key=86765a737b77411c86fa460d370fe29a")),
    current = Source[current],
    #"Converted to Table" = Table.FromList(current, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"airportCode", "airportName", "queueId", "queueName", "projectedWaitTime", "projectedMinWaitMinutes", "projectedMaxWaitMinutes", "localTime", "time"}, {"Column1.airportCode", "Column1.airportName", "Column1.queueId", "Column1.queueName", "Column1.projectedWaitTime", "Column1.projectedMinWaitMinutes", "Column1.projectedMaxWaitMinutes", "Column1.localTime", "Column1.time"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"Column1.airportCode", "Column1.airportName", "Column1.queueId", "Column1.localTime", "Column1.time"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "ProjWaitTimeMinutes", each [Column1.projectedWaitTime]/60),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Column1.queueName", "Column1.projectedWaitTime", "ProjWaitTimeMinutes", "Column1.projectedMinWaitMinutes", "Column1.projectedMaxWaitMinutes"})
in
    #"Reordered Columns"

to give you this
View attachment 91519
Finally Close and Load back to Excel
View attachment 91520

Well I am glad you posted your code, because my version (crippled) didn't have some of the options you showed. But I was able to use your code and jump all the missing steps.

Thank you so so so much for this help, and I so wish there was a way to donate to people who help.

 

[Kerryx]

Glad to help but my fault for not checking your excel version