Ngừng xóa cột trong Power Query: Hãy làm điều này thay thế

Một trong những điều đầu tiên tôi học về Power Query — và một quy tắc mà tôi vẫn tuân theo một cách nghiêm ngặt cho đến hôm nay — là không được chọn một cột và xóa nó. Để xây dựng một bảng điều khiển thực sự vững chắc, bạn thay vào đó phải nói với trình chỉnh sửa những gì cần giữ, chứ không phải những gì cần loại bỏ. Đây là lý do tại sao 'Remove Other Columns' là người bạn tốt mới của bạn.

Hầu hết các lỗi Power Query không phải là lỗi phần mềm—chúng là các hướng dẫn sai

Khi chúng ta làm sạch dữ liệu trong Power Query, bản năng đầu tiên của chúng ta là nhấp chuột phải vào các cột rác và chọn "Xóa Cột." Điều này có vẻ hiệu quả, nhưng thực ra lại tạo ra hai cạm bẫy sẽ khiến bạn gặp rắc rối sau này.

Giả sử bạn gửi một bảng chín cột đến Trình chỉnh sửa Power Query (thông qua Dữ liệu > Từ Bảng/Phạm vi) và xóa sáu cột bằng cách nhấp chuột phải vào tiêu đề và chọn "Xóa cột", chỉ để lại Product, Units Sold và Profit.

Ngày hôm sau, một đồng nghiệp thêm một cột Ghi chú mới vào bảng tính nguồn. Bởi vì bạn chỉ bảo Power Query xóa sáu cột ban đầu, cột mới, cồng kềnh này sẽ chảy trực tiếp vào bảng điều khiển của bạn ngay khi bạn làm mới, làm lộn xộn báo cáo của bạn.

Sau khi sửa vấn đề đó, bạn làm mới truy vấn của mình vào ngày hôm sau và gặp phải thông báo lỗi. Điều này xảy ra vì một đồng nghiệp khác đã đổi tên cột Department thành "Category" tại nguồn — một cột mà truy vấn của bạn được chỉ dẫn rõ ràng để loại bỏ. Power Query tuân theo hướng dẫn một cách nghiêm ngặt: nếu nó không tìm thấy cột mà nó được chỉ dẫn loại bỏ, nó sẽ báo lỗi và từ chối hoàn tất việc cập nhật. Bảng điều khiển của bạn sẽ bị một chỗ trên dữ liệu cũ cho đến khi bạn sửa bước đó.

Bằng cách chuyển từ việc xóa các cột sang việc xác định những gì cần giữ lại, bạn đảm bảo truy vấn chỉ tìm dữ liệu cần thiết và bỏ qua tất cả phần còn lại.

Cách xác định đúng 'danh sách cho phép' dữ liệu của bạn

Để tránh những bẫy này, bạn cần thay đổi quan điểm của mình. Thay vì xác định những gì cần bỏ đi, bạn nên tập trung vào các trường cụ thể mà báo cáo của bạn yêu cầu để hoạt động. Trong trường hợp này, những cột bắt buộc là Sản phẩm, Số đơn vị bán và Lợi nhuận.

Trong Trình chỉnh sửa Power Query, hãy giữ phím Ctrl và nhấp vào các tiêu đề "Product", "Units Sold" và "Profit". Sau đó, nhấp chuột phải vào bất kỳ một trong ba tiêu đề đã chọn đó và nhấp vào "Remove Other Columns".

Ngăn Các Bước Đã Áp Dụng ở bên phải bây giờ hiển thị một bước mới được gán nhãn Loại Bỏ Các Cột Khác, và mã M cơ bản liệt kê rõ ràng ba cột mà bạn muốn giữ lại:

=Table.SelectColumns(#"Changed Type",{"Product", "Units Sold", "Profit"})

Một dòng mã này thay đổi mọi thứ. Thay vì trừ các cột, hàm đó xác định rõ ràng cấu trúc của bộ dữ liệu của bạn. Power Query bây giờ biết chính xác báo cáo của bạn phụ thuộc vào gì—không hơn, không kém.

Khi bạn đã tách được các cột đó, nhấp vào nửa trên của nút chia 'Đóng & Tải' ở góc trên bên trái của cửa sổ Trình chỉnh sửa để gửi dữ liệu đã làm sạch đến một trang tính mới.

Mặc dù báo cáo đã được làm sạch trông giống hệt như khi bạn sử dụng quy trình Remove Columns, nhưng nó ổn định và mạnh mẽ hơn. Để chứng minh điều này, hãy thêm một cột mới vào bảng nguồn của bạn và thay đổi tiêu đề của một trong các cột không thuộc báo cáo của bạn. Sau đó, chuyển đến trang tính với tập dữ liệu đã được tinh chỉnh và nhấp vào "Refresh" trong tab Query. Bạn sẽ thấy rằng chẳng có gì thay đổi: Power Query bỏ qua cột mới và tiêu đề đã sửa đổi, chỉ tập trung vào ba cột mà bạn đã nói giữ lại.

Quy trình làm việc này cải thiện hiệu suất và sự rõ ràng

Cũng như làm cho bảng điều khiển Excel của bạn bền vững hơn, thói quen này làm cho các workbook Excel của bạn nhanh hơn đáng kể. Bằng cách sử dụng Remove Other Columns càng sớm càng tốt trong truy vấn của bạn, bạn đóng vai trò như một bộ lọc ngăn không cho dữ liệu không cần thiết chảy qua phần còn lại của truy vấn. Điều này càng trở nên quan trọng hơn nếu truy vấn của bạn cung cấp dữ liệu cho Mô hình Dữ liệu hoặc PivotTable, nơi mỗi cột không cần thiết đều làm tăng tải bộ nhớ.

Nếu tệp nguồn của bạn cuối cùng tăng từ 10 cột lên 100 cột, một bước Remove Columns tiêu chuẩn xuất hiện sau trong truy vấn của bạn có thể buộc Power Query phải xử lý tất cả dữ liệu bổ sung đó qua các biến đổi trước đó trước khi cuối cùng xóa nó. Tuy nhiên, sử dụng Remove Other Columns chỉ tách riêng các trường bạn cần ngay từ đầu. Điều này giảm việc sử dụng bộ nhớ và có thể cải thiện đáng kể hiệu suất làm mới, đặc biệt khi làm việc với các bảng Excel lớn hoặc các truy vấn không hỗ trợ query folding.

Cách tiếp cận này cũng làm cho công việc của bạn tự động có tài liệu. Bất kỳ ai kiểm toán các Bước Áp Dụng của bạn đều có thể chọn bước đầu tiên đó và xem chính xác các cột mà toàn bộ báo cáo dựa vào. Sáu tháng sau, khi bạn xem lại tệp, bạn sẽ không cần phải giải mã logic của mình—các cột cần thiết được khai báo rõ ràng trong một bước sạch sẽ.

Bằng cách chọn những gì để giữ lại thay vì những gì để vứt bỏ, bạn đang thêm một trong nhiều quy trình làm việc Power Query quan trọng cần biết vào bộ công cụ của mình. Bạn không chỉ xây dựng một bảng điều khiển không bị hỏng mỗi khi ai đó chỉnh sửa một bảng tính, mà còn giữ cho sổ làm việc của bạn gọn gàng và hiệu quả khi nó phát triển.