Power Query được tích hợp sẵn trong các phiên bản Windows của Office 365, Excel 2016, Excel 2019 và có sẵn dưới dạng tải xuống miễn phí trong các phiên bản Windows của Excel 2010 và Excel 2013. Công cụ này được thiết kế để trích xuất, chuyển đổi và tải dữ liệu vào Excel từ một đa dạng các nguồn. Phần hay nhất: Power Query ghi nhớ các bước của bạn và sẽ phát lại khi bạn muốn làm mới dữ liệu. Điều này có nghĩa là bạn có thể làm sạch dữ liệu vào Ngày 1 trong 80% thời gian bình thường và bạn có thể làm sạch dữ liệu vào Ngày 2 đến 400 bằng cách chỉ cần nhấp vào Làm mới.
Tôi nói điều này về rất nhiều tính năng mới của Excel, nhưng đây thực sự là tính năng tốt nhất để sử dụng Excel trong 20 năm.
Tôi kể một câu chuyện trong cuộc hội thảo trực tiếp của mình về cách Power Query được phát minh như một giá đỡ cho những khách hàng của Dịch vụ Phân tích SQL Server, những người buộc phải sử dụng Excel để truy cập Power Pivot. Nhưng Power Query tiếp tục trở nên tốt hơn và mọi người sử dụng Excel nên dành thời gian để tìm hiểu Power Query.
Nhận Power Query
Bạn có thể đã có Power Query. Nó nằm trong nhóm Get & Transform trên tab Data.
Nhưng nếu bạn đang sử dụng Excel 2010 hoặc Excel 2013, hãy truy cập Internet và tìm kiếm Tải xuống Power Query. Các lệnh Power Query của bạn sẽ xuất hiện trên tab Power Query chuyên dụng trong Ribbon.
Làm sạch dữ liệu lần đầu tiên trong Power Query
Để cung cấp cho bạn một ví dụ về một số điều tuyệt vời của Power Query, hãy nói rằng bạn nhận được tệp hiển thị bên dưới mỗi ngày. Cột A không được điền. Các phần sẽ đi ngang thay vì xuống trang.
Để bắt đầu, hãy lưu sổ làm việc đó vào ổ cứng của bạn. Đặt nó ở một nơi có thể đoán trước với một cái tên mà bạn sẽ sử dụng cho tệp đó hàng ngày.
Trong Excel, hãy chọn Lấy dữ liệu, Từ tệp, Từ sổ làm việc.
Duyệt đến sổ làm việc. Trong ngăn Xem trước, bấm vào Trang 1. Thay vì nhấp vào Tải, hãy nhấp vào Chỉnh sửa. Bây giờ bạn thấy sổ làm việc trong một lưới hơi khác - lưới Power Query.
Bây giờ bạn cần sửa tất cả các ô trống trong cột A. Nếu bạn làm điều này trong giao diện người dùng Excel, chuỗi lệnh khó sử dụng là Trang chủ, Tìm & Chọn, Đi đến Đặc biệt, Khoảng trống, Bằng, Mũi tên Lên, Ctrl + Enter .
Trong Power Query, chọn Biến đổi, Tô, Xuống.
Tất cả các giá trị null được thay thế bằng giá trị ở trên. Với Power Query, phải mất ba lần nhấp chuột thay vì bảy lần.
Vấn đề tiếp theo: Các quý đang tăng thay vì giảm. Trong Excel, bạn có thể khắc phục điều này bằng bảng tổng hợp Nhiều Phạm vi Hợp nhất. Điều này yêu cầu 12 bước và hơn 23 lần nhấp.
Trong Power Query, hãy chọn hai cột không phải là phần tư. Mở menu thả xuống Bỏ chia cột trên tab Chuyển đổi và chọn Bỏ chia các cột khác, như được hiển thị bên dưới.
Nhấp chuột phải vào cột Thuộc tính mới tạo và đổi tên nó là Quý thay vì Thuộc tính. Hơn hai mươi lần nhấp trong Excel trở thành năm lần nhấp trong Power Query.
Bây giờ, công bằng mà nói, không phải mọi bước dọn dẹp trong Power Query đều ngắn hơn trong Excel. Xóa cột vẫn có nghĩa là nhấp chuột phải vào cột và chọn Xóa cột. Nhưng thành thật mà nói, câu chuyện ở đây không phải là về việc tiết kiệm thời gian trong Ngày 1.
Nhưng hãy đợi: Power Query ghi nhớ tất cả các bước của bạn
Nhìn ở phía bên phải của cửa sổ Power Query. Có một danh sách được gọi là Các bước áp dụng. Nó là một dấu vết kiểm tra tức thì tất cả các bước của bạn. Nhấp vào bất kỳ biểu tượng bánh răng nào để thay đổi lựa chọn của bạn trong bước đó và thay đổi liên tục qua các bước trong tương lai. Nhấp vào bất kỳ bước nào để xem dữ liệu trông như thế nào trước bước đó.
Khi hoàn tất việc dọn dẹp dữ liệu, hãy nhấp vào Close & Load như hình bên dưới.
tiền boa
Nếu dữ liệu của bạn nhiều hơn 1.048.576 hàng, bạn có thể sử dụng menu thả xuống Đóng & Tải để tải dữ liệu trực tiếp vào Mô hình Dữ liệu Power Pivot, có thể chứa 995 triệu hàng nếu bạn đã cài đặt đủ bộ nhớ trên máy.
Trong vài giây, dữ liệu đã chuyển đổi của bạn sẽ xuất hiện trong Excel. Tuyệt vời.
Phần thưởng: Làm sạch dữ liệu vào ngày mai chỉ với một cú nhấp chuột
Nhưng một lần nữa, câu chuyện về Power Query không phải về việc tiết kiệm thời gian vào Ngày 1. Khi bạn chọn dữ liệu được trả về bởi Power Query, bảng Truy vấn & Kết nối xuất hiện ở bên phải Excel và trên đó là nút Làm mới. (Chúng tôi cần nút Chỉnh sửa ở đây, nhưng vì không có nút này, bạn phải nhấp chuột phải vào truy vấn ban đầu để xem hoặc thực hiện thay đổi đối với truy vấn ban đầu).
Thật thú vị khi làm sạch dữ liệu vào Ngày 1. Tôi thích làm điều gì đó mới. Nhưng khi người quản lý của tôi xem báo cáo kết quả và nói “Đẹp. Bạn có thể làm điều này mỗi ngày không? ” Tôi nhanh chóng chán ghét công việc dọn dẹp cùng một tập dữ liệu mỗi ngày.
Vì vậy, để chứng minh Ngày 400 làm sạch dữ liệu, tôi đã thay đổi hoàn toàn tệp gốc. Sản phẩm mới, khách hàng mới, số lượng ít hơn, nhiều hàng hơn, như hình bên dưới. Tôi lưu phiên bản mới này của tệp theo cùng một đường dẫn và có cùng tên tệp với tệp gốc.
Nếu tôi mở sổ làm việc truy vấn và bấm Làm mới, trong vài giây, Power Query báo cáo 92 hàng thay vì 68 hàng.
Làm sạch dữ liệu vào Ngày 2, Ngày 3, Ngày, 4,… Ngày 400,… Ngày Infinity giờ chỉ cần hai lần nhấp.
Ví dụ này chỉ làm xước bề mặt của Power Query. Nếu bạn dành hai giờ với cuốn sách, M dành cho (Dữ liệu) Khỉ của Ken Puls và Miguel Escobar, bạn sẽ tìm hiểu về các tính năng khác, chẳng hạn như sau:
- Kết hợp tất cả các tệp Excel hoặc CSV từ một thư mục thành một lưới Excel duy nhất
- Chuyển đổi một ô với Apple; Banana; Cherry; thì là; Cà tím thành năm hàng trong Excel
- Thực hiện VLOOKUP cho sổ làm việc tra cứu khi bạn đang đưa dữ liệu vào Power Query
- Tạo một truy vấn duy nhất thành một hàm có thể được áp dụng cho mọi hàng trong Excel
Để có mô tả đầy đủ về Power Query, hãy xem M Is for (Data) Monkey của Ken Puls và Miguel Escobar. Vào cuối năm 2019, phiên bản thứ hai được làm lại, Master Your Data, sẽ có sẵn.
Cảm ơn Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser và Colin Michael đã đề cử Power Query.
