Tạo, tải hoặc sửa truy vấn trong Excel (Power Query)

Biết bạn đang ở trong môi trường nào Power Query được tích hợp tốt vào giao diện người dùng Excel, đặc biệt là khi bạn nhập dữ liệu, làm việc với các kết nối và sửa Bảng Pivot, bảng Excel và phạm vi đã đặt tên. Để tránh nhầm lẫn, điều quan trọng là bạn cần biết mình đang ở trong môi trường nào, Excel hoặc Power Query tại bất kỳ thời điểm nào.

Trang tính , ribbon và lưới Excel quen thuộc	Ribbon Trình soạn thảo Power Query và bản xem trước dữ liệu

Ví dụ: thao tác với dữ liệu trong trang tính Excel về cơ bản khác với Power Query. Hơn nữa, dữ liệu được kết nối mà bạn nhìn thấy trong một trang tính Excel, có thể hoặc có thể không có Power Query làm việc ở phía sau để định hình dữ liệu. Điều này chỉ xảy ra khi bạn tải dữ liệu vào trang tính hoặc Mô hình Dữ liệu từ Power Query.

Đổi tên tab trang tính    Bạn nên đổi tên tab trang tính theo cách có ý nghĩa, đặc biệt là nếu bạn có nhiều tab. Việc làm rõ sự khác biệt giữa trang tính dữ liệu và trang tính được tải từ Trình soạn thảo Power Query đặc biệt quan trọng. Ngay cả khi bạn chỉ có hai trang tính, một trang tính với một bảng Excel, được gọi là Sheet1 và một truy vấn khác được tạo bằng cách nhập bảng Excel đó, được gọi là Table1, bạn cũng có thể dễ dàng bối rối. Bạn nên thay đổi tên mặc định của tab trang tính thành tên có ý nghĩa hơn với bạn. Ví dụ: đổi tên Sheet1 thành DataTable và Table1 thành QueryTable. Bây giờ, đã rõ tab nào có dữ liệu và tab nào có truy vấn.

Bạn có thể tạo truy vấn từ dữ liệu đã nhập hoặc tạo truy vấn trống.

Tạo truy vấn từ dữ liệu đã nhập

Đây là cách phổ biến nhất để tạo truy vấn.

1. Nhập một số dữ liệu. Để biết thêm thông tin, hãy xem Nhập dữ liệu từ các nguồn dữ liệu bên ngoài.

2. Chọn một ô trong dữ liệu, rồi chọn Truy vấn> sửa.

Tạo truy vấn trống

Bạn có thể chỉ muốn bắt đầu từ đầu. Có hai cách thực hiện như sau.

• Chọn Dữ> Lấy dữ liệu > liệu từ các nguồn khác > truy vấn trống.

• Chọn Dữ liệu > Lấy Dữ liệu >chạy Trình soạn thảo Power Query.

Tại thời điểm này, bạn có thể thêm các bước và công thức theo cách thủ công nếu bạn biết rõ ngôn ngữ công thức Power Query M.

Hoặc bạn có thể chọn Trang đầu, rồi chọn một lệnh trong nhóm Truy vấn Mới. Thực hiện một trong những thao tác sau đây.

• Chọn Nguồn Mới để thêm nguồn dữ liệu. Lệnh này giống như lệnh Dữ liệu > Lấy Dữ liệu trong dải băng Excel.

• Chọn Nguồn Gần đây để chọn từ nguồn dữ liệu mà bạn đã làm việc cùng. Lệnh này giống như lệnh Nguồn Dữ > Gần đây trong ribbon Excel.

• Chọn Nhập Dữ liệu để nhập dữ liệu theo cách thủ công. Bạn có thể chọn lệnh này để dùng thử Trình soạn thảo Power Query độc lập với nguồn dữ liệu ngoài.

Giả sử truy vấn của bạn hợp lệ và không có lỗi, bạn có thể tải nó trở lại một trang tính hoặc Mô hình Dữ liệu.

Tải truy vấn từ Trình soạn thảo Power Query

Trong Trình soạn thảo Power Query, hãy thực hiện một trong các thao tác sau:

• Để tải vào một trang tính, hãy chọn Trang đầu> Đóng & tải >đóng hoặc & tải.

• Để tải vào Mô hình Dữ liệu, hãy chọn Trang> Đóng & tải > Đóng & Tải Tới.
  
  Trong hộp thoại Nhập Dữ liệu, chọn Thêm dữ liệu này vào Mô hình Dữ liệu.

Mẹo   Đôi khi , lệnh Tải Tới bị mờ đi hoặc bị vô hiệu hóa. Điều này có thể xảy ra khi bạn tạo truy vấn lần đầu trong sổ làm việc. Nếu điều này xảy ra, hãy chọn Đóng & Tải, trong trang tính mới, chọn Truy vấn dữ liệu > Truy vấn & Kết nối > Tab Truy vấn, bấm chuột phải vào truy vấn, rồi chọn Tải Đến. Ngoài ra, trên ribbon Trình soạn thảo Power Query, chọn Truy > Tải Đến.

Tải truy vấn từ ngăn Truy vấn và Kết nối 

Trong Excel, bạn có thể muốn tải truy vấn vào trang tính khác hoặc Mô hình Dữ liệu.

1. Trong Excel, chọn Truy> Truy & Kết nối, rồi chọn tab Truy vấn.

2. Trong danh sách truy vấn, định vị truy vấn, bấm chuột phải vào truy vấn, rồi chọn Tải Đến. Hộp thoại Nhập Dữ liệu xuất hiện.

3. Quyết định cách bạn muốn nhập dữ liệu, rồi chọn OK. Để biết thêm thông tin về cách sử dụng hộp thoại này, hãy chọn dấu chấm hỏi (?).

Có một vài cách để sửa một truy vấn được tải vào một trang tính.

Sửa truy vấn từ dữ liệu trong trang tính Excel

• Để sửa truy vấn, hãy định vị truy vấn đã tải trước đó từ Trình soạn thảo Power Query, chọn một ô trong dữ liệu, rồi chọn Truy vấn> Sửa.

Sửa truy vấn từ ngăn Truy & kết nối

Bạn có thể thấy ngăn Truy & Kết nối thuận tiện hơn khi bạn có nhiều truy vấn trong một sổ làm việc và bạn muốn tìm nhanh một truy vấn.

1. Trong Excel, chọn Truy> Truy & Kết nối, rồi chọn tab Truy vấn.

2. Trong danh sách truy vấn, định vị truy vấn, bấm chuột phải vào truy vấn, rồi chọn Sửa.

Sửa truy vấn từ hộp thoại Thuộc tính Truy vấn

• Trong Excel, chọn Dữ liệu> liệu & Kết nối > Truy vấn, bấm chuột phải vào truy vấn và chọn Thuộc tính, chọn tab Định nghĩa trong hộp thoại Thuộc tính , rồi chọn Chỉnh sửa truy vấn.

Mẹo    Nếu bạn đang ở trong trang tính có truy vấn, hãy chọn Thuộc tính> dữ liệu, chọntab Định nghĩa trong hộp thoại Thuộc tính, rồi chọn Sửa Truy vấn. 

Mô hình Dữ liệu thường chứa một vài bảng được sắp xếp trong một mối quan hệ. Bạn tải truy vấn vào Mô hình Dữ liệu bằng cách sử dụng lệnh Tải Tới để hiển thị hộp thoại Nhập Dữ liệu, rồi chọn hộp kiểm Thêm dữ liệu này vào Chế độ Dữ liệu l. Để biết thêm thông tin về Mô hình Dữ liệu, hãy xem Tìm hiểu xem nguồn dữ liệu nào được sử dụng trong mô hình dữ liệu sổ làm việc, Tạo Mô hình Dữ liệu trong Excel và Sử dụng nhiều bảng để tạo PivotTable.

1. Để mở Mô hình Dữ liệu, hãy chọn Power Pivot >Quản lý.

2. Ở cuối cửa sổ Power Pivot, chọn tab trang tính của bảng bạn muốn.
   
   Xác nhận rằng bảng hiển thị chính xác. Mô hình Dữ liệu có thể có nhiều bảng.

3. Lưu ý tên bảng.

4. Để đóng cửa sổ Power Pivot, hãy chọn Tệp > Đóng. Có thể mất vài giây để lấy lại bộ nhớ.

5. Chọn Dữ > Kết nối & tính >Truy vấn, bấm chuột phải vào truy vấn, rồi chọn Chỉnh sửa.

6. Khi thực hiện xong các thay đổi trong Trình soạn thảo Power Query, hãy chọn > Đóng & Tải.

Kết quả

Truy vấn trong trang tính và bảng trong Mô hình Dữ liệu được cập nhật.

Nếu bạn nhận thấy rằng việc tải truy vấn vào Mô hình Dữ liệu mất nhiều thời gian hơn tải vào một trang tính, hãy kiểm tra các bước trong Power Query của bạn để xem liệu bạn đang lọc một cột văn bản hay cột Có cấu trúc danh sách bằng cách sử dụng toán tử Chứa. Hành động này khiến Excel liệt kê lại toàn bộ tập dữ liệu cho từng hàng. Ngoài ra, Excel không thể sử dụng thực thi nhiều lần một cách hiệu quả. Như một giải pháp thay thế, hãy thử sử dụng một toán tử khác, chẳng hạn như Bằng hoặc Bắt đầu Với.

Microsoft đã biết về sự cố này và hiện đang điều tra.

Bạn có thể tải Power Query:

• Đến một trang tính. Trong Trình soạn thảo Power Query, chọn Trang đầu> Đóng & tải >đóng & tải.

• Đến Mô hình Dữ liệu. Trong Trình soạn thảo Power Query, chọn Trang đầu> Đóng & tải >đóng & tải tới.
  
  Theo mặc định, Power Query sẽ tải các truy vấn vào một trang tính mới khi tải một truy vấn duy nhất và tải nhiều truy vấn cùng một lúc vào Mô hình Dữ liệu.  Bạn có thể thay đổi hành vi mặc định cho tất cả các sổ làm việc của bạn hoặc chỉ sổ làm việc hiện tại. Khi thiết đặt các tùy chọn này, Power Query sẽ không thay đổi kết quả truy vấn trong trang tính hoặc dữ liệu Mô hình Dữ liệu và chú thích.
  
  Bạn cũng có thể tự động ghi đè các thiết đặt mặc định cho truy vấn bằng cách sử dụng hộp thoại Nhập hiển thị sau khi bạn chọn Đóng & TảiTới. 

Thiết đặt chung áp dụng cho tất cả các sổ làm việc của bạn

1. Trong Trình soạn thảo Power Query, hãy chọn Tệp> chọn và thiết đặt >chọn Truy vấn.

2. Trong hộp thoại Tùy chọn Truy vấn, ở bên trái, bên dưới mục GLOBAL , chọn Tải Dữ liệu.

3. Trong phần Thiết đặt Tải Truy vấn Mặc định , hãy làm như sau:

   • Chọn Sử dụng cài đặt tải tiêu chuẩn. 

   • Chọn Chỉ định cài đặt tải mặc định tùy chỉnh,rồi  chọn hoặc bỏ chọn Tải vào trang tính hoặc Tải vào Mô hình Dữ liệu.

Mẹo    Ở cuối hộp thoại, bạn có thể chọn Khôi phục Mặc định để quay lại thiết đặt mặc định một cách thuận tiện.

Thiết đặt sổ làm việc chỉ áp dụng cho sổ làm việc hiện tại

1. Trong hộp thoại Tùy chọn Truy vấn, ở bên trái, bên dưới mục SỔ LÀM VIỆC HIỆN TẠI, chọn Tải Dữ liệu.

2. Hãy thực hiện một hoặc nhiều thao tác sau:

   • Bên dưới Phát hiện Loại, chọn hoặc bỏ chọn Phát hiện loại cột và tiêu đề cho các nguồn phi cấu trúc.
     
     Hành vi mặc định là để phát hiện chúng. Xóa tùy chọn này nếu bạn muốn tự định hình dữ liệu.

   • Bên dưới Mối quan hệ, chọn hoặc bỏ chọn Tạo mối quan hệ giữa các bảng khi thêm vào Mô hình Dữ liệu lần đầu tiên.

     Trước khi tải vào Mô hình Dữ liệu, hành vi mặc định là tìm mối quan hệ hiện có giữa các bảng, chẳng hạn như khóa ngoại trong cơ sở dữ liệu có quan hệ và nhập chúng cùng với dữ liệu. Xóa tùy chọn này nếu bạn muốn tự thực hiện việc này.

   • Bên dưới Mối quan hệ, chọn hoặc xóa Cập nhật mối quan hệ khi làm mới truy vấn được tải vào Mô hình Dữ liệu.
     
     Hành vi mặc định là không cập nhật mối quan hệ. Khi các truy vấn làm mới đã được tải lên Mô hình Dữ liệu, Power Query sẽ tìm các mối quan hệ hiện có giữa các bảng chẳng hạn như khóa ngoại trong một cơ sở dữ liệu quan hệ và cập nhật chúng. Điều này có thể loại bỏ các mối quan hệ được tạo theo cách thủ công sau khi dữ liệu được nhập hoặc giới thiệu các mối quan hệ mới. Tuy nhiên, nếu bạn muốn thực hiện điều này, hãy chọn tùy chọn đó.

   • Trong Dữ liệu Nền, chọn hoặc bỏ chọn Cho phép xem trước dữ liệu tải xuống trong nền.
     
     Hành vi mặc định là tải xuống bản xem trước dữ liệu trong nền. Xóa tùy chọn này nếu bạn có thể muốn xem tất cả dữ liệu ngay lập tức.