Power Query – Chỉnh sửa truy vấn
Trong phần này của loạt bài viết về Power Query, chúng ta sẽ cùng tìm hiểu cách chỉnh sửa truy vấn Power Query. Ở một bài viết trước mình đã hướng dẫn sơ qua, nhưng bây giờ Học Excel Online sẽ giúp bạn đi sâu hơn nhiều.
Xem nhanh
Tải xuống các tệp mẫu
Nếu nhìn các ví dụ được cắt ra bên dưới bạn cũng có thể hiểu nó, nhưng bạn sẽ lưu giữ thông tin tốt hơn nhiều nếu bạn tải các ví dụ về và thực hành cùng mình.
Nhấp vào Downloads để tải nhé!
Tất cả các ví dụ trong bài viết này sử dụng Example 7 – Edit Queries.xlsx từ bản tải xuống. Nó là một sổ làm việc Excel đã chứa một số truy vấn cơ bản.
Bây giờ thì mở tệp lên và bắt đầu thôi.
Chỉnh sửa một truy vấn hiện có
Cũng như nhiều tính năng khác của Power Query, có nhiều cách để truy cập vào cùng một hành động. Việc mở Power Query để chỉnh sửa một truy vấn hiện có cũng thế, có vô số cách để thực hiện. Mình sẽ trình bày ngắn gọn những điều phổ biến nhất bên dưới, mặc dù chúng ta chỉ thực sự cần biết một hoặc hai.
Mở Power Query
Queries & Connections – Nhấp đúp
Phương pháp dễ nhất là nhấp vào Data -> Queries & Connections.
Cửa sổ Queries & Connections sẽ mở ra, chỉ cần nhấp đúp vào tên của truy vấn.
Queries & Connections – Nhấp chuột phải – chọn Edit
Trong cửa sổ Queries & Connections, chúng ta có thể nhấp chuột phải vào truy vấn và chọn chỉnh sửa(edit).
Queries & Connections – Di chuột chọn Edit
Khi di chuột qua một truy vấn (không nhấp, chỉ di chuột), Excel sẽ hiển thị tóm tắt truy vấn. Nhấp vào tùy chọn Edit ở dưới cùng.
Khởi chạy Power Query Editor
Đã được hướng dãn cụ thể trong bài viết Chuẩn hóa dữ liệu bằng Power Query. Một phương pháp khác là mở trình soạn thảo Power Query mà không cần chọn một truy vấn cụ thể
Nhấp vào Data -> Get Data -> Launch Power Query Editor.
Query Tools
Vẫn còn một phương pháp nữa để bạn thử, hãy chọn bất kỳ ô nào trong bảng truy vấn. Một dải Query mới sẽ xuất hiện. Nhấp vào Query -> Edit.
Chọn một truy vấn để chỉnh sửa
Khi trình chỉnh sửa Power Query mở ra, chúng ta có thể chỉnh sửa bất kỳ truy vấn nào tồn tại trong sổ làm việc. Bạn có thể mở rộng danh sách truy vấn bằng cách nhấp vào mũi tên ở trên cùng bên trái của bản xem trước dữ liệu.
Danh sách truy vấn sẽ mở ra.
Nhấp vào bất kỳ truy vấn riêng lẻ nào để kích hoạt truy vấn đó để xem hoặc chỉnh sửa.
Close & Load
Trong rất nhiều các bài trong loạt bài về Power Query này, chúng ta chỉ sử dụng Close & Load khi tạo các truy vấn. Chúng ta chỉ cần tải các truy vấn đơn lẻ vào Excel. Tuy nhiên bạn lưu ý rằng Close & Load được áp dụng cho tất cả các truy vấn trong cửa sổ làm việc. Vì vậy, nếu bạn thực hiện thay đổi cho nhiều truy vấn, sau đó cập nhật với Close & Load thì tất cả các truy vấn sẽ được cập nhật, không chỉ những truy vấn đang hoạt động.
Đóng mà không lưu thay đổi
Sau khi chỉnh sửa truy vấn, chúng ta có thể quyết định rằng chúng ta không muốn tải các thay đổi vào Excel. Vậy để loại bỏ các thay đổi đó, bạn hãy nhấp vào File -> Discard and Close
Ngoài ra, bạn có thể nhấp vào nút đóng [X] (trên cùng bên phải của cửa sổ). Sẽ có một cửa sổ xác nhận để đảm bảo chúng ta không nhấp nhầm vào nó.
Chỉnh sửa các bước
Bây giờ chúng ta đang ở trong Power Query và có thể di chuyển tự do giữa các truy vấn và đã sẵn sàng để bắt đầu chỉnh sửa các bước riêng lẻ. Trong phần này, chúng ta sẽ làm việc với tệp ví dụ để sửa đổi các phép biến đổi hiện có.
Bạn có thể sử dụng một trong các phương pháp trên để mở Power Query và bắt đầu chỉnh sửa một số chuyển đổi.
Cài đặt cơ bản
Như chúng ta đã thấy trong một bài viết trước, biểu tượng bánh răng tồn tại bên cạnh nhiều bước. Nhấp vào nó sẽ hiển thị cửa sổ tùy chọn cho bước cụ thể đó.
Chọn truy vấn Top Customers, sau đó nhấp vào biểu tượng bánh răng bên cạnh bước Keep First Rows.
Cửa sổ Keep Top Rows sẽ mở ra. Truy vấn hiện đang hiển thị 5 khách hàng, hãy thay đổi thành 3 và nhấp vào OK.
Thêm, chèn, xóa và di chuyển các bước
Chọn truy vấn TopCustomers và nhấp vào bước được gọi là Calculated End of Month. Bản xem trước dữ liệu hiển thị tất cả các bước cho đến khi kết thúc lựa chọn hiện tại.
Để chèn thêm một bước, hãy chọn một bước hiện có, sau đó thực hiện các phép biến đổi mà chúng ta muốn áp dụng tiếp theo.
Nhấp vào menu thả xuống của cột Date và chỉ chọn ngày 31 tháng 1 năm 2019 (tùy thuộc vào định dạng ngày địa phương của bạn, nó có thể hơi khác đối với bạn).
Vậy là một bước mới đã được thêm vào giữa các bước.
Tiếp tục nhấp qua các bước khác để xem các phép biến đổi:
Khi bạn thay đổi, di chuyển hoặc chèn các bước, điều đó có thể gây ra sự cố sau này trong truy vấn bởi vì những thay đổi bạn thực hiện sẽ không chuyển qua các bước tiếp theo một cách chính xác. Mình sẽ cho bạn xem.
Nhấp vào bước Removed Columns. Thay đổi tiêu đề của cột Date thành Month end..
Lúc này một thông báo cảnh báo sẽ được hiển thị. Không phải tất cả các thông báo cảnh báo đều xấu; bạn hãy xem kỹ nó có liên quan đến việc bạn đang làm hay không. Trong ví dụ này, chúng ta đang cố gắng xác định lỗi, vì vậy hãy nhấp vào Insert.
Bước mới đã được thêm vào (xem ảnh chụp màn hình bên dưới).
Bây giờ hãy nhấp vào bước Calculated End of Month . Ôi trời… thật có lỗi. Điều này là do bước đó đang cố gắng tìm một cột có tên là Date, cột này không còn ở đó nữa.
Nếu bạn vẫn mở Formula Bar, bạn có thể thấy tham chiếu đến cột Date không còn nữa (xem ảnh chụp màn hình ở trên)
Bây giờ bạn có một số điều phải cân nhắc. Bạn có chắc chắn cần phải chèn bước đó vào thời điểm này không? Bạn có nên xóa và thực hiện lại các bước bị ảnh hưởng không? Bạn có nên tạo mã M của các bước còn lại để sao chép thay đổi không? Chỉ bạn mới có thể đưa ra quyết định này. Thực ra khi bạn đã rơi vào tình huống này một vài lần, bạn sẽ sớm tìm ra cách tốt nhất thôi.
Nếu bạn chọn xóa tất cả các bước còn lại, hãy nhấp chuột phải vào bước đó và chọn Delete Until End.
Vì đây chỉ là minh họa về các vấn đề có thể xảy ra với các bước thay đổi, hãy xóa bước Renamed Columns (Cột đã đổi tên) mà chúng ta đã thêm, Nhấp vào X bên cạnh để xóa.
Các bước di chuyển
Để di chuyển một bước lên hoặc xuống có các tùy chọn. Chúng ta có thể nhấp chuột phải vào bước đó và nhấp vào Move Up hoặc Move Down..
Một cách khác đó là nhấp vào một bước duy nhất, giữ nút chuột và kéo bước đó đến vị trí mới.
Như bạn có thể tưởng tượng, việc di chuyển các bước có cùng vấn đề với việc chèn các bước.
Bước bạn vừa làm đã đi về đâu?
Power Query rất thông minh, nó cố gắng nhóm các hành động tương tự lại với nhau.
Chọn truy vấn SalesByDay và đảm bảo bạn đã chọn bước cuối cùng, đó là Removed Columns. Bây giờ chúng ta sẽ thay đổi tiêu đề của mỗi cột:
- Day Name thành Day – một bước mới đã được thêm vào.
- Ngày 31/01/2019 (hoặc ngày tương đương tại địa phương của bạn) thay đổi thành Number of Sales – một bước mới không được thêm vào.
Nhìn lại các bước đã áp dụng…chỉ có một bước được thêm vào, vậy bước thứ hai đã đi đến đâu?
Nhìn vào Formula Bar (nếu bạn mở nó), bạn sẽ thấy rằng cả hai bước đã được kết hợp thành một.
Điều này chỉ xảy ra khi các bước tương tự được thực hiện lần lượt. Nó chắc chắn sẽ giúp giữ cho các bước được áp dụng nhỏ hơn đáng kể so với những bước khác.
Mã M trong thanh công thức & Trình chỉnh sửa nâng cao
Có vẻ như mọi thứ đang trở nên nghiêm trọng, cách giải quyết là chúng ta sẽ thay đổi mã M! Chúng ta có thể chỉnh sửa mã M trực tiếp theo hai cách (1) Thanh công thức (2) Trình chỉnh sửa nâng cao. Cá nhân mình thích dùng thanh công thức hơn nên chúng ta sẽ bắt đầu với với nó nhé!
Thanh công thức
Nếu thanh công thức không hiển thị, chúng ta có thể bật nó bằng cách nhấp vào View -> Formula Bar
Chọn truy vấn SalesByDay và nhấp vào bước Sorted Rows.
Thanh công thức sẽ hiển thị như sau:
Chúng ta có thể không viết được mã M từ đầu, nhưng điều đó không có nghĩa là chúng ta không thể chỉnh sửa nó. Trong thanh công thức, thay đổi “Day of Week” thành “Day Name”. Nhìn vào cửa sổ xem trước dữ liệu bạn sẽ thấy bây giờ nó được sắp xếp theo thứ tự bảng chữ cái dựa trên Day Name.
Sau đó, thay đổi văn bản có nội dung Order.Ascending thành Order.Descending. Kiểm tra lại cửa sổ xem trước và nhận thấy thứ tự đã thay đổi.
Công thức cuối cùng sẽ giống như sau:
Nhấp vào bước cuối cùng trong truy vấn. Bản xem trước dữ liệu bây giờ trông như thế này.
Những thay đổi chúng ta thực hiện trong thanh công thức ở trên đã được phản ánh trong truy vấn của chúng ta.
Trình chỉnh sửa nâng cao
Chúng ta có thể thực hiện những thay đổi tương tự trong Trình chỉnh sửa nâng cao. Nhấp vào Home -> Advanced Editor.
Advanced Editor sẽ hiển thị từng bước, chúng ta phải xác định dòng có liên quan, sau đó hãy thực hiện các thay đổi tương tự.
Tải vào Excel
Cuối cùng, hãy nhấp vào Close and Load. Những thay đổi chúng ta đã thực hiện sẽ được tải lên các Bảng trên trang tính.
Việc chỉnh sửa truy vấn trong Power Query có thể thực hiện bằng nhiều cách như vậy đó. Mỗi cách đều có ưu và nhược điểm. Học Excel Online sẽ tiếp tục hướng dẫn cụ thể các bạn về Power Query trong loạt bài viết sắp tới!
