Power Query cung cấp một số cách để tạo và tải truy vấn Nguồn vào sổ làm việc của bạn. Bạn cũng có thể đặt thiết đặt tải truy vấn mặc định trong cửa sổ Tùy chọn Truy vấn.
Mẹo Để biết liệu dữ liệu trong trang tính có được định hình bởi Power Query không, hãy chọn một ô dữ liệu và nếu tab ribbon ngữ cảnh Truy vấn xuất hiện thì dữ liệu đã được tải từ Power Query.
Giới thiệu về sự tích hợp Power Query vào Excel
Biết môi trường bạn đang ở trong 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à chỉnh sửa Bảng Pivot, bảng Excel và dải ô đã đặ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 | Dải 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 thao tác Power Query. Hơn nữa, dữ liệu được kết nối mà bạn nhìn thấy trong trang tính Excel, có thể có hoặc không có tác dụng Power Query việc hậu trường để đị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. Điều đặc biệt quan trọng là phải làm rõ sự khác biệt giữa trang tính dữ liệu và trang tính được tải từ trang tính Trình soạn thảo Power Query. 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.
Tạo 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.
- 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.
- Chọn một ô trong dữ liệu, rồi chọn Sửa Truy>vấn.
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ữ liệu>Lấy dữ liệu>từ các nguồn khác truy>vấn trống.
- Chọn Khởi chạy>Dữ liệu>để 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 Power Query công thức 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 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 Gần>đây Dữ liệu 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 để thử các tùy chọn Trình soạn thảo Power Query độc lập với nguồn dữ liệu ngoài.
Tải truy vấn
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ừ danh sách Trình soạn thảo Power Query
Trong hộp 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 Đóng>Trang & Tải>Đóng & tải.
Để tải vào Mô hình Dữ liệu, hãy chọn Đóng>Trang & Tải>Đóng & Tải Vào.
Trong hộpthoạ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, bấm chuột phải vào truy vấn, rồi chọn Tải Đến. Ngoài ra, trên dải Trình soạn thảo Power Query hãy chọn Tải Truy vấn>vào.
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.
- Trong Excel, chọn Truy vấn>Dữ & Kết nối, rồi chọn tab Truy vấn.
- 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.
- 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 (?).
Sửa truy vấn từ trang tính
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 Sửa Truy>vấn.
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.
- Trong Excel, chọn Truy vấn>Dữ & Kết nối, rồi chọn tab Truy vấn.
- 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 tabDữ> liệu&> truy vấn Kết nối, 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.
Sửa truy vấn của bảng trong Mô hình Dữ liệu
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.
Để mở Mô hình Dữ liệu, hãy chọn Quản lý Power Pivot>.
Ở 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.
Lưu ý tên bảng.
Để đóng cửa sổ Power Pivot, hãy chọn Đóng>Tệp. Có thể mất vài giây để lấy lại bộ nhớ.
Chọn Kết nối>Dữ & truy vấn Thuộc> tính, bấm chuột phải vào truy vấn, rồi chọn Chỉnh sửa.
Khi hoàn tất thực hiện thay đổi trong Trình soạn thảo Power Query, hãy chọn Đóng>Tệp & 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.
Việc tải truy vấn vào Mô hình Dữ liệu mất nhiều thời gian bất thường
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 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ằnghoặc Bắt đầu Với.
Microsoft đã biết về sự cố này và hiện đang điều tra.
Đặt tùy chọn tải truy vấn
Bạn có thể tải tệp Power Query:
Đến một trang tính. Trong hộp Trình soạn thảo Power Query, chọn Đóng>Màn hình & Load>Close & Load.
Đến Mô hình Dữ liệu. Trong hộp Trình soạn thảo Power Query, chọn Đóng>Màn hình & Load>Close & LoadTo.
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 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ải Vào.
Thiết đặt chung áp dụng cho tất cả các sổ làm việc của bạn
Trong hộp kiểm Trình soạn thảo Power Query chọn Tùy chọn Tệp và>thiết đặt Tùy chọn>Truy vấn.
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.
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 Xác đị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ínhhoặ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
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.
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 thấy 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 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.