Cách sử dụng Power Query để làm sạch dữ liệu Excel

Mọi người luôn sử dụng Excel để tính toán nhanh và tạo bảng đơn giản. Nhưng ngoài các công thức phổ biến và kỹ thuật xử lý dữ liệu cơ bản, có thể bạn chưa bao giờ cảm thấy cần phải học thêm các chức năng Excel khác - cho đến khi những dự án bắt đầu trở nên phức tạp.

Cách sử dụng Power Query để dọn dẹp dữ liệu

Nhiều người đã quyết định thực hiện một quy trình từng bước đơn giản trong Power Query Editor. Đây chính xác là cách họ dọn dẹp những file xuất CSV lộn xộn đó và biến chúng thành một bảng tính nhất quán, có cấu trúc tốt.

Đầu tiên, hãy nhập dữ liệu vào Power Query Editor bằng cách mở một workbook trống, nhấp vào Data trên ribbon và chọn From Text/CSV. Sau đó, chọn file CSV và nhấp vào Transform Data để mở file bằng Power Query Editor.

Bắt đầu bằng cách sửa cột Date. Vì ví dụ đang thu thập dữ liệu từ hai nguồn có mốc thời gian cách nhau 12 giờ, nên tác giả cần chuẩn hóa ngày tháng. Việc này hóa ra lại cực kỳ đơn giản. chỉ cần chọn cột Date, nhấp chuột phải để mở menu ngữ cảnh và chọn Change Type > Using Locale. Trong menu pop-up, đặt kiểu thành Date và chọn English (United States) để đảm bảo định dạng nhất quán. Sau đó, Power Query tự động nhận dạng các định dạng khác nhau, chẳng hạn như MM/DD/YYYY, YYYY/MM/DD và các biến thể sử dụng những ký hiệu như DD-MM-YY, rồi chuẩn hóa tất cả thành một định dạng ngày tháng duy nhất.

Giờ định dạng ngày tháng đã được sửa, chỉ cần dọn dẹp cột. Có nhiều cách khác nhau để dọn dẹp bảng tính Excel, nhưng vì tất cả lỗi đều là các mục nhập sai do công cụ thu thập dữ liệu gây ra, nên chỉ cần chọn sử dụng bộ lọc Remove Errors để xóa những mục nhập đó. Bước này đã loại bỏ các giá trị null và bất kỳ dữ liệu có vấn đề nào còn sót lại không được ghi lại chính xác, giúp bạn có ngày tháng rõ ràng và nhất quán trên tất cả các file của mình.

Tiếp theo, cần giải quyết tình trạng lộn xộn về tên thương hiệu bằng chức năng Replace Values. Giống như trước đó, chọn cột mục tiêu, sau đó nhấp chuột phải để mở menu ngữ cảnh và chọn Replace Values. Trong cửa sổ pop-up, nhập giá trị không nhất quán vào trường Value to Find và giá trị chuẩn vào trường Replace With.

Làm lại khoảng hai lần nữa và cuối cùng đã biến tất cả các giá trị "gigabyte" và "GIGABTYE Inc." thành một giá trị "GIGABYTE" thống nhất trên mọi file. Làm tương tự với AMD và giờ đây toàn bộ cột Brand cho GPU đều sử dụng tên thương hiệu chuẩn.

Cuối cùng, cần chuẩn hóa cột Price. Việc này hơi khó khăn vì nhiều mục chỉ là số, không có dấu hiệu nào cho biết chúng là đô la Mỹ hay peso Philippines, khiến việc chuyển đổi trở nên khó khăn. May mắn thay, cột Store nằm ngay bên cạnh, cho phép dễ dàng chuyển đổi tất cả giá từ Shoppe PH sang USD.

Cách xử lý vấn đề này là trước tiên sử dụng Replace Values ​​để xóa tất cả $, ₱, USD và PHP khỏi cột Price. Sau đó, chuyển đổi các giá trị thành số thập phân bằng cách sử dụng Change Type > Decimal. Với một cột sạch chỉ chứa số thập phân, hãy tạo một cột mới bằng cách nhấp vào Column (ribbon) > Custom Column và đặt tên là "Price USD". Sau đó, nhập công thức sau:

if [Store] = "Shopee PH"
then Number.Round([Price] / 55, 2)
else Number.Round([Price], 2)

Sau đó nhấn OK. Thao tác này sẽ chuyển đổi tất cả giá từ Shopee PH sang USD để dễ so sánh.

Công thức được viết bằng ngôn ngữ công thức Power Query M. Đây là một ngôn ngữ kịch bản khá đơn giản với khái niệm cốt lõi đơn giản, dễ nắm bắt. Bạn không cần phải học toàn bộ ngôn ngữ để sử dụng nó hiệu quả. Nhiều người chỉ sử dụng nó làm tài liệu tham khảo bất cứ khi nào cần xây dựng công thức của riêng mình.

Giờ mọi thứ đã ổn định, chỉ cần đóng Power Query Editor và chọn Keep để lưu tất cả các thay đổi trong workbook đã tạo.

Power Query sẽ giúp tiết kiệm hàng giờ đồng hồ

Một lý do khiến nhiều người tránh sử dụng Power Query là vì họ cho rằng nó sẽ là một tính năng phức tạp, mất quá nhiều thời gian để học. Nhưng hóa ra nó dễ hơn mọi người tưởng rất nhiều. Thay vì chạy vô số lệnh tìm và thay thế, bạn có thể sử dụng Power Query để nhanh chóng và tự động dọn dẹp dữ liệu khỏi các công cụ thu thập dữ liệu của mình.

Điều khiến chúng ta ngạc nhiên hơn nữa về Power Query là mọi lệnh đã thực hiện đều được ghi lại và có thể được lặp lại nhiều lần. Về cơ bản, điều này cung cấp cho bạn một script dọn dẹp tự động có thể chuyển đổi các file CSV lộn xộn thành những bảng tính gọn gàng, có cấu trúc - hoàn hảo nếu bạn đang tạo các tập dữ liệu tùy chỉnh bằng công cụ thu thập dữ liệu web, vì những công cụ đó thường xuất ra dữ liệu lộn xộn.

Đối với bất kỳ ai phải xử lý việc dọn dẹp dữ liệu lặp đi lặp lại, định dạng không nhất quán hoặc nhiều nguồn dữ liệu, Power Query biến những gánh nặng đó thành một quy trình đơn giản, tự động. Thay vì dành hàng giờ mỗi tuần để sửa lỗi thủ công, bạn chỉ cần nhấn Refresh và bắt đầu phân tích. Đó là tính năng Excel mà nhiều người ước mình đã sử dụng từ lâu. Một khi đã trải nghiệm sức mạnh của một script dọn dẹp tự động, có thể tái tạo, bạn sẽ không còn muốn quay lại cách làm cũ nữa.