Lesson 2.4: Rapid Data Transformation: Flash Fill, Text to Columns & Deduplication

🎥 Video Chapter: Chapter 2 – Data Management (16:05)

Why This Matters

Data analysts frequently receive exports where multiple attributes are crammed into a single cell or transaction tables containing repeated dimensional entities. Excel's built-in transformation tools allow instant parsing and restructuring without complex formulas or external ETL code.

Core Concepts

• Flash Fill (Ctrl + E): Machine-learning driven pattern detection that parses, merges, or restructures text based on sample manual entries.
• Text to Columns (Alt + A + E): Converts delimited text strings (commas, tabs, hyphens, pipes, spaces) into distinct adjacent columns.
• Remove Duplicates (Alt + A + M): Scans specified columns for duplicate key combinations and purges redundant rows.

1. Text to Columns: Parsing Superstore Product IDs

The Sample Superstore dataset (<a href="/COURSE-EXCEL-ZERO-TO-HERO/reference/sample-superstore-dataset-documentation/" class="internal-link">Sample Superstore Dataset Documentation</a>) encodes 3 hierarchical attributes into the Product ID column (e.g., FUR-BO-10001798):

1. Department Code: FUR (Furniture), OFF (Office Supplies), TEC (Technology)
2. Sub-Category Code: BO (Bookcases), CH (Chairs), PH (Phones), LA (Labels)
3. Item Serial Number: 10001798 (unique 8-digit item identifier)

Step-by-Step Delimited Extraction

1. Select the Product ID column range (L2:L9995).
2. Press Alt + A + E (or Data > Text to Columns).
3. Step 1 of 3: Select Delimited ➔ Click Next.
4. Step 2 of 3:
   • Uncheck Tab.
   • Check Other and type hyphen - in the delimiter box.
   • Preview window shows 3 distinct columns.
5. Step 3 of 3:
   • Set Destination cell to empty adjacent columns (e.g., T2) to preserve the raw Product ID.
   • Select Column 3 (10001798) and explicitly choose Text format to avoid stripping leading zeros if serials contain them.
6. Click Finish.

Raw Product ID:         FUR-BO-10001798
                              │
                    [Text to Columns: '-']
                              │
         ┌────────────────────┼────────────────────┐
         ▼                    ▼                    ▼
   [Col 1: Dept]      [Col 2: Sub-Cat]     [Col 3: Serial]
       "FUR"                "BO"              "10001798"

2. Flash Fill (Ctrl + E): Order ID Decomposition

In <a href="/COURSE-EXCEL-ZERO-TO-HERO/reference/sample-superstore-dataset-documentation/" class="internal-link">Sample Superstore Dataset Documentation</a>, Order ID combines country, order year, and order sequence: CA-2016-152156 or US-2015-108966.

Flash Fill detects patterns instantaneously without touching formulas:

3. Deduplication: Transactional vs Dimensional Granularity

Correct Deduplication Workflow (Building a Customer Dimension Table)

1. Copy the Customer ID, Customer Name, and Segment columns to a new sheet.
2. Select the copied columns (A1:C9995).
3. Press Alt + A + M (or Data > Remove Duplicates).
4. Ensure My data has headers is checked.
5. Select Customer ID as the sole primary key check.
6. Excel reports:

   "9201 duplicate values found and removed; 793 unique values remain."

7. You now have a verified Customer Dimension Table with zero data corruption to the underlying sales ledger.

Practical Lab Exercise & Demo Workbook

• Demo Workbook: 11_Demos_and_Workbooks/02_Data_Management/Superstore_Dataset_Demo.xlsx
  • Order Year: Extracted using =YEAR([@[Order Date]]) and formatted as integer 0.
  • Gross Revenue: Calculated catalog price before discount: =[@Sales] / (1 - [@Discount]).
  • Net Revenue: Realized revenue after discount: =[@Sales] * (1 - [@Discount]).
  • Dept, Sub-Cat, Serial: Decomposed from Product ID via Delimited Text to Columns or =TEXTSPLIT([@[Product ID]], "-").
  • Dim_Customers Tab: Deduplicated customer dimension table (793 distinct customer entities).

Related Knowledge

• Concepts: Data Cleaning, Power Query, Excel File Formats
• Dataset: Sample Superstore Dataset Documentation
• Formulas: TEXTJOIN, TRIM, TEXTSPLIT
• Student Workbook: 11_Demos_and_Workbooks/02_Data_Management/Superstore_Dataset_Demo.xlsx