Introduction
As I was trying to find a flight I was scheduled to take, I opened a page listing all Departures from Bangkok’s Suvarnabhumi Airport (BKK). Whilst I was there, I took a look at the Arrivals page too as well as the departures and arrivals pages for Bangkok’s other airport, Don Mueang (DMK).
The purpose of this page, then, is to create a Power BI file that shows all flights departing from and arrive at the airports and that updates in real time: it does not show history, it shows the current day’s flights. In the file, I have also created a map that summarises source and destination of the day’s flights. I have also created tables showing the names of all client airports and the number of them as well as KPI style cards that show median values for numbers of arrivals and departures and changes to schedules. I have also created a column chart showing timing changes minute by minute for both arrivals and departures.
Finally, I illustrate how I created the DMK version of the file as well as an Excel version of the file.
Source Pages
As an example, here is the departures page for BKK on Sunday 3rd March 2019, as you can see, a simple, uncluttered page:
Get Data
To get these data, all I needed to do for the Departures Query was Get Data … From Web … and input the address of the departures page and then repeat that for the Arrivals Query.
Editing the Data
Overall, as I mentioned, the pages on the web are rather simple and easy to import. Take a closer look, though:
- Scheduled and Revised time Columns: that means a flight’s timing can change
- The To column shows the City and its airport code in the same cell: I wanted to show these elements separately
- The Flight column shows the airline and flight number as well as all Code Shares … there were up to 15 possible code shares on 3rd March: I felt the need to separate all of these elements from each other
- The Status column shows whether the flight is on time, departed on time or departed late: I thought it would be useful to analyse the likelihood of a change to flights from this airport.
Here is a screenshot of my Departures Query that illustrates all of the steps I took in designing it:
I created a very similar Query for Arrivals and I did so by Duplicating the Departures Query and making any changes I felt were needed: in fact, I changed nothing in this case.
This screenshot relates to Wednesday 6th March 2019 when there are 19 code shares in the table! Download and open the file, link below; and see how I did what I did to get from the web page to my detailed analysis, including the following:
Maps for BKK of Departures and Arrivals by City
Changes to Schedule by Time of Day by Departures and Arrivals
Cards Showing Median Values for Time Changes (minutes), Total Arrivals, Total Departures and so on
Tables Illustrating the Number of Flights by City, Departures and Arrivals
I have not analysed them in any way so far but Flight codes and Code Share Codes might be important for you and/or anyone travelling through BKK and DMK.
Many flights do not have a code share but some really do have 19! Just to prove the point, here is a screenshot of all 19 code share columns for 6th March 2019 and I appreciate it is difficult to read:
Creating the DMK File
This is a fabulous feature of Power BI: creating the DMK file was so simple, for this reason. All I needed to do was to change the source of the Departures Query and then the Arrivals Query by finding their URLs and copying and pasting them into my PBI file. Given that the layout of pages for both airports is the same, I needed to change nothing in the Queries and my visualisations.
All I needed to do now was to save the DMK version with a new file name: BKK_analysis.pbix became DMK_analysis.pbix
So simple!
Creating the Excel File
I am often asked if I can copy my PBI work to Excel or save the files as an xlsx file and I say no! Unless you upgrade to a fee paying version of PBI, your options are limited. Yes, you can export data from your tables and visualisations but that may be a pain if you are creating a lot of analysis. No, you CAN put everything into Excel in this way, for BKK Departures as my example:
Make sure you are happy with your work and that you have finished what you wanted to do. Save the file.
- Edit Queries
- BKK Departures
- View
- Advanced Editor
- Copy the M Language code you will now see, like this:
let
Source = Web.Page(Web.Contents(“https://www.bangkokairportonline.com/flight-status-departures/”)),
Data0 = Source{0}[Data],
#”Changed Type” = Table.TransformColumnTypes(Data0,{{“Scheduled”, type time}, {“Revised”, type time}, {“To”, type text}, {“Flight”, type text}, {“Status”, type text}}),
#”Inserted Removed Characters” = Table.AddColumn(#”Changed Type”, “Removed Characters”, each Text.Remove([Status], {” “, “-“, “D”, “a”, “d”, “e”, “i”, “l”..”n”, “p”, “r”..”u”, “y”}), type text),
#”Renamed Columns” = Table.RenameColumns(#”Inserted Removed Characters”,{{“Removed Characters”, “Status_b”}}),
#”Replaced Value” = Table.ReplaceValue(#”Renamed Columns”,”OT”,””,Replacer.ReplaceText,{“Status_b”}),
#”Changed Type1″ = Table.TransformColumnTypes(#”Replaced Value”,{{“Status_b”, Int64.Type}}),
#”Filtered Rows” = Table.SelectRows(#”Changed Type1″, each true),
#”Split Column by Delimiter” = Table.SplitColumn(#”Filtered Rows”, “To”, Splitter.SplitTextByEachDelimiter({“(“}, QuoteStyle.Csv, false), {“To.1”, “To.2″}),
#”Changed Type2″ = Table.TransformColumnTypes(#”Split Column by Delimiter”,{{“To.1”, type text}, {“To.2″, type text}}),
#”Renamed Columns1″ = Table.RenameColumns(#”Changed Type2”,{{“To.1”, “City”}, {“To.2”, “City Code”}}),
#”Replaced Value1″ = Table.ReplaceValue(#”Renamed Columns1″,”)”,””,Replacer.ReplaceText,{“City Code”})
in
#”Replaced Value1″
- Done or Cancel
- Close and Apply
Open a file in Excel
- Data
- Get Data … From Other Sources … Blank Query
- View
- Advanced View
- Delete the basic code you will already see there then Paste the M Language Code
- Done
- Name the Query
- Close and Load
Done! You now have a working copy of BKK Departures in Excel. You need to create your maps, tables and charts now, of course but how difficult was that?
Conclusions
You have seen how I build four Queries in Power BI that I can now use to interrogate Bangkok’s two international airports on a daily basis both for Departures and Arrivals. You have also seen the analysis I have carried out although I did not describe how I created the visualisations and tables.
As important as anything, though, I show you how to copy and paste the automatically generated M Language code from Power BI to Power Query in Excel without the need to do anything and without the need to upgrade to a fee paying version of Power BI.
Downloads
I am sorry but WordPress will not allow me to upload my Power BI files so here are my Excel BKK_analysis files, Departures and Arrivals versions but they have no analysis …
Duncan Williamson
6th March 2019
Excel with Excel Master 