Hướng dẫn sử dụng Power Query trong Excel 2019: Bí kíp xử lý dữ liệu hiệu quả

Bạn đang vật lộn với việc xử lý và làm sạch dữ liệu thủ công trong Excel? Dành hàng giờ để kết hợp các bảng tính, loại bỏ lỗi, và chuẩn hóa định dạng? Nếu vậy, đã đến lúc bạn khám phá Power Query – công cụ biến đổi dữ liệu mạnh mẽ được tích hợp sẵn trong Excel 2019. Bài viết này sẽ cung cấp hướng dẫn sử dụng Power Query trong Excel 2019 một cách chi tiết, giúp bạn tự động hóa các tác vụ lặp đi lặp lại và nâng cao hiệu quả công việc.

Trước đây, Power Query là một add-in riêng biệt, nhưng kể từ phiên bản Excel 2016, Microsoft đã tích hợp nó vào thẻ Dữ liệu (Data) dưới tên gọi Get & Transform Data. Điều này mang lại sự tiện lợi tối đa cho người dùng Excel 2019.

Nội dung chính

Power Query là gì và Tại sao bạn nên dùng nó trong Excel 2019?

Power Query là một công cụ Business Intelligence (BI) mạnh mẽ, hoạt động như một engine ETL (Extract, Transform, Load) ngay trong môi trường Excel quen thuộc. Nó cho phép bạn:

Kết nối và Nhập dữ liệu (Extract): Lấy dữ liệu từ rất nhiều nguồn khác nhau như file Excel, CSV, Text, thư mục chứa nhiều file, cơ sở dữ liệu (SQL Server, Access…), trang web, dịch vụ đám mây (Azure), và nhiều nguồn khác.
Biến đổi và Làm sạch dữ liệu (Transform): Đây là sức mạnh cốt lõi của Power Query. Bạn có thể thực hiện hàng trăm thao tác biến đổi dữ liệu mà không cần viết code phức tạp, ví dụ: loại bỏ cột/hàng, lọc dữ liệu, thay đổi kiểu dữ liệu, tách/gộp cột, chuẩn hóa văn bản (loại bỏ khoảng trắng, đổi chữ hoa/thường), tính toán cột mới, unpivot/pivot bảng, kết hợp (merge) hoặc nối (append) nhiều bảng.
Tải dữ liệu (Load): Sau khi dữ liệu đã được làm sạch và định hình theo ý muốn, bạn có thể tải nó vào trang tính Excel dưới dạng bảng (Table), PivotTable, PivotChart, hoặc chỉ tạo kết nối để sử dụng trong Power Pivot Data Model.

Tại sao nên sử dụng Power Query trong Excel 2019?

Tiết kiệm thời gian: Tự động hóa các bước xử lý dữ liệu lặp đi lặp lại. Một khi bạn đã thiết lập quy trình trong Power Query, chỉ cần một cú nhấp chuột “Refresh” để cập nhật dữ liệu mới.
Giảm thiểu lỗi: Các bước biến đổi được ghi lại và thực hiện nhất quán, loại bỏ nguy cơ lỗi do thao tác thủ công.
Xử lý dữ liệu lớn: Power Query có thể xử lý hiệu quả các tập dữ liệu lớn hơn khả năng của trang tính Excel thông thường, đặc biệt khi kết hợp với Data Model.
Tăng tính nhất quán: Đảm bảo dữ liệu luôn được xử lý theo cùng một quy tắc, tạo ra kết quả đáng tin cậy cho việc phân tích và báo cáo.
Giao diện trực quan: Hầu hết các thao tác được thực hiện qua giao diện người dùng thân thiện, không yêu cầu kiến thức lập trình sâu (mặc dù ngôn ngữ M phía sau cung cấp khả năng tùy biến nâng cao).

Các bước cơ bản để sử dụng Power Query trong Excel 2019

Hãy cùng đi qua quy trình cơ bản để bắt đầu với tính năng Power Query Excel 2019.

Bước 1: Truy cập Power Query (Get Data)

Để bắt đầu, hãy mở Excel 2019 và điều hướng đến tab Data. Trong nhóm Get & Transform Data, bạn sẽ thấy nút Get Data. Nhấp vào đây sẽ mở ra một menu với nhiều tùy chọn nguồn dữ liệu.

From File: Chọn nếu dữ liệu của bạn nằm trong các file Excel, Text/CSV, XML, JSON, hoặc cả một thư mục chứa nhiều file cùng cấu trúc.
From Database: Kết nối với các hệ quản trị cơ sở dữ liệu phổ biến.
From Azure: Lấy dữ liệu từ các dịch vụ lưu trữ và cơ sở dữ liệu trên Azure.
From Online Services: Kết nối với các dịch vụ như SharePoint Online List.
From Other Sources: Bao gồm các tùy chọn như From Web (lấy dữ liệu từ bảng trên trang web), Blank Query (tự viết truy vấn M),…

[Gợi ý: Chèn ảnh màn hình tab Data > nhóm Get & Transform Data trong Excel 2019]

Bước 2: Kết nối và Tải dữ liệu vào Power Query Editor

Sau khi chọn nguồn dữ liệu (ví dụ: From File > From Workbook), bạn sẽ duyệt đến file cần lấy dữ liệu. Một cửa sổ Navigator sẽ xuất hiện, hiển thị các bảng hoặc trang tính có trong file đó. Chọn đối tượng bạn muốn nhập và nhấp vào nút Transform Data. Thao tác này sẽ mở cửa sổ Power Query Editor – nơi bạn thực hiện các phép biến đổi.

[Gợi ý: Chèn ảnh cửa sổ Power Query Editor với dữ liệu mẫu]

Bước 3: Chuyển đổi và Làm sạch dữ liệu (Transform Data)

Cửa sổ Power Query Editor có giao diện ribbon tương tự Excel, chứa đầy đủ các công cụ biến đổi:

Home Tab: Chứa các lệnh phổ biến như chọn/loại bỏ cột, giữ/xóa hàng, tách cột, thay đổi kiểu dữ liệu, làm mới preview.
Transform Tab: Cung cấp các lệnh biến đổi trên các cột hiện có như định dạng văn bản, trích xuất, tính toán số học, pivot/unpivot cột.
Add Column Tab: Cho phép thêm các cột mới dựa trên tính toán, điều kiện (Conditional Column), hoặc các hàm tùy chỉnh.
View Tab: Điều chỉnh cách hiển thị trong Editor, bao gồm bật/tắt thanh công thức, xem các bước đã áp dụng (Applied Steps).

Ở phía bên phải, bạn sẽ thấy khung Applied Steps. Mọi thao tác bạn thực hiện trong Editor đều được ghi lại tại đây dưới dạng một bước. Bạn có thể nhấp vào từng bước để xem trạng thái dữ liệu tại thời điểm đó, chỉnh sửa hoặc xóa bước nếu cần. Đây là tính năng cực kỳ hữu ích để gỡ lỗi và hiểu quy trình xử lý.

Một số thao tác làm sạch phổ biến bao gồm: loại bỏ các hàng trống, lọc dữ liệu không cần thiết, thay đổi kiểu dữ liệu (ví dụ: từ text sang number hoặc date), tách cột họ tên thành cột họ và cột tên, gộp nhiều file trong một thư mục thành một bảng duy nhất.

Bước 4: Tải dữ liệu đã xử lý vào Excel (Close & Load)

Khi đã hài lòng với dữ liệu đã biến đổi, hãy nhấp vào nút Close & Load trên tab Home. Bạn có hai lựa chọn chính:

Close & Load: Tải dữ liệu vào một trang tính mới trong Excel dưới dạng Bảng (Table) mặc định.
Close & Load To…: Mở hộp thoại “Import Data”, cho phép bạn chọn cách tải dữ liệu:
- Table: Tải vào bảng Excel (mặc định).
- PivotTable Report: Tạo PivotTable trực tiếp từ dữ liệu đã xử lý.
- PivotChart: Tạo PivotChart trực tiếp.
- Only Create Connection: Chỉ tạo kết nối đến dữ liệu, không tải trực tiếp vào trang tính. Thường dùng khi bạn muốn tải dữ liệu vào Power Pivot Data Model hoặc sử dụng truy vấn này làm nguồn cho truy vấn khác.
Bạn cũng có thể chọn thêm dữ liệu này vào Data Model, hữu ích cho việc tạo mối quan hệ giữa các bảng và phân tích bằng Power Pivot.

[Gợi ý: Chèn ảnh menu Close & Load và hộp thoại Import Data]

Dữ liệu sau khi tải ra Excel sẽ có kết nối với Power Query. Khi dữ liệu nguồn thay đổi (ví dụ: thêm dữ liệu mới vào file gốc, hoặc thêm file mới vào thư mục), bạn chỉ cần vào tab Data > Refresh All (hoặc chuột phải vào bảng dữ liệu > Refresh), Power Query sẽ tự động thực hiện lại tất cả các bước biến đổi và cập nhật kết quả trong Excel.

Ví dụ thực tế: Kết hợp nhiều file Excel bằng Power Query

Một ứng dụng cực kỳ phổ biến của Power Query là kết hợp dữ liệu từ nhiều file Excel có cùng cấu trúc trong một thư mục. Thay vì mở từng file và copy-paste thủ công, bạn chỉ cần:

Chọn Data > Get Data > From File > From Folder.
Duyệt đến thư mục chứa các file Excel.
Trong cửa sổ xem trước, nhấp Combine > Combine & Transform Data.
Chọn sheet hoặc bảng mẫu chứa dữ liệu trong cửa sổ Combine Files.
Power Query sẽ tự động tạo các hàm và truy vấn cần thiết để kết hợp dữ liệu từ tất cả các file. Bạn có thể tiếp tục làm sạch, biến đổi dữ liệu kết hợp trong Power Query Editor.
Cuối cùng, Close & Load để đưa dữ liệu tổng hợp vào Excel.

Khi có file mới được thêm vào thư mục, chỉ cần Refresh, dữ liệu mới sẽ tự động được cập nhật vào bảng kết quả.

Mẹo và Lưu ý khi sử dụng Power Query trong Excel 2019

Đặt tên rõ ràng: Đặt tên có ý nghĩa cho các truy vấn và các bước trong Applied Steps để dễ dàng quản lý và bảo trì sau này.
Kiểm tra kiểu dữ liệu: Luôn đảm bảo các cột có kiểu dữ liệu đúng (số, ngày tháng, văn bản…) để tránh lỗi tính toán hoặc phân tích sai lệch.
Tận dụng Refresh: Hiểu rõ cơ chế làm mới dữ liệu để đảm bảo báo cáo của bạn luôn cập nhật.
Tìm hiểu thêm về M Language: Mặc dù không bắt buộc, việc hiểu cơ bản về ngôn ngữ M (ngôn ngữ công thức của Power Query) sẽ mở ra khả năng tùy biến và xử lý các tác vụ phức tạp hơn. Bạn có thể xem mã M của từng bước trong Advanced Editor.
Kết hợp với Power Pivot: Để phân tích dữ liệu phức tạp hơn từ nhiều bảng, hãy tải dữ liệu vào Data Model và sử dụng Power Pivot để tạo mối quan hệ và các phép đo DAX.

Tóm lại, hướng dẫn sử dụng Power Query trong Excel 2019 này cho thấy đây là một công cụ không thể thiếu cho bất kỳ ai thường xuyên làm việc với dữ liệu. Nó giúp tự động hóa quy trình làm sạch và chuẩn bị dữ liệu, tiết kiệm vô số thời gian và công sức, đồng thời đảm bảo tính chính xác và nhất quán. Hãy bắt đầu khám phá Power Query ngay hôm nay để cách mạng hóa cách bạn làm việc với Excel!

Để tìm hiểu sâu hơn, bạn có thể tham khảo tài liệu chính thức về Power Query từ Microsoft.