Pivot Và Unpivot Trong Power Query: Chuyển Đổi Cấu Trúc Dữ Liệu Dễ Dàng

1. Pivot Vs Unpivot: Khái Niệm #

Pivot: xoay dữ liệu từ dọc sang ngang — biến giá trị cột thành tên cột mới. Unpivot: ngược lại — biến nhiều cột thành dòng. Đây là 2 thao tác reshape data quan trọng nhất trong Power Query, giúp chuyển đổi data giữa wide format và long format.

Ví dụ: báo cáo doanh thu có 12 cột (Jan-Dec) → Unpivot thành 2 cột (Month, Revenue) để vẽ chart timeline. Ngược lại: data dạng transaction (1 dòng/tháng) → Pivot thành matrix cho báo cáo.

2. Unpivot — Biến Cột Thành Dòng #

Trong Power Query: Select cột cần giữ cố định → Transform → Unpivot Other Columns. Kết quả: 2 cột mới "Attribute" (tên cột cũ) và "Value" (giá trị). Rename cho phù hợp.

// M Language: Unpivot
let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    Unpivoted = Table.UnpivotOtherColumns(
        Source, 
        {"Product", "Region"},  // Cột giữ cố định
        "Month",   // Tên cột Attribute mới
        "Revenue"  // Tên cột Value mới  
    )
in
    Unpivoted

3. Pivot — Biến Dòng Thành Cột #

Select cột muốn trở thành column headers → Transform → Pivot Column → chọn Values Column + Aggregate Function (Sum, Count, Don't Aggregate). Kết quả: data từ long format thành wide format matrix.

// M Language: Pivot  
let
    Source = ...,
    Pivoted = Table.Pivot(
        Source,
        List.Distinct(Source[Month]),  // Giá trị thành cột headers
        "Month",     // Cột chứa headers
        "Revenue",   // Cột chứa values
        List.Sum     // Aggregate: Sum
    )
in
    Pivoted

4. Khi Nào Dùng Unpivot? #

Unpivot TRƯỚC khi: vẽ chart (timeline, comparison), load vào data model (star schema yêu cầu long format), tính toán aggregate (SUM, AVG dễ hơn trên long format), merge với bảng khác. Rule: database = long format, report = wide format.

5. Real-World Use Cases #

Use case 1: File Excel từ phòng kế toán có 12 cột tháng → Unpivot thành format chuẩn (Date, Amount) để load vào Power BI. Use case 2: Survey results (Q1,Q2,Q3... thành columns) → Unpivot để phân tích distribution. Use case 3: Cross-tab report → Unpivot → Transform → Pivot lại theo tiêu chí khác.

6. Unpivot Only Selected Columns Vs Other Columns #

Unpivot Columns: chỉ unpivot các cột đã select. Unpivot Other Columns: unpivot TẤT CẢ cột NGOẠI TRỪ cột đã select. Dùng Other Columns khi số cột thay đổi (VD: thêm tháng mới) — dynamic và an toàn hơn.

7. Tips Và Lỗi Thường Gặp #

1) Data type phải consistent trước Pivot — nếu cột Value có mix text/number sẽ lỗi. 2) Unpivot Other Columns tốt hơn Unpivot Columns vì tự handle cột mới. 3) Rename Attribute/Value ngay sau unpivot. 4) Dùng Fill Down nếu có merged cells trước khi Unpivot.

8. Kết Luận #

Pivot/Unpivot là kỹ năng ETL cốt lõi. Unpivot biến report format (wide) thành database format (long) để phân tích. Pivot biến data format thành report format để trình bày. Master cả hai = xử lý được mọi kiểu reshape data.