Chuẩn Hóa Dữ Liệu trong Excel với Power Query: Hướng Dẫn Chi Tiết

Dữ liệu tốt là nền tảng cho phân tích hiệu quả. Nếu dữ liệu lộn xộn, việc phân tích trong Excel sẽ trở nên khó khăn, đòi hỏi các công thức phức tạp, thêm cột không cần thiết, hoặc thậm chí phải sao chép dữ liệu. Nhưng nếu dữ liệu được cấu trúc đúng, mọi thứ sẽ dễ dàng hơn rất nhiều. Bài viết này sẽ hướng dẫn bạn cách chuẩn hóa dữ liệu bằng Power Query, một công cụ mạnh mẽ trong Excel.

Power Query giúp bạn sắp xếp và biến đổi dữ liệu một cách nhanh chóng và hiệu quả. Hãy cùng tìm hiểu qua một số ví dụ thực tế.

Ví dụ 1: Chuyển đổi dữ liệu theo quý

Giả sử bạn có bảng dữ liệu doanh thu theo từng quý như sau:

Dữ liệu doanh thu theo quý

Thay vì mỗi quý chiếm một cột, chúng ta sẽ đưa tất cả các quý vào một cột duy nhất. Vậy làm thế nào để thực hiện việc này?

Với Power Query, bạn không cần phải sao chép và dán thủ công. Hãy cùng xem cách thực hiện:

1. Tạo bảng: Chuyển đổi dữ liệu thành bảng bằng cách nhấn Ctrl + T hoặc Ctrl + L và đặt tên cho bảng (ví dụ: VD_1).

2. Mở Power Query Editor: Chọn một ô bất kỳ trong bảng và chọn From Table/Range.

Mở Power Query Editor

3. Sử dụng Unpivot Columns: Trong Power Query Editor, vào tab Transform và chọn Unpivot Columns (hoặc Unpivot Only Selected Columns nếu chỉ muốn chuyển đổi một số cột cụ thể). Chọn các cột Quý 1, Quý 2, Quý 3, Quý 4.

Sử dụng Unpivot Columns

4. Đổi tên cột: Đổi tên cột Attribute thành Quý.

Kết quả sau khi Unpivot

5. Nạp dữ liệu: Chọn Close & Load (hoặc Close & Load To...) để đưa dữ liệu đã chuẩn hóa vào Excel.

Nạp dữ liệu vào Excel

Kết quả cuối cùng sẽ như sau:

Dữ liệu sau khi chuẩn hóa

Ngoài Unpivot Columns, bạn cũng có thể sử dụng Unpivot Other Columns. Sự khác biệt giữa hai phương pháp này nằm ở khả năng tự động cập nhật khi có thêm cột mới.

Ví dụ 2: Tách và Pivot dữ liệu

Giả sử dữ liệu của bạn được cấu trúc như sau:

Dữ liệu gốc ví dụ 2

Cấu trúc lý tưởng hơn sẽ là các cột STT, Hàng hoá, Tháng, Doanh thu (DT), Chi phí (CP). Power Query có thể giúp bạn thực hiện điều này:

1. Tạo bảng và mở Power Query Editor: Tương tự như ví dụ 1.

2. Unpivot: Chọn Unpivot Other Columns.

Unpivot Other Columns

3. Tách cột: Tách cột Attribute thành hai cột bằng dấu phân cách “_” (dùng chức năng Split Columns).

Tách cột Attribute

4. Pivot: Pivot cột Attribute.1 (chứa DT và CP) bằng cách chọn cột này và cột Value, sau đó chọn Pivot Column.

Pivot cột Attribute.1

5. Đổi tên và định dạng: Đổi tên cột và định dạng dữ liệu cho phù hợp.

6. Nạp dữ liệu: Close & Load vào Excel.

Kết quả:

Dữ liệu sau khi chuẩn hóa ví dụ 2

Ví dụ 3: Xử lý tiêu đề Merge

Nếu tiêu đề của bạn bị merge, bạn có thể xử lý trực tiếp trong Excel hoặc trong Power Query Editor. Trong Power Query, bạn có thể đổi tên cột theo quy tắc để áp dụng các bước tương tự như ví dụ 2.

Tiêu đề bị merge

Kết luận

Power Query là công cụ đắc lực giúp chuẩn hóa dữ liệu trong Excel một cách dễ dàng và hiệu quả. Việc thành thạo Power Query cũng là bước đệm hoàn hảo để tiếp cận Power BI, một công cụ tuyệt vời cho việc tạo báo cáo. Hy vọng bài viết này đã cung cấp cho bạn những kiến thức hữu ích về chuẩn hóa dữ liệu với Power Query.