[Power Query Tutorial] Cách dùng chức năng Pivot Column

Pivot Column trong Power Query là tính năng chuyển đổi cấu trúc bảng cho phép lấy các giá trị duy nhất từ một cột danh mục để biến thành tiêu đề của các cột mới đồng thời tổng hợp các giá trị số liệu tương ứng vào từng ô giao nhau. Thao tác này giúp chuyển đổi nhanh bảng dữ liệu từ dạng dọc sang dạng ngang, phục vụ cho việc tính toán chéo giữa các chỉ số, tạo báo cáo tổng hợp nhanh và tái cấu trúc dữ liệu dạng Key-Value.

Bài viết dưới đây Starttrain sẽ hướng dẫn chi tiết từng bước thao tác thực tế trên giao diện, giải mã cú pháp M Code và chia sẻ giải pháp xử lý các lỗi thường gặp khi sử dụng Pivot Column.

Về chức năng Pivot Column trong Power Query

Pivot Column là gì?

Pivot Column là thao tác biến đổi cấu trúc bảng trong Power Query, thực hiện chuyển đổi các giá trị ở từng dòng của một cột thành các cột độc lập mới. Cơ chế xử lý cụ thể như sau:

• Tạo tiêu đề cột mới: Hệ thống lọc ra danh sách các giá trị duy nhất từ một cột phân loại được chọn và biến mỗi giá trị đó thành một tiêu đề cột mới.
• Gộp dòng dữ liệu: Các dòng có cùng giá trị ở những cột định danh còn lại sẽ được gom lại thành một dòng duy nhất.
• Điền dữ liệu tương ứng: Số liệu từ cột giá trị được chỉ định sẽ được phân phối vào đúng ô giao nhau giữa dòng định danh và cột mới tương ứng.

Kết quả sau khi thực hiện thao tác là bảng dữ liệu sẽ giảm số lượng dòng và tăng số lượng cột.

Bản chất hoạt động của Pivot Column

Để thực hiện thao tác Pivot, Power Query yêu cầu xác định hai thành phần:

• Pivot Column: Cột chứa các giá trị định danh sẽ được nâng cấp thành hàng tiêu đề cột mới.
• Values Column: Cột chứa số liệu sẽ được phân bổ vào các ô tương ứng dưới các cột mới tạo.

Nếu bảng dữ liệu có nhiều dòng trùng nhau về tổ hợp các cột định danh và nhãn Pivot, Power Query sẽ kích hoạt các hàm thống kê như Sum, Count, Average, Min, Max, hoặc tùy chọn giữ nguyên không tính toán (Don’t Aggregate).

Minh họa thao tác thực hiện Pivot Column trong Power Query

Xác định cấu trúc bảng cần biến đổi

Trước khi thao tác, hãy quan sát bảng dữ liệu nguồn: cột Index hiện đang ở dạng phân loại dọc, chứa 4 chỉ số quảng cáo lặp đi lặp lại gồm Conv (Conversion), Cost (Chi phí), CTR (Tỷ lệ click) và Impr (Impression). Cột Value chứa số liệu tương ứng với từng chỉ số đó. Các bước xử lý cụ thể như sau:

• Di chuyển chuột đến bảng dữ liệu và nhấp chuột trái vào tiêu đề cột Index để chọn toàn bộ cột.
• Trên thanh công cụ Ribbon phía trên màn hình, chọn thẻ Transform.
• Di chuyển đến nhóm lệnh Any Column nằm ở góc trái của thanh công cụ.
• Nhấp chuột vào nút chức năng Pivot Column.

Thiết lập thông số cột giá trị trong hộp thoại Pivot Column

Sau khi nhấp lệnh, cửa sổ thiết lập Pivot Column sẽ hiển thị ngay trung tâm màn hình làm việc:

• Tại trường Values Column, nhấp vào biểu tượng menu thả xuống và chọn đúng cột chứa số liệu là Value. Đây là cột sẽ cung cấp giá trị số để điền vào từng ô giao nhau tương ứng dưới 4 cột mới.
• Nhấp vào mục Advanced options nếu bạn muốn thay đổi phương pháp tổng hợp. Với dữ liệu dạng số, Power Query sẽ mặc định chọn hàm Sum. Bạn có thể chuyển sang Average, Min, Max, Count, hoặc chọn Don’t Aggregate.
• Sau khi kiểm tra trường Values Column đã hiển thị đúng cột Value, nhấp chuột vào nút OK.

Kết quả sau khi biến đổi

Ngay sau khi lệnh được thực thi, giao diện Power Query Editor lập tức cập nhật cấu trúc bảng dữ liệu mới:

• Cột Index ban đầu đã hoàn toàn biến mất khỏi bảng. Thay vào đó, 4 giá trị định danh đã được tách thành 4 cột số liệu độc lập đặt liền kề nhau: Conv, Cost, CTR, và Impr.
• Bảng dữ liệu được thu gọn từ dạng dọc sang dạng ngang. Mỗi dòng dữ liệu giờ đây đại diện cho một bản ghi duy nhất xác định bởi tổ hợp 3 cột còn lại: Row Labels + Platform + Month (ví dụ: dòng đầu tiên phản ánh trọn vẹn số liệu của Campaign_10 trên nền tảng Facebook trong tháng M1).
• Ở góc bên phải màn hình, danh sách Applied Steps tự động ghi nhận một bước biến đổi mới có tên là Pivoted Column, nằm ngay sau bước Filtered Rows.
• Phía trên bảng dữ liệu hiển thị câu lệnh M Code đã được hệ thống tạo tự động.

M Code đằng sau chức năng Pivot Column

Hiểu rõ cú pháp M Code giúp bạn làm chủ thao tác và viết lệnh linh hoạt mà không bị phụ thuộc vào giao diện nút bấm.

Cú pháp chuẩn của hàm Table.Pivot

Table.Pivot(

    table as table, 

    pivotValues as list, 

    attributeColumn as text, 

    valueColumn as text, 

    optional aggregationFunction as nullable function

) as table

Phân tích code từ ví dụ minh họa

Nhìn vào thanh công thức tại hình 3, công thức M được sinh ra tự động là:

= Table.Pivot(#”Filtered Rows”, List.Distinct(#”Filtered Rows”[Index]), “Index”, “Value”, List.Sum)

Trong đó:

• #”Filtered Rows”: Bảng dữ liệu nguồn lấy từ bước thao tác ngay trước đó.
• List.Distinct(#”Filtered Rows”[Index]): Danh sách các giá trị duy nhất trích xuất từ cột Index. Power Query dùng hàm List.Distinct để tìm danh sách các nhãn cần tạo thành tiêu đề cột ({“Conv”, “Cost”, “CTR”, “Impr”}).
• “Index”: Tên cột thuộc tính danh mục xoay ngang (Attribute Column).
• “Value”: Tên cột chứa số liệu phân bổ vào các ô (Value Column).
• List.Sum: Hàm tính tổng giá trị gộp khi phát sinh các dòng trùng khóa định danh.

Phân biệt Pivot Column và Transpose trong Power Query

Cả Pivot Column và Transpose đều là các tính năng làm thay đổi hướng hiển thị của bảng dữ liệu, nhưng bản chất toán học và mục đích sử dụng hoàn toàn khác nhau:

[FAQ] Một vài thắc mắc về Pivot Column trong Power Query

Lỗi “There are too many elements in the enumeration to complete the operation” xuất hiện khi nào?

Lỗi này xảy ra khi bạn chọn Don’t Aggregate trong mục Advanced Options, nhưng thực tế dữ liệu lại có từ 2 giá trị trở lên cho cùng một ô giao nhau. Nếu chấp nhận tính tổng hoặc lấy giá trị biên, hãy chuyển sang dùng Sum, Max hoặc Count. Nếu dữ liệu bắt buộc là duy nhất, hãy dùng lệnh Remove Duplicates ở bước trước đó để loại bỏ trùng lặp.

Có thể Pivot cột chứa dữ liệu Text được không?

Hoàn toàn được. Trong mục Advanced Options, bạn có thể:

• Chọn Don’t Aggregate nếu mỗi giao điểm chỉ có duy nhất một giá trị text.
• Sử dụng hàm gộp chuỗi trong M Code (Combiner.CombineTextByDelimiter) nếu muốn nối các đoạn văn bản trùng nhau lại bằng dấu phẩy.

Pivot Column có làm chậm tốc độ refresh dữ liệu không?

Có. Pivot Column là một thao tác tính toán trên toàn bảng, buộc Power Query phải tải hết toàn bộ dòng để quét danh sách cột và thực hiện gom nhóm. Phương pháp cho vấn đề này là lọc dữ liệu (Filter Rows) và xóa bỏ cột không dùng trước khi Pivot, đồng thời đặt bước Pivot Column ở giai đoạn cuối của quy trình ETL.

Tổng kết

Pivot Column trong Power Query là công cụ cốt lõi giúp xoay chuyển dữ liệu từ dạng dọc sang dạng ngang chuẩn xác dựa trên cặp thuộc tính và giá trị. Việc làm chủ thao tác này, kết hợp hiểu rõ bản chất hàm Table.Pivot và phân biệt với Transpose, sẽ giúp bạn xây dựng mô hình dữ liệu tối ưu cho việc tính toán chéo, kiểm soát tốt các lỗi tổng hợp và duy trì hiệu suất làm mới báo cáo ổn định trên Power BI lẫn Excel.