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.

Vấn đề khiến nhiều người cuối cùng cũng phải chú ý

Do một số yếu tố thị trường và thuế nhập khẩu, việc mua linh kiện PC ở một số khu vực thường đắt hơn ở Mỹ. Tác giả muốn tìm hiểu xem mình phải trả thêm bao nhiêu cho cùng một linh kiện và liệu đặt hàng trực tiếp từ Amazon hay Newegg thay vì mua tại các nhà bán lẻ địa phương có tốt hơn không. Vì vậy, tác giả đã thu thập dữ liệu giá của một vài tháng về các linh kiện PC chính (CPU, GPU, RAM) mà những cửa hàng địa phương thường nhập khẩu. Một dự án theo dõi đơn giản, phải không? Sai rồi.

Tác giả nhanh chóng nhận được một mớ dữ liệu hỗn độn. Mỗi nhà bán lẻ xuất thông tin của họ bằng các quy ước định dạng khác nhau, khiến việc kết hợp những file gần như không thể. Amazon cung cấp ngày theo định dạng MM/DD/YYYY, còn Newegg thì dùng YYYYMMDD, còn Shopee thì dùng DD-MM-YYYY.

Sự không nhất quán không chỉ dừng lại ở đó. Tên cột thay đổi rất nhiều. Newegg đặt nhãn giá là "retail_price", trong khi Amazon sử dụng "unit_price_usd", và Shopee chọn "price_php". Định dạng giá cũng gặp vấn đề tương tự, một số file hiển thị "₱18.600" bao gồm cả ký hiệu tiền tệ, trong khi một số file khác lại hiển thị số đơn giản như "320". Ngay cả tên thương hiệu cũng thiếu nhất quán, xuất hiện dưới dạng "gigabyte", "GIGABYTE INC." hoặc "Gigabyte Tech" cho cùng một nhà sản xuất trên nhiều file khác nhau.

Việc dọn dẹp và kết hợp dữ liệu này theo cách thủ công sẽ tốn hàng giờ đồng hồ. Bạn phải sao chép và dán giữa các file, tìm và thay thế những giá trị không nhất quán, và xóa từng hàng trống. Việc chuyển đổi PHP sang USD để so sánh giá đồng nghĩa với việc liên tục phải liếc nhìn màn hình khác để xem tỷ giá hối đoái. Nhìn chung, đó là một công việc tẻ nhạt và dễ xảy ra lỗi đến mức nhiều người gần như bỏ cuộc.

Đó là lúc cuối cùng nhiều người cân nhắc sử dụng một tính năng mà những người đam mê Excel luôn nói đến - Power Query. Excel còn cung cấp một số tính năng mạnh mẽ khác, nhưng Power Query là công cụ hoàn hảo cho vấn đề cụ thể này. Vì vậy, sau khi xem một vài hướng dẫn trên YouTube, bạn sẽ ngay lập tức nhận ra mình có thể tiết kiệm được bao nhiêu thời gian khi bắt đầu sử dụng Power Query Editor để dọn dẹp mọi dữ liệu lộn xộn mà đã thu thập từ Internet.

Power Query giúp dọn dẹp dữ liệu dễ dàng

Power Query là công cụ chuyển đổi dữ liệu tích hợp sẵn của Excel, giúp tự động hóa các tác vụ dữ liệu phức tạp. Thay vì phải dọn dẹp thủ công từng ô, bạn chỉ cần dạy Power Query những gì mình muốn một lần, và nó sẽ áp dụng các bước đó cho bất kỳ tập dữ liệu nào. Bạn có thể loại bỏ các mục trùng lặp, tách cột, thay đổi kiểu dữ liệu và xử lý những giá trị bị thiếu chỉ với vài cú nhấp chuột.

Power Query có cách tiếp cận từng bước và giao diện trực quan, giúp bạn dễ dàng theo dõi. Mỗi thao tác bạn thực hiện đều được ghi lại thành một bước có thể được sửa đổi hoặc xóa sau đó. Về cơ bản, bạn đang thao tác dữ liệu chứ không phải viết code - mặc dù nó sẽ tự động tạo code M nếu bạn tò mò.

Power Query kết nối với hầu hết mọi nguồn dữ liệu, bao gồm file Excel, tài liệu CSV, cơ sở dữ liệu, trang web và thậm chí cả dịch vụ đám mây. Chỉ cần thêm một file vào cùng một thư mục, Power Query sẽ tự động tìm nạp và dọn dẹp bảng tính Excel lộn xộn đó.

Cách thiết lập Power Query trong Excel

Power Query được tích hợp sẵn trong Excel 2016 và các phiên bản mới hơn. Vì vậy, bạn không cần cài đặt riêng.

Hãy xem bảng tính doanh số sản phẩm sau đây - nó thật lộn xộn. Nó có định dạng ngày tháng hỗn tạp, tên sản phẩm không nhất quán, giá trị bị thiếu và khoảng trắng thừa ở khắp mọi nơi. Đây là loại tập dữ liệu khiến nhiều người muốn thoát khỏi Excel hoàn toàn.

Bảng tính Excel cho doanh số sản phẩm

Nhưng rất dễ khắc phục điều này bằng Power Query. Sau đây là cách thiết lập phép biến đổi đầu tiên của bạn:

1. Chọn phạm vi dữ liệu hoặc nhấp vào bất kỳ đâu trong tập dữ liệu.
2. Vào mục Data, sau đó nhấp vào From Table/Range.
3. Excel sẽ phát hiện ranh giới dữ liệu của bạn và tạo một bảng.
4. Nhấp vào OK để mở Power Query Editor.
5. Thực hiện phép biến đổi bằng các công cụ trên ribbon.
6. Nhấp vào Close & Load để áp dụng các thay đổi trở lại Excel.

Power Query Editor sẽ mở ra trong một cửa sổ riêng. Môi trường biệt lập này cho phép bạn thử nghiệm mà không ảnh hưởng đến dữ liệu gốc - hãy tưởng tượng nó như một sandbox để dọn dẹp dữ liệu.

Các chuyển đổi của bạn được lưu dưới dạng truy vấn trong bảng Queries. Các truy vấn này có thể tái sử dụng và làm mới. Khi có dữ liệu mới, chỉ cần nhấp chuột phải và chọn Refresh để tự động áp dụng các bước làm sạch tương tự.

Tính năng tự động hóa này rất hữu ích khi xây dựng các công thức tra cứu nhanh như chớp, vì dữ liệu sạch và nhất quán giúp các mối quan hệ trong bảng hoạt động tốt thay vì bị gián đoạn do những khác biệt nhỏ về định dạng.

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.