Trong loạt bài trước về Power Query chúng ta đã tìm hiểu cách đưa dữ liệu về dạng chuẩn từ các định dạng tệp khác nhau và tải nó vào Excel. Trong bài viết này Học Excel Online sẽ hướng dẫn cách làm mới dữ liệu, và những hướng dẫn sau đây có thể cho phép bạn truy vấn một lần hoặc sử dụng nhiều lần.

Làm mới tất cả dữ liệu

Các công cụ tính toán có trong Excel theo mặc định sẽ tính toán lại với mọi sự thay đổi. Tuy nhiên Power Query không giống vậy, nó sẽ chỉ tính toán lại khi có lệnh cụ thể. 

Quá trình làm mới khá đơn giản, nhấp vào Data → Refresh all.

Phần sau sẽ sử dụng Example 6 – Data Refresh 1.csv và Example 6 – Data Refresh 2.csv từ các bản tải xuống để làm ví dụ  hiểu rõ hơn.

Tạo một truy vấn cơ bản

Mở cửa sổ làm việc và tạo một truy vấn mới bằng tệp CSV: trên thanh làm việc chọn Data–> Get Data–> From File –> From text/CSV.

Cửa sổ Import data hiển thị. Lựa chọn Example – Data Refresh 1.csv và nhập vào Import.

Power Query sẽ mở một cửa sổ mới và hiển thị một mẫu dữ liệu, nhấp vào Transform Data.

Tiếp theo thực hiện một số chuyển đổi dữ liệu cơ bản để định hình CSV thành thông tin hữu ích hơn. Đây là bài đầu tiên hướng dẫn một số phép biến đổi, điều này sẽ được hướng dẫn rõ hơn trong các phần sau.

Nhấp vào cột Date. Trên thanh làm việc vào Transform –> Date–>Month –>End of month (thao tác này sẽ thay đổi cột Date thành ngày cuối cùng trong tháng theo lịch).

Nhấp vào tiêu đề cột Product, sau đó giữ phím Shift và nhấp vào tiêu đề cột Sold By. Sau khi cả 2 cột được chọn, nhấn tab Home –> Remove Columns. (thao tác này xóa các cột đã chọn).

Bây giờ chúng ta chuyển đổi dữ liệu trên cột Date. Chọn tiêu đề cột Date sau đó nhấn Transform –> Pivot Column trên thanh làm việc.

Cửa sổ Pivot Column sẽ mở ra. Thay đổi cột Values Column thành Value. Nhấp vào tùy chọn nâng cao và đặt Aggregate Value Function thành Sum, Sau đó nhấp vào OK.

Bây giờ nhấp vào tab Home–> Close&Load. Excel sẽ tạo một trang tính mới với một bảng và bảng này chứa dữ liệu từ CSV đã được chuyển đổi. Giống như ảnh chụp bên dưới:

Cập nhật dữ liệu nguồn

Để thực hiện quá trình làm việc mới, chúng ta mô phỏng nơi người dùng có thể nhận được tệp mới hàng ngày, hàng tuần hoặc hàng tháng. Để làm được điều này chúng ta đổi tên cả hai sổ làm việc mẫu.

• Example -Data Refresh 1.csv có thể đổi tên thành bất kỳ thứ gì.
• Example 6 – Data Refresh 2.csv được đổi tên thành Example 6 – Data Refresh 1.csv .

Power Query vẫn đang trỏ đến một tệp có tên là Example 6 – Data Refresh 1.csv  có chứa dữ liệu mới.

Làm mới dữ liệu

Nhấp vào Data –>Refresh all.

Excel sẽ nhập dữ liệu từ tệp Power Query áp dụng các phép biến đổi tương tự và tải dữ liệu vào trang tính. Dữ liệu mới sẽ tự động xuất hiện trên trang tính.

Có thể nhìn thấy dữ liệu tháng 3 xuất hiện chỉ với một cú nhấp chuột.

Khi xuất hiện sự thay đổi với các các dữ liệu hiện tại hoặc nơi nhận tệp mới bạn chỉ cần lưu tệp với cùng đường dẫn tệp chỉ bằng một cú nhấp chuột,

Trong phần sau chúng ta sẽ xem xét việc liên kết đường dẫn tệp với một ô. Bạn có thể nhập mà không cần ghi đè lên các tệp trước đó.

Làm mới các truy vấn cụ thể

Chúng ta có thể mất một khoảng thời gian để làm mới tất cả khi có nhiều truy vấn trong cửa sổ làm việc. Trong những trường hợp đó làm cách nào chỉ thực hiện những truy vấn mà chúng ta cần. Có một số cách thực hiện như sau:

Nút Refresh

Bạn nhập vào một truy vấn trong bảng, trên thanh làm việc nhấp Data–> menu thả xuống với một số Refresh với tùy chọn.

Nhấp vào Refresh để làm mới truy vấn bạn đã chọn.

Menu Query and Connections (truy vấn và kết nối)

Khi tải dữ liệu từ Power Query vào Excel, menu Queries and Connections sẽ mở ra. Cửa sổ này hiển thị tất cả các truy vấn được tạo trong trang làm việc.

Nếu menu đó không mở, hãy nhấp vào Data -> Queries and Connections.

Mỗi truy vấn trong menu Queries and Connections có một biểu tượng làm mới. Chỉ cần nhấp vào biểu tượng để làm mới dữ liệu.

Ngoài ra, chúng ta có thể nhấp chuột phải vào truy vấn và chọn Refresh từ menu.

Làm mới VBA

Mục này sẽ không trình bày trong loạt bài này, nhưng bạn có thể sử dụng VBA để làm mới các truy vấn riêng lẻ. Đây có thể là một tùy chọn hữu ích khi:

• Bạn muốn cung cấp một giao diện dễ sử dụng cho người dùng
• Bạn muốn kiểm soát thứ tự làm mới các truy vấn.

Tùy chọn làm mới nâng cao

Việc làm mới các truy vấn riêng lẻ có thể trở nên tốn thời gian hoặc quá chậm khi nhiều truy vấn được thêm vào các trang làm việc. Tuy nhiên, Excel đã thêm một số các tùy chọn để cho phép dữ liệu được làm mới tự động.

Chọn một ô trong một Bảng truy vấn, sau đó nhấp vào Data ->  Refresh All (Drop Down) -> Connection Properties.

Các Query Properties sẽ được mở ra:

Các tùy chọn làm mới có sẵn trong cửa sổ này là:

Background refresh: cho phép bạn  tiếp tục làm việc trong khi dữ liệu làm mới diễn ra ở background. Nó được hiển thị theo mặc định. Nếu chúng ta xóa tùy chọn này thì sẽ không thể làm việc cho đến khi quá trình làm mới hoàn tất. Với tính năng này được áp dụng, quá trình làm mới sẽ mất nhiều thời gian hơn, tuy nhiên bạn vẫn có thể tiếp tục làm việc.

Refresh every x minutes: Các trang làm việc phải được mở để tùy chọn Refresh every x minutes  hoạt động. Tính năng này hữu ích khi dữ liệu nguồn thường xuyên thay đổi. Bạn chỉ nên sử dụng tùy chọn này khi bật tính năng Background refresh, nếu không nó có thể phát sinh những vấn đề cho người dùng.

Refresh when opening the file: Tự động làm mới dữ liệu khi mở tệp là một tính năng hữu ích vì dữ liệu được cập nhật bất cứ khi nào bạn mở tệp.

Refresh this connection on Refresh All: nơi truy vấn chứa dữ liệu cố định, bạn không cần làm mới nó nhiều lần. Bạn chỉ cần xóa dấu tích bên cạnh hộp Refresh this connection on Refresh All thì nó sẽ xóa các truy vấn khỏi quy trình.

Những lưu ý khi bạn làm mới dữ liệu

Dưới đây sẽ là một số khó khăn mà bạn có thể gặp phải

Đóng và mở tệp

Vị trí lưu trữ dữ liệu nguồn rất quan trọng đối với việc làm mới dữ liệu.

Khi dữ liệu được lưu trữ trong tệp bên ngoài như CSV, văn bản hoặc là các workbook của Excel thì nó là phiên bản được lưu cuối cùng của tệp được tải vào Power Query. Nếu tệp bên ngoài đang mở, các thay đổi sẽ không được bao gồm trong quá trình làm mới cho đến khi chúng được lưu. 

Khi dữ liệu và truy vấn nằm trong cùng một sổ làm việc, tất cả các thay đổi được kết hợp ngay cả khi tệp chưa được lưu.

Truy vấn dựa trên các truy vấn khác

Trong các bài đăng tiếp theo sẽ hướng dẫn các bạn xem xét sử dụng truy vấn dựa trên một truy vấn khác. Nhưng chúng ta phải cần lưu ý về kết quả nếu chúng ta chỉ làm mới một truy vấn duy nhất vì mọi truy vấn tiếp theo trong chuỗi sẽ không được làm mới.

Background refresh 

Đây là một tính năng hữu ích vì chúng ta có thể tiếp tục làm việc trong Excel. Tuy nhiên, bất kỳ PivotTables hoặc công thức nào được cập nhật hoặc tính toán trước khi quá trình background refresh hoàn tất thì chúng ta không nhất thiết phải có dữ liệu chính xác. Do đó, trong một số trường hợp, bạn có thể không cần đến background refresh.