Những lệnh Power Query đơn giản giúp tiết kiệm hàng giờ dọn dẹp dữ liệu Excel

Dọn dẹp dữ liệu trong Excel, đặc biệt là đối với nhiều workbook, là một trong những công việc khó chịu nhất mà bất kỳ người dùng Excel nào cũng phải đối mặt. Nhưng với Power Query, công việc đó thậm chí còn trở nên thú vị hơn.

Tách ô bằng dấu phân cách

Dễ dàng tách dữ liệu thành các cột

Một vấn đề thường gặp khi dọn dẹp trang tính Excel là dữ liệu cần được tách thành hai cột lại được gộp thành một. Hãy xem ảnh chụp màn hình bên dưới, trong đó cột Order ID cũng có tên khách hàng.

Chúng ta sẽ tách cột này trong Power Query bằng dấu phân cách, một ký tự hoặc biểu tượng (như dấu phẩy, dấu cách hoặc dấu gạch nối) để phân tách dữ liệu trong một ô. Dấu phân cách là dấu gạch nối hoặc dấu gạch ngang (-) phân tách ID đơn hàng khỏi tên khách hàng trong ví dụ trên.

Giả sử bạn đã lưu trang tính này ở đâu đó trên máy tính. Để nhập, hãy chọn tab Data và nhấp vào Get Data -> From File -> From Excel Workbook trong nhóm lệnh Get & Transform Data trên ribbon.

Từ đó, điều hướng đến vị trí file, chọn file và nhấp vào Import. Trong Display Options ở bên trái, chọn trang tính có dữ liệu và nhấp vào Transform Data. Lưu ý rằng đây chỉ là một cách để nhập dữ liệu vào trình soạn thảo - các tùy chọn sẽ khác nhau tùy thuộc vào nơi bạn lấy dữ liệu.

Trừ ngày

Xác định sự chênh lệch ngày chính xác

Tiếp tục với bảng ví dụ ở trên, chúng ta cũng có thể thực hiện nhiều phép tính khác nhau trên các cột trong trình soạn thảo Power Query. Hãy tận dụng cơ hội này để thêm các cột bổ sung cho một số phép tính nhất định. Cách phổ biến nhất mọi người làm là xác định sự chênh lệch giữa các ngày.

Ở đây, ví dụ muốn tìm số ngày giao hàng của các đơn hàng, vì vậy cần trừ ngày giao hàng cho ngày đặt hàng. Để làm điều đó, hãy chọn cột Delivery Date, nhấn Ctrl, sau đó chọn Order Date (lưu ý thứ tự lựa chọn). Tiếp theo, chọn tab Add Column và nhấp vào Date -> Subtract Days trong nhóm lệnh From Date & Time của ribbon.

Lọc lỗi

Đảm bảo bạn chỉ thấy các hàng bị ảnh hưởng

Power Query không có cách dễ dàng để lọc chỉ hiển thị các hàng có lỗi. Những hàng này có thể gây đau đầu nếu chúng nằm trong một file có hàng trăm hàng trở lên.

Giả sử bạn biết rằng cột Order Date có lỗi và muốn xử lý chúng theo cách thủ công trước khi load dữ liệu vào Excel. Bạn có thể tạo bộ lọc để chỉ hiển thị các hàng bị ảnh hưởng.

Chọn tab Add Column và nhấp vào Custom Column trong nhóm lệnh General của ribbon. Trong vùng văn bản Custom column formula, nhập công thức sau và nhấp vào OK:

try [Order Date]

Power Query sẽ thêm một cột mới có tên là Custom, và các hàng sẽ có giá trị TRUE nếu có lỗi hoặc FALSE nếu không có lỗi. Bây giờ, hãy làm như sau:

1. Nhấp vào biểu tượng mũi tên ở bên trái cột Custom.
2. Bỏ chọn tất cả các mục ngoại trừ Has Error.
3. Nhấp vào OK.
4. Nhấp vào biểu tượng mũi tên bên cạnh cột Custom một lần nữa.
5. Bỏ chọn False.
6. Nhấp vào OK.

Bỏ trục (unpivot) dữ liệu

Hỗ trợ phân tích dữ liệu

Dữ liệu đã được pivot là dữ liệu được cấu trúc theo định dạng rộng, với các giá trị trải rộng trên nhiều cột. Điều này có thể gây khó khăn cho việc phân tích dữ liệu khi sử dụng Pivot Table trong Excel.

Hãy xem ảnh chụp màn hình của bảng bên dưới hiển thị dữ liệu bán hàng theo 3 trạng thái. Vì các tháng là một danh mục, chúng thực sự nên được sắp xếp theo hàng thay vì cột để hỗ trợ phân tích dữ liệu.

Để khắc phục điều này, chúng ta cần bỏ trục dữ liệu, nghĩa là dữ liệu cần được định hình lại thành định dạng cao. Về cơ bản, các trạng thái, tháng và doanh số cần được đặt trên những hàng riêng biệt.

Việc thực hiện thủ công sẽ liên quan đến rất nhiều thao tác sao chép và dán, căn chỉnh giá trị thủ công và đảm bảo không có lỗi. Nhưng Power Query có thể giúp việc này dễ dàng hơn.

Một truy vấn đơn giản mang lại hiệu quả rất lớn

Không hề phóng đại khi nói rằng Power Query sẽ khiến bạn mong chờ việc dọn dẹp dữ liệu Excel. Điều này đặc biệt đúng vì bạn có thể tạo một quy trình tự động để làm sạch nhiều trang tính theo cùng một cách. Và, như bạn thấy, bạn có thể làm được rất nhiều việc chỉ với một vài lệnh đơn giản.