Homework 4: Canceled Flights

• Produce pivot tables from multiple tables.
• Use the Internal Data Model to form relationships between tables.
• Use pivot tables to explore data.
• Construct a specified chart.
• Refine your skills working with data with errors.

1. Start with This workbook
   • I started with this dataset
     • It was quite large, so I used python to remove all but the canceled flights.
     • Built the about page.
     • I removed quite a few columns.
     • I cleaned the data quite a bit.
     • Look the data over
       • FlightData represents individual flights.
       • The airport code and other airport information can be found in the Airport Worksheet.
       • Information about airlines is in the Airlines worksheet.
       • Cancellation codes and the corresponding description is in the e Cancellation worksheet.
2. Put your name on the about page.
3. [25%] Relationships.
   • Looking at the data it should be clear what the relationships are between the different worksheets.
   • Build relationships involving the Airline, Origin and Cancellation fields of the FlightData table and the other tables.
     • Remember, you need to build tables.
     • When building tables, pay attention, one does not recognize the headers.
     • Name those tables
   • Once this is complete, attempt to build a relationship based on the Destination field of the FlightData table and another table. (This will fail)
     • Find official Microsoft documentation on line discussing why this operation fails.
     • Discuss your findings on the About page.
     • Comment on Microsoft's choice of error message.
     • Include a pointer to this on the About page.
4. [25%] Build a pivot table, include the data model.
   • Label the Worksheet Part 4
   • Put Airline name (not code) in the Rows
   • Put Tail_Number in the Values
   • Put Distance in the Columns.
   • Wow, that is awful.
     • It might be better to look at flight distances in terms of 1000 miles, not miles.
     • Go to the FlightData worksheet
     • Add a column after Distance
     • Add a good title
     • Compute the integer of the distance/500
       • 500 is strange, but 1000 is too "small" and 250 is too "big"
       • 
   • Go back to the Part 4 worksheet
     • ON the PivotTable Tools Analyze special tab, in the Data command group select Refresh All
     • Note that this will add your new field to the flight data table.
     • Use the new field in the Columns area
     • 
   • I don't like those numbers however.
     • Add a new worksheet and build a table.
     • Use formulas to build the following table
     • 
     • Add the table to the relation table.
     • And refresh the pivot table.
   • Change the columns to your new distance label.
   • 
   • A little clean up.
     • Remove the row and column totals.
     • Change the headers as in the picture
     • 
5. [25%] Build a new pivot table
   • Use the data model.
   • Name the worksheet Part 5.
   • Build the following table
     • Ya, I know there is a problem. Fix it.
     • Think Star Trek, you know ... too much space.
     • 
6. [25%] Build a new pivot table that compares the hour the flight was scheduled to depart to the reason it was canceled.
   • Put this in a worksheet labeled Part 6
   • I modified the hour so there were only 24 of them. (New column)
   • Use this to build the following pivot chart. (Try to copy mine, if you are not happy with that, produce another chart.)
     • Some of these options don't make sense, like Nike, "Just Do It"
     • 
     • Axis and chart titles.
     • Commas and adjusted y axis labels
     • Data labels on the Weather line
     • Error bars on the Airline line
   • Should you avoid flights at 6:00 and 18:00? Yes or no? Why? Can you even answer that question based on this data?