Biến dữ liệu phức tạp thành đơn giản với 7 mẹo Power Query trong Excel

7/4/2025

Power Query có thể khiến bạn cảm thấy rối khi mới bắt đầu vì có quá nhiều nút, lỗi lạ và ngôn ngữ công thức riêng biệt. Tuy nhiên, việc thành thạo công cụ mạnh mẽ này hoàn toàn xứng đáng vì nó giúp bạn tiết kiệm rất nhiều thời gian bằng cách tự động hoá việc nhập, làm sạch và chuyển đổi dữ liệu trong Excel.

 

Video hướng dẫn

Tải file ví dụ

Power Query Made Easy Files.zip

 

Tìm hiểu về Power Query

Power Query được tạo ra để đưa dữ liệu vào Excel và xử lý làm sạch dữ liệu. Công cụ này giúp tự động hóa việc thu thập và biến đổi dữ liệu, giúp bạn phân tích dễ dàng hơn.

Tuy nhiên, Power Query không thay thế cho công thức, Pivot Table hay biểu đồ. Thay vào đó, nó hỗ trợ các công cụ đó bằng cách xử lý các bước lặp đi lặp lại trong việc chuẩn bị dữ liệu.

 

Mẹo 1: Nhập và gộp dữ liệu từ nhiều file

Một trong những tính năng nổi bật của Power Query là khả năng nhập dữ liệu từ nhiều nguồn như file CSV, cơ sở dữ liệu và thư mục. Nếu bạn có nhiều file CSV chứa dữ liệu tương tự, Power Query có thể gộp chúng thành một bảng duy nhất.

Cách gộp các file CSV trong một thư mục:

1. Vào tab Data trong Excel, tìm mục Get & Transform Data.
2. Chọn From Folder và chọn đến thư mục chứa các file CSV.
3. Power Query sẽ hiển thị danh sách các các trong thư mục. Nhấn Combine and Transform để gộp chúng.
4. Xem trước kết quả, sau đó nhấn OK để mở Power Query Editor.

Kết quả là tất cả dữ liệu từ các các CSV sẽ được xếp chồng thành một bảng duy nhất, sẽ rất lý tưởng khi bạn cần sử dụng để tổng hợp báo cáo theo tuần hoặc tháng.

 

Mẹo 2: Làm sạch dữ liệu bằng cách xóa hàng trống và cột không cần thiết

Sau khi nhập dữ liệu, bước tiếp theo là làm sạch. Power Query hỗ trợ bạn dễ dàng với các công cụ lọc và chuyển đổi tích hợp sẵn.

Cách xóa hàng trống (giá trị null):

1. Nhấn vào biểu tượng filter ở đầu cột bất kỳ.
2. Bỏ chọn mục null để loại bỏ các hàng trống.

Cách xóa cột không cần thiết:

1. Nhấp chuột phải vào tiêu đề cột và chọn Remove, hoặc chỉ cần nhấn Delete.

Sau khi làm sạch xong, nhấn Close & Load (trong tab Home) để xuất dữ liệu đã xử lý sang một worksheet mới trong Excel.

 

Mẹo 3: Dùng “Applied Steps”

Một tính năng cực kỳ hữu ích của Power Query là Applied Steps nằm bên phải cửa sổ chỉnh sửa. Mỗi thao tác bạn thực hiện sẽ được ghi lại theo thứ tự.

Bạn có thể xem đây như một "cỗ máy thời gian", vì nó cho phép bạn:

• Nhấp vào bất kỳ bước nào để xem dữ liệu ở thời điểm đó.
• Xóa hoặc sắp xếp lại các bước để chỉnh sửa sai sót.
• Chỉnh sửa bước nếu dữ liệu hoặc yêu cầu thay đổi.

Khi bạn refresh truy vấn, Power Query sẽ tự động chạy lại toàn bộ các bước này trên dữ liệu mới. Ví dụ, nếu bạn thêm một file CSV mới vào thư mục, chỉ cần làm mới truy vấn và dữ liệu mới sẽ được cập nhật mà không cần thao tác lại từ đầu.

 

Mẹo 4: Quản lý Data Types cẩn thận để tránh lỗi

Kiểu dữ liệu đóng vai trò quan trọng trong Power Query và cũng là nguyên nhân phổ biến gây lỗi, đặc biệt với người mới. Mỗi cột đều có kiểu dữ liệu riêng như số nguyên, văn bản hay số thập phân.

Power Query sẽ tự động nhận dạng kiểu dữ liệu khi tạo truy vấn, nhưng không phải lúc nào cũng chính xác. Ví dụ, cột có dấu gạch ngang (“-”) có thể bị hiểu sai thành văn bản thay vì số, gây lỗi khi xử lý.

Cách xử lý:

1. Xác định các giá trị gây lỗi như dấu “-”.
2. Dùng tính năng Replace Values trong tab Transform để thay bằng số 0 hoặc giá trị phù hợp.
3. Sau khi làm sạch, chuyển cột về đúng kiểu dữ liệu.

Việc đảm bảo đúng kiểu dữ liệu sẽ giúp bạn tránh rắc rối khi phân tích sau này bằng công thức hay Pivot Table.

 

Mẹo 5: Biết dùng đúng tab để chuyển đổi dữ liệu

Trong Power Query, thanh công cụ có hai tab chính: Transform và Add Column. Cả hai có nhiều nút tương tự nhưng mục đích khác nhau.

• Dùng Transform khi bạn muốn chỉnh sửa trực tiếp cột hiện có.
• Dùng Add Column khi bạn muốn tạo cột mới dựa trên phép tính hoặc trích xuất dữ liệu.

Ví dụ:

• Dùng Week of Year trong tab Transform sẽ thay thế cột ngày bằng số tuần.
• Dùng Week of Year trong tab Add Column sẽ thêm cột mới chứa số tuần, giữ nguyên cột ngày ban đầu.

Chọn đúng thao tác giúp dữ liệu rõ ràng, tránh ghi chồng lên các dữ liệu khác không mong muốn.

 

Mẹo 6: Dùng tính năng AI "Column From Examples" để trích dữ liệu

Power Query có một tính năng thông minh gọi là Column From Examples, cho phép bạn tạo cột mới bằng cách nhập một vài ví dụ đầu ra. Đây là cách trích xuất văn bản hoặc số mà không cần viết công thức phức tạp.

Ví dụ: Nếu bạn có mã vận đơn và muốn tách mã hãng vận chuyển (3 ký tự đầu tiên), hãy làm như sau:

1. Chọn cột chứa mã vận đơn.
2. Vào tab Add Column > chọn Column From Examples > From Selection.
3. Nhập vài ví dụ như “USP”, “DHL” vào cột mới.
4. Power Query sẽ nhận ra quy luật và tự động điền phần còn lại.
5. Nhấn OK để thêm cột mới.

Tính năng này tiết kiệm rất nhiều thời gian và không cần kỹ năng lập trình.

 

Mẹo 7: Khám phá ngôn ngữ M (M Code) trong Power Query

Đằng sau mỗi thao tác trong Power Query là code được tạo bằng ngôn ngữ công thức gọi là M. Bạn có thể xem hoặc chỉnh sửa code này qua formula bar hoặc Advanced Editor.

Dù không cần biết ngôn ngữ M để sử dụng Power Query hiệu quả nhưng nếu bạn có kiến thức cơ bản thì nó có thể giúp bạn thực hiện các thao tác nâng cao và xử lý lỗi dễ hơn.

Ví dụ:

Bạn có thể bắt gặp các hàm như Text.Middle, dùng để trích một phần của chuỗi văn bản. Cú pháp của những hàm này giống với công thức trong Excel, nhưng được thiết kế riêng để xử lý dữ liệu trong Power Query.

Cách xem code M:

• Bật formula bar trong tab View nếu chưa thấy.
• Nhấp vào một bước trong Applied Steps để xem code tương ứng.
• Dùng Advanced Editor để xem/chỉnh toàn bộ script truy vấn.

Đừng lo nếu bạn thấy phức tạp lúc đầu vì Power Query vẫn là công cụ không cần hoặc ít dùng code, hầu hết thao tác có thể thực hiện chỉ với vài cú nhấp chuột.

 

Bổ sung: Dùng Unpivot để chuyển dữ liệu thành dạng phân tích

Một trong những tính năng mạnh mẽ nhất của Power Query là Unpivot, giúp chuyển các cột thành hàng. Đây là bước quan trọng để chuẩn bị dữ liệu cho Pivot Table hoặc phân tích sâu hơn.

Ví dụ:

Bạn có báo cáo với các tháng nằm ngang trên đầu và sản phẩm nằm dọc bên trái. Dạng bảng này khó dùng với Pivot Table hay các hàm.

Cách dùng Unpivot:

1. Tải dữ liệu vào Power Query bằng cách chọn bảng rồi nhấn From Table/Range.
2. Chọn cột bạn muốn giữ nguyên (ví dụ: cột Product).
3. Vào tab Transform > chọn Unpivot Other Columns.
4. Power Query sẽ chuyển các cột tháng thành một cột Month, kèm theo cột Value tương ứng.
5. Đổi tên cột nếu cần, rồi tải dữ liệu về lại Excel.

Chỉ với vài cú nhấp chuột, bạn đã có dữ liệu dạng bảng chuẩn, không cần dùng đến macro phức tạp.

 

Kết luận

Trong hướng dẫn này, bạn đã khám phá 7 mẹo quan trọng giúp bắt đầu và nâng cao hiệu quả khi sử dụng Power Query:

• Hiểu đúng vai trò của Power Query trong việc nhập và làm sạch dữ liệu.
• Nhập và gộp dữ liệu từ nhiều file một cách dễ dàng.
• Làm sạch dữ liệu bằng cách xóa hàng trống và cột thừa.
• Quản lý và tự động hóa các bước xử lý với Applied Steps.
• Kiểm soát kiểu dữ liệu cẩn thận để tránh lỗi.
• Chọn đúng tab công cụ để chuyển đổi hoặc tạo cột mới.
• Tận dụng tính năng AI như Column From Examples.
• Khám phá ngôn ngữ M để xử lý nâng cao khi cần.

Power Query là giải pháp không cần mã (no-code) hoặc ít mã (low-code) giúp bạn tiết kiệm hàng giờ làm các công việc lặp đi lặp lại. Sau khi thiết lập truy vấn, bạn chỉ cần refresh để cập nhật báo cáo với dữ liệu mới nhanh chóng.

 

Nguồn: excelcampus.com