Tối Ưu Dữ Liệu Excel với Power Query: Hướng Dẫn Unpivot Dữ Liệu Chi Tiết

Việc chuẩn bị dữ liệu là bước then chốt trong phân tích dữ liệu, đặc biệt là khi làm việc với Excel. Bố cục dữ liệu hợp lý giúp đơn giản hóa việc sử dụng công thức, tránh tạo cột dư thừa và tối ưu hóa hiệu suất làm việc. Bài viết này từ Thủ Thuật sẽ hướng dẫn bạn chi tiết cách sử dụng tính năng Unpivot dữ liệu trong Power Query, một công cụ mạnh mẽ giúp chuyển đổi dữ liệu Excel một cách hiệu quả. Chúng ta sẽ cùng tìm hiểu qua ba ví dụ thực tế, từ đơn giản đến phức tạp, để làm chủ kỹ thuật này.

power-query-unpivot-du-lieu-1

Unpivot Dữ Liệu Cơ Bản trong Excel

Ví dụ đầu tiên sử dụng dữ liệu doanh số theo từng tháng. Thay vì mỗi tháng chiếm một cột, chúng ta sẽ chuyển đổi dữ liệu sao cho tên tháng nằm trong một cột duy nhất (cột “Thuộc tính”), kèm theo cột giá trị tương ứng.

1. Tạo Bảng Excel: Chọn vùng dữ liệu, nhấn Ctrl + T hoặc vào Insert -> Table. Đánh dấu chọn “My table has headers” nếu dữ liệu có hàng tiêu đề. Loại trừ các hàng tổng cộng. Đặt tên cho bảng (ví dụ: “SalesData”).

2. Mở Power Query: Chọn bảng, vào Data -> From Table/Range.

3. Xóa Bước “Change Type” (nếu cần): Bước này đôi khi gây khó khăn khi thêm cột mới.

4. Unpivot Dữ Liệu: Chọn các cột cần giữ nguyên (ví dụ: “Customer”, “Product”) và vào Transform -> Unpivot Columns -> Unpivot Other Columns. Power Query sẽ tạo cột “Attribute” chứa tên tháng và cột “Value” chứa giá trị doanh số.

5. Tinh Chỉnh Dữ Liệu:

   • Đổi tên cột “Attribute” thành “Month”.
   • Chỉnh sửa kiểu dữ liệu cho từng cột (Text, Number…).
   • Lọc bỏ các giá trị null trong cột “Value”.

6. Kết Thúc: Nhấn Close & Load để đưa dữ liệu đã xử lý về Excel.

power-query-unpivot-du-lieu-10

Unpivot Dữ Liệu với Nhiều Cột trong Excel

Ví dụ thứ hai phức tạp hơn, với dữ liệu bao gồm cả doanh số (Value) và số lượng (Unit) cho mỗi tháng. Mục tiêu là tạo một cột “Month”, một cột “Value” và một cột “Unit”.

1. Thực Hiện Các Bước 1-4 như trên.

2. Tách Cột “Attribute”: Cột “Attribute” lúc này chứa cả tên tháng và loại giá trị (Value/Unit). Sử dụng Transform -> Split Column -> By Delimeter để tách cột này dựa trên ký tự phân cách (ví dụ: khoảng trắng).

3. Hoàn Thiện: Đổi tên cột, chỉnh sửa kiểu dữ liệu và nhấn Close & Load.

power-query-unpivot-du-lieu-14

Unpivot Dữ Liệu với Nhiều Hàng Tiêu Đề trong Excel

Ví dụ cuối cùng xử lý dữ liệu có hai hàng tiêu đề. Đây là một định dạng khó làm việc trong Excel, và Power Query sẽ giúp chúng ta chuẩn hóa nó.

1. Tạo Bảng Excel: Không chọn “My table has headers” khi tạo bảng.

2. Mở Power Query: Thực hiện bước 2 như trên.

3. Chuyển Vị và Điền Dữ Liệu:

   • Transform -> Transpose.
   • Chọn cột đầu tiên, Transform -> Fill -> Down để điền tên tháng xuống các hàng.
   • Chọn hai cột đầu tiên, Transform -> Merge Columns, sử dụng ký tự phân cách.
   • Transform -> Transpose để đưa dữ liệu về định dạng ban đầu.

4. Loại Bỏ Ký Tự Thừa: Transform -> Format -> Trim.

5. Đặt Hàng Tiêu Đề: Transform -> Use First Row as Headers.

6. Hoàn Thành: Thực hiện các bước tương tự như ví dụ 2 để tách cột và hoàn thiện dữ liệu.

power-query-unpivot-du-lieu-19

Qua ba ví dụ trên, hy vọng bạn đã nắm được cách sử dụng Power Query để unpivot dữ liệu trong Excel. Kỹ thuật này giúp tối ưu hóa bố cục dữ liệu, từ đó đơn giản hóa việc phân tích và báo cáo dữ liệu.