Module 11
Appending tables
Stack tables, align schemas and handle mismatched columns.
You'll be able to
- Append two or more tables
- Align different column names
- Predict nulls from missing columns
Lessons
- 1.Append tables and align their columnsStack tables correctly, including ones whose column names differ.
- 2.Append many tables and tag where each row came fromCombine a list of tables while keeping each row traceable to its source.
- 3.Mixed column types after an appendDetect and repair columns whose values have different types after an append.
- 4.Control the appended schemaProduce the same set of columns from an append however the inputs vary.
Take the appending tables quiz (8 questions)
Exercises
- beginner~6 minPro
Stack two months of orders (kite shop)
Append February's orders below January's to get one table.
- beginner~6 minPro
Stack two months of orders (bakery)
Append February's orders below January's to get one table.
- beginner~6 minPro
Stack two months of orders (bike rental)
Append February's orders below January's to get one table.
- intermediate~9 minPro
Append store and web sales with different column names (bookshop)
Align WebSales column names to StoreSales before appending, and see where nulls come from.
- intermediate~9 minPro
Append store and web sales with different column names (garden centre)
Align WebSales column names to StoreSales before appending, and see where nulls come from.
- beginner~7 minPro
Append three store tables and label each row's store (kite shop)
Each store sends its own orders table. Stack all three and add a Store column saying where each row came from.
- beginner~7 minPro
Append three store tables and label each row's store (bakery)
Each store sends its own orders table. Stack all three and add a Store column saying where each row came from.
- beginner~7 minPro
Append three store tables and label each row's store (bike rental)
Each store sends its own orders table. Stack all three and add a Store column saying where each row came from.
- intermediate~8 minPro
Append tables whose columns differ in order and set (kite shop)
Web orders have a Discount column, phone orders a Channel column, and their columns are in different orders. Append by name.
- intermediate~8 minPro
Append tables whose columns differ in order and set (bakery)
Web orders have a Discount column, phone orders a Channel column, and their columns are in different orders. Append by name.
- intermediate~7 minPro
Append two exports that overlap (kite shop)
Two exports cover overlapping weeks, so some orders appear in both, identical. Append them and keep each order once.
- intermediate~7 minPro
Append two exports that overlap (bakery)
Two exports cover overlapping weeks, so some orders appear in both, identical. Append them and keep each order once.
- intermediate~7 minPro
Append two exports that overlap (bike rental)
Two exports cover overlapping weeks, so some orders appear in both, identical. Append them and keep each order once.
- intermediate~8 minPro
Align column types before appending (bookshop)
Shop sales come from a CSV (Amount is text); online sales come from a table (Amount is a number). Make Amount numeric for both.
- intermediate~8 minPro
Align column types before appending (nursery)
Shop sales come from a CSV (Amount is text); online sales come from a table (Amount is a number). Make Amount numeric for both.
- advanced~11 minPro
Append every monthly table in a workbook (kite shop)
A workbook has one Excel table per month (Sales_Jan, Sales_Feb…) plus a notes sheet and a targets table. Append just the monthly sales tables.
- advanced~11 minPro
Append every monthly table in a workbook (bakery)
A workbook has one Excel table per month (Sales_Jan, Sales_Feb…) plus a notes sheet and a targets table. Append just the monthly sales tables.