Power Query: Nhập và chuyển đổi dữ liệu như một chuyên gia

Nếu bạn dành hàng giờ mỗi tuần để tải xuống các tệp CSV, xóa các hàng trống, định dạng ngày tháng và viết các công thức lồng nhau phức tạp chỉ để chuẩn bị dữ liệu cho việc phân tích, thì bạn đang làm việc vất vả hơn mức cần thiết. Chào mừng bạn đến với Power Query—công cụ tự động hóa dữ liệu mạnh mẽ nhất được tích hợp trực tiếp vào Microsoft Excel.

Thường được gọi là "Get & Transform Data" (Lấy & Chuyển đổi dữ liệu), Power Query cho phép bạn kết nối với hầu hết mọi nguồn dữ liệu, làm sạch và định hình lại thông tin, sau đó tải nó vào bảng tính của bạn. Điều tuyệt vời nhất là gì? Nó ghi lại các bước của bạn. Lần tới khi bạn nhận được dữ liệu mới, bạn không cần phải lặp lại công việc thủ công; bạn chỉ cần nhấp vào Refresh (Làm mới).

Trong hướng dẫn toàn diện này, chúng ta sẽ khám phá Power Query là gì, cách điều hướng giao diện của nó và đi qua một ví dụ thực tế về việc chuyển đổi một tập dữ liệu lộn xộn thành thông tin sạch sẽ, sẵn sàng cho phân tích.

Power Query là gì?

Power Query là một công cụ kết nối và chuẩn bị dữ liệu. Trong thế giới quản trị cơ sở dữ liệu, quá trình này được gọi là ETL: Trích xuất (Extract), Chuyển đổi (Transform) và Tải (Load).

• Trích xuất: Lấy dữ liệu từ nhiều nguồn khác nhau (sổ làm việc Excel, tệp CSV và tệp văn bản, trang web, cơ sở dữ liệu SQL, hoặc thậm chí các dịch vụ đám mây).
• Chuyển đổi: Làm sạch và định hình lại dữ liệu. Bao gồm việc lọc hàng, thay đổi kiểu dữ liệu, tách văn bản, hợp nhất các bảng và tạo các cột tính toán.
• Tải: Xuất dữ liệu đã được tinh chỉnh trực tiếp vào một Bảng (Table) Excel, một PivotTable, hoặc Mô hình dữ liệu Power Pivot (Power Pivot Data Model).

Theo cách truyền thống, người dùng Excel thường dựa vào sự kết hợp của các hàm như TRIM, PROPER, SUBSTITUTE, và VLOOKUP kết hợp với việc sao chép và dán thủ công để xử lý các tác vụ này. Power Query thay thế quy trình làm việc tẻ nhạt đó bằng một giao diện trực quan và thân thiện với người dùng.

Tại sao bạn nên học Power Query

Nếu bạn vẫn còn do dự về việc học một công cụ Excel mới, thì đây là lý do tại sao việc làm chủ Power Query sẽ tạo ra sự đột phá cho năng suất của bạn:

• Không yêu cầu viết mã (Zero Coding): Mặc dù Power Query chạy trên một ngôn ngữ công thức gọi là "M", bạn vẫn có thể thực hiện 95% các tác vụ chuyển đổi dữ liệu chỉ bằng cách sử dụng các nút trên thanh công cụ (ribbon) và menu chuột phải.
• Tự động hóa không cần VBA: Bạn không cần phải biết cách viết macro để tự động hóa các tác vụ lặp đi lặp lại. Power Query ghi nhớ các "Bước đã áp dụng" (Applied Steps) của bạn và tự động áp dụng chúng cho dữ liệu mới.
• Xử lý dữ liệu lớn (Big Data): Excel có giới hạn vật lý là 1.048.576 hàng. Power Query có thể kết nối với các tập dữ liệu có hàng triệu hàng, tổng hợp dữ liệu trong nền và chỉ tải kết quả đã tóm tắt vào bảng tính của bạn.
• Chỉnh sửa không phá hủy: Power Query không bao giờ làm thay đổi nguồn dữ liệu gốc của bạn. Nó tạo ra một kết nối, đọc dữ liệu và xuất ra một bản sao đã được làm sạch.

Bắt đầu: Trình chỉnh sửa Power Query (Power Query Editor)

Để truy cập Power Query, hãy mở một sổ làm việc Excel trống và điều hướng đến tab Data trên thanh Ribbon. Hãy tìm nhóm Get & Transform Data (Lấy & Chuyển đổi Dữ liệu) ở phía ngoài cùng bên trái.

Từ đây, bạn có thể nhấp vào Get Data (Lấy dữ liệu) để xem menu thả xuống chứa các nguồn dữ liệu có sẵn. Khi bạn chọn một tệp và nhấp vào "Transform Data" (Chuyển đổi dữ liệu), Excel sẽ mở Power Query Editor trong một cửa sổ mới. Giao diện này bao gồm bốn khu vực chính:

1. Thanh Ribbon: Nằm ở trên cùng, chứa các tab như Home, Transform, Add Column và View. Đây là nơi chứa tất cả các công cụ của bạn.
2. Khung Queries (Truy vấn): Nằm ở bên trái, liệt kê tất cả các kết nối dữ liệu đang hoạt động trong sổ làm việc của bạn.
3. Bản xem trước dữ liệu: Lưới ở trung tâm hiển thị bản xem trước trực tiếp dữ liệu của bạn trông như thế nào ở bước hiện đang được chọn.
4. Khung Query Settings (Cài đặt truy vấn - Applied Steps): Nằm ở bên phải. Đây là phần quan trọng nhất của trình chỉnh sửa. Mỗi khi bạn thay đổi điều gì đó, Power Query sẽ ghi lại tại đây như một bước tuần tự. Bạn có thể xóa, sắp xếp lại hoặc chỉnh sửa các bước này bất cứ lúc nào.

Hướng dẫn từng bước: Làm sạch Dữ liệu Bán hàng lộn xộn

Hãy cùng xem một ví dụ thực tế. Tưởng tượng bạn xuất báo cáo doanh số hàng tuần từ hệ thống CRM của công ty. Bản xuất thô rất lộn xộn, chứa các tiêu đề không cần thiết, các chuỗi văn bản gộp chung và định dạng không nhất quán.

Dưới đây là mẫu dữ liệu thô, lộn xộn của chúng ta:

Xuất từ hệ thống: Báo cáo Doanh số Q3	Column2	Column3
Tạo ngày: 10/01/2023
Rep_ID_Name	Order_Date	Revenue
101-John Doe	2023-08-15	1500.5
102-Jane Smith	09/01/2023	$2,340.00
103-Bob_Jones	23-Sep-2023	850.75

Nếu sử dụng các công thức truyền thống, chúng ta sẽ phải dùng LEFT, RIGHT, FIND và VALUE để trích xuất tên nhân viên kinh doanh và sửa các con số. Thay vào đó, hãy sử dụng Power Query.

Bước 1: Nhập dữ liệu

Lưu dữ liệu lộn xộn dưới dạng tệp CSV hoặc Excel. Mở một sổ làm việc Excel mới, vào Data > Get Data > From File (Dữ liệu > Lấy dữ liệu > Từ tệp), và chọn tệp của bạn. Khi cửa sổ xem trước xuất hiện, nhấp vào Transform Data. Trình chỉnh sửa Power Query Editor sẽ mở ra.

Bước 2: Xóa các hàng trên cùng và Đưa lên làm Tiêu đề

Hai hàng đầu tiên trong dữ liệu của chúng ta là siêu dữ liệu (metadata) được hệ thống xuất ra, không phải là bản ghi dữ liệu thực tế. Chúng ta cần loại bỏ chúng.

• Trên tab Home, nhấp vào Remove Rows > Remove Top Rows (Xóa Hàng > Xóa Các Hàng Trên Cùng). Nhập "2" và nhấp OK.
• Giờ đây, các tiêu đề cột thực sự của chúng ta đang nằm ở hàng 1. Nhấp vào nút Use First Row as Headers (Sử dụng Hàng đầu tiên làm Tiêu đề) trên tab Home. Ngay lập tức, "Rep_ID_Name", "Order_Date" và "Revenue" sẽ trở thành tiêu đề cột chính thức của bạn.

Bước 3: Tách cột Rep (Nhân viên kinh doanh)

Cột "Rep_ID_Name" chứa cả mã số (ID) và tên của nhân viên, được phân tách bằng một dấu gạch ngang.

• Nhấp chuột phải vào tiêu đề cột "Rep_ID_Name".
• Chọn Split Column > By Delimiter (Tách cột > Theo dấu phân cách).
• Chọn "Custom" (Tùy chỉnh) và nhập dấu gạch ngang ( - ). Nhấp OK.
• Power Query sẽ tách dữ liệu thành hai cột. Bây giờ, bạn có thể nhấp đúp vào tiêu đề các cột mới để đổi tên chúng thành "Rep_ID" và "Rep_Name".

Để làm sạch các dấu gạch dưới trong tên của Bob (Bob_Jones), hãy nhấp chuột phải vào cột Rep_Name, chọn Replace Values (Thay thế giá trị), gõ dấu gạch dưới (_) vào ô "Value to Find" (Giá trị cần tìm), và để trống ô "Replace With" (Thay thế bằng) hoặc thêm một dấu cách. Nhấp OK.

Bước 4: Chuẩn hóa kiểu dữ liệu

Bạn có chú ý rằng định dạng ngày tháng và doanh thu của chúng ta hoàn toàn khác nhau không? Power Query giúp việc chuẩn hóa điều này trở nên dễ dàng.

• Nhấp vào biểu tượng nhỏ ở phía bên trái tiêu đề cột "Order_Date" (nó có thể hiển thị "ABC" hoặc "Any"). Đổi nó thành Date (Ngày). Power Query sẽ tự động chuyển các định dạng ngày khác nhau thành một chuẩn ngày tháng thống nhất.
• Nhấp vào biểu tượng cạnh "Revenue" và đổi thành Currency (Tiền tệ) (hoặc Decimal Number - Số thập phân). Thao tác này sẽ loại bỏ các ký hiệu đô la và định dạng mọi thứ một cách sạch sẽ.

Bước 5: Thêm Cột Điều kiện

Giả sử chúng ta muốn phân loại doanh số trên $1.000 là "High Value" (Giá trị cao). Thay vì phải viết một hàm IF phức tạp như =IF(C2>=1000, "High Value", "Standard") trong Excel, chúng ta có thể sử dụng giao diện (UI) của Power Query.

Vào tab Add Column (Thêm cột) và nhấp vào Conditional Column (Cột Điều kiện). Đặt các quy tắc: Nếu (If) [Revenue] lớn hơn hoặc bằng (is greater than or equal to) 1000, xuất ra "High Value", ngược lại (else) xuất "Standard". Ở chế độ nền, Power Query sẽ tự động tạo mã M sau cho bước này:

= Table.AddColumn(#"Changed Type", "Sales Category", each if [Revenue] >= 1000 then "High Value" else "Standard")

Hợp nhất các truy vấn (Merge Queries): Giải pháp thay thế hiện đại cho VLOOKUP

Một trong những tác vụ phổ biến nhất trong phân tích dữ liệu là kết hợp các bảng. Nếu bạn có một bảng riêng biệt chứa khu vực của từng Nhân viên kinh doanh, thông thường bạn sẽ tìm đến hướng dẫn toàn diện về VLOOKUP của chúng tôi để đưa dữ liệu đó vào.

Tuy nhiên, việc chạy hàng nghìn công thức VLOOKUP hoặc INDEX và MATCH có thể làm bảng tính của bạn chậm đi đáng kể. Trong Power Query, bạn sẽ sử dụng tính năng Merge Queries (Hợp nhất các truy vấn).

Chỉ cần nhập cả hai bảng vào Power Query, chọn bảng doanh số chính của bạn và nhấp vào Merge Queries trên tab Home. Chọn bảng thứ hai (bảng Regions - Khu vực), nhấp vào cột khớp nhau ở cả hai bảng (ví dụ: "Rep_ID"), rồi nhấp OK. Power Query sẽ thực hiện thao tác tương đương với một hàm VLOOKUP siêu tốc chỉ trong vài giây, bất kể bạn có mười hàng hay mười triệu hàng.

Bỏ xoay dữ liệu (Unpivot): Vũ khí bí mật của Power Query

Thông thường, bạn sẽ nhận được dữ liệu đã được nhóm sẵn vào một cấu trúc giống như bảng pivot (ví dụ: các tháng chạy ngang qua các cột: Jan, Feb, Mar, Apr). Mặc dù cấu trúc này dễ đọc đối với con người, nhưng nó lại rất tệ cho việc tạo biểu đồ hoặc PivotTable.

Chọn các cột định danh (identifier columns) của bạn (chẳng hạn như Tên nhân viên - Rep Name), nhấp chuột phải vào tiêu đề và chọn Unpivot Other Columns (Bỏ xoay các cột khác). Power Query sẽ ngay lập tức chuyển đổi dữ liệu dạng bảng chéo, rộng của bạn thành một bố cục dạng bảng, phẳng với một cột "Attribute" (Thuộc tính, tức Tháng) và một cột "Value" (Giá trị, tức Doanh số) mới. Việc thực hiện điều này bằng các công thức Excel tiêu chuẩn gần như là không thể, khiến Unpivot trở thành một trong những tính năng được ca ngợi nhất của Power Query.

Tải Dữ liệu đã chuyển đổi

Khi dữ liệu của bạn đã hoàn toàn sạch sẽ, đã đến lúc gửi nó trở lại Excel.

Trên tab Home, nhấp vào Close & Load (Đóng & Tải). Theo mặc định, lệnh này sẽ tải dữ liệu đã chuyển đổi của bạn vào một Bảng Excel (Excel Table) mới, có màu xanh lá trên một trang tính mới. Nếu muốn đưa thẳng dữ liệu sang giai đoạn phân tích, bạn có thể nhấp vào mũi tên thả xuống, chọn Close & Load To... (Đóng & Tải vào...), và chọn PivotTable Report thay thế. Nếu bạn cần ôn lại cách xây dựng các bảng tổng hợp này, hãy xem qua hướng dẫn tạo pivot table cho người mới bắt đầu của chúng tôi.

Điều kỳ diệu của nút "Refresh" (Làm mới)

Sức mạnh thực sự của Power Query sẽ trở nên rõ ràng vào tuần tới, khi bạn nhận được một bản xuất doanh số thô mới. Đừng lặp lại các bước ở trên nhé!

Bạn chỉ cần lưu đè tệp CSV mới lên tệp cũ (giữ chính xác tên tệp và vị trí thư mục đó). Sau đó, hãy mở sổ làm việc Excel của bạn, nhấp chuột phải vào bất kỳ đâu trong bảng dữ liệu sạch và nhấp vào Refresh.

Power Query sẽ kết nối tới tệp đó, áp dụng lại từng bước một—xóa hàng, đưa lên làm tiêu đề, tách cột, thay thế văn bản, kiểm tra điều kiện, và hợp nhất các bảng—sau đó cập nhật kết quả đầu ra cuối cùng của bạn chỉ trong chớp mắt. Đây là một thành phần quan trọng trong các quy trình tự động hóa Excel.

Đơn giản hóa Hành trình Dữ liệu của bạn bằng AI

Mặc dù Power Query xử lý các chuyển đổi cấu trúc một cách tuyệt vời, nhưng đôi khi bạn cần các logic điều kiện cụ thể hoặc phân tích cú pháp văn bản phức tạp đòi hỏi phải có công thức Excel nâng cao hoặc mã M tùy chỉnh. Thay vì sục sạo các diễn đàn để tìm câu trả lời, bạn có thể tận dụng trí tuệ nhân tạo (AI).

Nếu bạn thấy khó khăn trong việc viết công thức tính toán hoàn hảo cho một cột tùy chỉnh, thì GPTExcel sẽ là một người bạn đồng hành lý tưởng. Bạn chỉ cần mô tả điều bạn muốn đạt được bằng ngôn ngữ tự nhiên—ví dụ: "Tôi cần một công thức chỉ trích xuất các con số từ một chuỗi văn bản hỗn hợp"—và GPTExcel sẽ ngay lập tức tạo ra công thức hoặc mã M chính xác. Việc kết hợp Power Query với AI để làm sạch dữ liệu sẽ mang đến cho bạn một bộ công cụ phân tích dữ liệu vô đối.

Các câu hỏi thường gặp

Power Query có làm thay đổi tệp dữ liệu gốc của tôi không?

Không. Power Query tạo ra một kết nối một chiều đến dữ liệu nguồn của bạn. Nó đọc dữ liệu, áp dụng các chuyển đổi trong bộ nhớ và xuất kết quả mới ra Excel. Tệp CSV, cơ sở dữ liệu hoặc sổ làm việc gốc của bạn hoàn toàn không bị thay đổi và luôn an toàn.

Tôi có thể sử dụng Power Query trên máy Mac không?

Có, Microsoft đã cải thiện đáng kể tính năng hỗ trợ Power Query trong Excel dành cho Mac. Mặc dù theo truyền thống, phiên bản Mac thiếu một số trình kết nối nâng cao và các tính năng giao diện (UI) có trên Windows, nhưng giờ đây bạn có thể kết nối mượt mà với các tệp cục bộ, cơ sở dữ liệu và làm mới các truy vấn hiện có trong các phiên bản Microsoft 365 hiện đại.

Sự khác biệt giữa Merge (Hợp nhất) và Append (Thêm vào cuối) trong Power Query là gì?

Merge tương đương với hàm VLOOKUP hoặc INDEX/MATCH. Bạn sử dụng nó để thêm các cột dữ liệu mới bằng cách đối chiếu một ID chung giữa hai bảng. Append giống như việc sao chép và dán dữ liệu vào dưới cùng của trang tính. Bạn sử dụng nó để xếp chồng các bảng lên nhau, từ đó thêm các hàng mới (ví dụ: kết hợp doanh số tháng Một và doanh số tháng Hai).

Tại sao việc làm mới (refresh) Power Query của tôi bị lỗi?

Lý do phổ biến nhất khiến việc làm mới truy vấn thất bại là do tệp nguồn đã bị di chuyển, đổi tên hoặc bị xóa. Một vấn đề thường gặp khác là tiêu đề cột trong dữ liệu thô đã bị thay đổi (ví dụ: hệ thống đổi "Revenue" thành "Total Revenue"). Bạn có thể khắc phục sự cố này bằng cách mở Power Query Editor, vào khung Applied Steps (Các bước đã áp dụng) và cập nhật bước Nguồn (Source) hoặc đổi tên cột trong logic bước của bạn.