[Power Query Tutorial] Cách sử dụng tính năng Transpose

Tính năng Transpose trong Power Query là gì?

Trong Power Query, transpose là thao tác chuyển đổi toàn bộ cấu trúc của một bảng dữ liệu bằng cách hoán đổi dòng (rows) thành cột (columns) và ngược lại, chuyển toàn bộ cột thành dòng.

Khi thực hiện transpose:

• Dòng đầu tiên của bảng ban đầu sẽ biến thành cột đầu tiên của bảng mới.
• Cột đầu tiên của bảng ban đầu sẽ biến thành dòng đầu tiên của bảng mới.
• Thứ tự các ô dữ liệu được giữ nguyên vị trí tương quan theo trục hoán đổi.

Tính năng này tương tự như thao tác copy và paste transpose trong excel truyền thống nhưng điểm vượt trội của transpose trong power query là tính tự động hóa. Mọi bước xử lý đều được ghi lại trong phần applied steps và sẽ tự động cập nhật khi dữ liệu nguồn thay đổi.

Minh họa cụ thể tính năng Transpose trong Power Query

Sử dụng tính năng Transpose

Tại giao diện Power Query Editor:

• Trên thanh menu chính, bạn chọn thẻ Transform.
• Tại nhóm công cụ Table, bạn sẽ thấy nút Transpose nằm ngay cạnh các công cụ như use first row as headers, reverse rows, count rows.
• Quan sát bảng dữ liệu ban đầu, dữ liệu có các cột tiêu đề mặc định là KPI by App & Division, Column2, Column3, Column4… Các dòng chứa thông tin năm (2022, 2021), tháng (Jun), loại chỉ số (Revenue, Profit, Cash) và danh mục ứng dụng (Productivity Apps, Game Apps…). Cấu trúc này rất khó để lập báo cáo dạng chuẩn.

Kết quả sau khi thực hiện Transpose

Sau khi nhấp chuột vào nút Transpose:

• Toàn bộ bảng dữ liệu đã được xoay 90 độ.
• Các tiêu đề cột ban đầu (KPI by App & Division, Column2, Column3…) đã biến thành dữ liệu dòng.
• Ngược lại, các giá trị trên từng dòng ở hình 1 (như Productivity Apps, các con số tài chính, năm 2022, 2021…) giờ đây đã quay ngang thành các cột mới (Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8…).
• Trong khung Applied Steps bên phải, một bước mới có tên Transposed Table đã tự động được thêm vào quy trình xử lý.

M Code cho tính năng Transpose trong Power Query

Mã M Code cho hàm transpose là:

Table.Transpose(table as table, optional columns as any)

Giải thích chi tiết trong ví dụ thực tế:

Như quan sát trên thanh công thức (Formula Bar), câu lệnh M Code được tạo ra là:

= Table.Transpose(#"Changed Type")

Trong đó:

• Table.Transpose: Là tên hàm built-in trong Power Query dùng để hoán đổi dòng và cột.
• #”Changed Type”: Là tên của bước (step) ngay liền trước đó trong danh sách Applied Steps. Power Query lấy đầu ra của bước “Changed Type” để làm đầu vào cho hàm transpose.

Nếu bạn muốn viết trực tiếp bằng M Code trong Advanced Editor, cấu trúc cơ bản sẽ có dạng:

let

Source = Excel.Workbook(File.Contents("C:\Data\Report.xlsx"), null, true),

Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],

#"Changed Type" = Table.TransformColumnTypes(Data_Sheet, {{"Column1", type text}}),

#"Transposed Table" = Table.Transpose(#"Changed Type")

in

#"Transposed Table"

Trong thực tế dùng tính năng Transpose để làm gì?

Chuẩn hóa báo cáo tài chính và quản trị dạng ma trận

Các file xuất ra từ phần mềm kế toán, ERP hoặc file Excel ngân sách do con người lập thường có dạng ma trận:

• Chiều dọc (dòng): Danh mục các chỉ số tài chính (Doanh thu, Giá vốn, Chi phí bán hàng, Lợi nhuận gộp, Cash Flow…).
• Chiều ngang (cột): Các mốc thời gian (Tháng 1, Tháng 2, … Tháng 12, Quý 1, Quý 2, Năm 2023, 2024…).

Cấu trúc ma trận này phù hợp cho người đọc bằng mắt nhưng hoàn toàn bất lợi khi đưa vào Power BI, Pivot Table hay xây dựng mô hình dữ liệu. Bạn không thể viết các DAX Measures hay tạo bộ lọc slicer thời gian.

Cách giải quyết với Transpose:

• Xoay bảng bằng Transpose để đưa toàn bộ danh mục chỉ số từ dòng thành các tiêu đề cột.
• Kết hợp công cụ Unpivot cho các cột chứa mốc thời gian để chuyển bảng về chuẩn hàng dọc (Long Format / Fact Table). Khi đó, mỗi dòng đại diện cho đúng 1 bản ghi giao dịch gồm Thời gian – Chỉ số – Giá trị.

Xử lý file dữ liệu có tiêu đề nhiều dòng

Khi làm việc với file báo cáo Excel tổng hợp, hệ thống hoặc người dùng thường gộp ô và dùng 2 đến 3 dòng đầu tiên làm tiêu đề. Power Query không thể nhận diện trực tiếp 3 dòng này làm 1 dòng tiêu đề cột chuẩn. Cách xử lý triệt để duy nhất là ứng dụng kỹ thuật Transpose – Fill Down – Merge Columns:

• Nếu Power Query đã tự nhận diện dòng 1 làm tiêu đề, chọn Use Headers as First Row (Demote Headers) để hạ toàn bộ tiêu đề xuống thành các dòng dữ liệu thông thường.
• Chọn Transform -> Transpose để xoay toàn bộ bảng. Lúc này, các dòng tiêu đề sẽ biến thành các cột dữ liệu đầu tiên. Các ô bị Merge Cells ban đầu sẽ hiển thị thành các giá trị null xếp nối tiếp theo chiều dọc.
• Nhấp chuột phải vào các cột có chứa null và chọn Fill -> Down để lấp đầy toàn bộ giá trị null bằng thông tin của ô phía trên.
• Dùng tính năng Merge Columns để ghép các cột tiêu đề lại với nhau thành 1 cột duy nhất.
• Bấm Transpose một lần nữa để xoay bảng trở lại chiều ngang ban đầu.
• Chọn Use First Row as Headers. Kết quả là bạn đã có một dòng tiêu đề duy nhất, rõ ràng và đầy đủ ngữ nghĩa.

Hướng dẫn chuẩn hóa dữ liệu kết hợp tính năng Transpose và Unpivot

• Tháo tiêu đề cũ (Promote/Demote Headers): Nếu bảng đang có tiêu đề cột, chọn Use Headers as First Row để chuyển tiêu đề thành dòng đầu tiên.
• Thực hiện Transpose: Nhấp vào Transform > Transpose để xoay toàn bộ bảng.
• Điền khoảng trống và gộp thông tin: Dùng Fill Down hoặc Fill Up cho các ô null, sau đó gộp các cột thông tin bằng Merge Columns.
• Xoay lại hoặc Unpivot: Nhấp Transpose một lần nữa để trả về chiều mong muốn, hoặc sử dụng Unpivot Columns để đưa dữ liệu định dạng số về một cột duy nhất.

So sánh tính năng Transpose và Unpivot trong Power Query

Rất nhiều người mới học Power Query thường nhầm lẫn giữa Transpose và Unpivot. Bảng so sánh dưới đây giúp bạn phân biệt rõ ràng:

Tổng kết

Tính năng Transpose trong Power Query là công cụ không thể thiếu khi xử lý và làm sạch dữ liệu trong Excel cũng như Power BI. Bằng cách hoán đổi linh hoạt giữa dòng và cột, Transpose giúp bạn dễ dàng giải quyết các bài toán phức tạp và chuẩn hóa dữ liệu từ nhiều nguồn khác nhau. Việc nắm vững cách kết hợp Transpose cùng các thao tác như Fill Down, Merge Columns và Unpivot sẽ giúp bạn xây dựng quy trình xử lý dữ liệu tự động, tối ưu thời gian làm báo cáo và nâng cao hiệu suất công việc hàng ngày.