- Power Query cho phép bạn hợp nhất nhiều tệp từ thư mục cục bộ hoặc trên đám mây, miễn là chúng có cùng cấu trúc dữ liệu.
- Việc sử dụng tệp mẫu giúp dễ dàng áp dụng các phép biến đổi hàng loạt, chẳng hạn như xóa các hàng đầu tiên hoặc thay đổi tiêu đề.
- Sẽ có những hạn chế khi các cột không khớp chính xác, đòi hỏi phải chỉnh sửa thủ công hoặc sử dụng các tập lệnh VBA cho các trường hợp phức tạp.
Hãy tưởng tượng bạn có rất nhiều báo cáo hàng tháng hoặc hàng tuần nằm rải rác trong các thư mục, và thay vì lãng phí cả buổi sáng để sao chép và dán dữ liệu một cách vội vã, bạn có thể hợp nhất tất cả vào một bảng tính Excel duy nhất chỉ với vài cú nhấp chuột. Đây chính là lúc Power Query phát huy tác dụng, một công cụ gần như là trợ thủ đắc lực cho bất kỳ ai làm việc với dữ liệu, cho phép chúng ta dễ dàng hợp nhất các tệp có cùng cấu trúc.
Điều tuyệt vời nhất của hệ thống này là nó không giới hạn bạn chỉ sử dụng một thư mục cục bộ trên máy tính; bạn có thể lấy dữ liệu từ các trang SharePoint, Azure Data Lake hoặc Azure blob . Chìa khóa để hệ thống hoạt động trơn tru là các tệp phải có cùng cấu trúc, nghĩa là các cột phải khớp nhau, mặc dù Power Query đủ thông minh để giúp làm sạch dữ liệu nếu nó chứa bất kỳ dữ liệu nhiễu hoặc các hàng không cần thiết nào.
Quy trình kết hợp từng bước
Khi kết nối với nguồn dữ liệu, điều đầu tiên bạn sẽ thấy là chế độ xem hệ thống tập tin. Ở đây, cột quan trọng nhất là Nội dung, chứa biểu diễn nhị phân của mỗi tài liệu. Nếu bạn nhấp vào một trong những giá trị nhị phân này, chương trình sẽ cố gắng xác định xem đó là tập tin CSV, JSON hay Excel để mở nó. Để kết hợp toàn bộ lô dữ liệu, bạn có hai tùy chọn: nhấp trực tiếp vào nút Hợp nhất, hoặc, nếu bạn muốn tỉ mỉ hơn, hãy chọn Chuyển đổi dữ liệu để lọc danh sách tập tin (ví dụ: để bỏ qua các thư mục cụ thể) trước khi tiến hành hợp nhất.
Khi bạn vào hộp thoại hợp nhất, Power Query sẽ sử dụng một tệp mẫu để phân tích cấu trúc . Theo mặc định, nó sẽ chọn tệp đầu tiên trong danh sách, nhưng bạn có thể thay đổi nếu có tệp khác thể hiện tập dữ liệu tốt hơn. Tại đây, bạn có thể chọn tùy chọn bỏ qua các tệp bị lỗi , điều này rất hữu ích khi báo cáo bị hỏng hoặc trống, giúp ngăn toàn bộ quá trình dừng lại.
Quản lý đầu ra và làm sạch dữ liệu
Sau khi chấp nhận việc hợp nhất, hệ thống sẽ thực hiện một số thao tác kỹ thuật ngầm: nó tạo ra một truy vấn mẫu và một hàm tham số. Điều này có nghĩa là bất kỳ thay đổi nào bạn thực hiện đối với tệp mẫu sẽ tự động được sao chép sang tất cả các tệp khác trong thư mục. Ví dụ, nếu tệp CSV của bạn có bốn hàng tiêu đề không cần thiết trước dữ liệu thực tế, bạn có thể vào truy vấn chuyển đổi và sử dụng tùy chọn xóa các hàng đầu để làm sạch phần dữ liệu thừa đó.
Sau khi làm sạch các hàng, điều cần thiết là áp dụng chức năng " Sử dụng hàng đầu tiên làm tiêu đề" . Hãy nhớ rằng khi bạn thực hiện điều này, Power Query thường thêm bước chuyển đổi kiểu dữ liệu tự động. Nếu, khi quay lại truy vấn chính, bạn thấy lỗi báo rằng không tìm thấy cột 'Column1', đừng lo lắng; điều này là bình thường vì bạn đã đổi tên các cột trong ví dụ và bước tiếp theo không còn nhận ra tên cũ nữa. Chỉ cần xóa bước bị lỗi đó khỏi ngăn Các bước đã áp dụng và mọi thứ sẽ trở lại bình thường.
Những thách thức phổ biến và các giải pháp thay thế tiên tiến
Đôi khi cuộc sống không đơn giản như vậy, và chúng ta gặp phải những tập tin không hoàn toàn giống nhau. Ví dụ, nếu bạn có các báo cáo ServiceNow trong đó một tiêu đề có tên là 'resolved_on' và một tiêu đề khác là 'closed_on', Power Query có thể gây ra sự cố vì nó yêu cầu cấu trúc nhất quán . Nếu tập tin mẫu không hoàn toàn khớp với phần còn lại, công cụ có thể bỏ qua thông tin từ các tập tin không khớp hoặc báo lỗi khi nhập dữ liệu.
Đối với những trường hợp cấu trúc hoàn toàn hỗn loạn hoặc khối lượng dữ liệu khổng lồ (vượt quá một triệu dòng Excel), sẽ có những lựa chọn khác. Một số người dùng chọn sử dụng các tập lệnh VBA với giao diện CSV , cho phép ghép các tệp mà không cần lặp lại tiêu đề, bất kể dấu phân cách trường hoặc bản ghi có khác nhau giữa các tệp hay không. Mặc dù Windows Shell cực kỳ nhanh, nhưng nó thường gặp lỗi khi xử lý các ngắt dòng nhúng, trong khi một giải pháp VBA mạnh mẽ không làm quá tải RAM và có thể xử lý hàng triệu bản ghi một cách dễ dàng.
Để hoàn tất quy trình, sau khi dữ liệu được kết hợp trong Power Query, hãy nhớ gán kiểu dữ liệu chính xác cho từng cột (Ngày tháng cho ngày, Tiền tệ cho thu nhập, v.v.) trước khi tải bảng cuối cùng. Bạn có thể xác minh mọi thứ đều chính xác bằng cách lọc cột Tên nguồn để đảm bảo rằng tất cả các tệp trong thư mục đã được xử lý thành công.
Khả năng tự động kết hợp nhiều tệp CSV cho phép bạn chuyển từ công việc thủ công tốn nhiều thời gian sang quy trình làm việc chuyên nghiệp, cho dù bằng cách tận dụng tính linh hoạt của Power Query để làm sạch dữ liệu hay sử dụng VBA khi cấu trúc không tương thích hoặc khối lượng vượt quá giới hạn của bảng tính.