Power Query cung cấp một số cách để tạo và tải Power queries 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 hay 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ề việc tích hợp Power Query vào Excel
Biết môi trường bạn đang ở đâu Power Query được tích hợp tốt vào giao diện người dùng Excel, nhấ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à các phạm vi đã đặt tên. Để tránh nhầm lẫn, điều quan trọng là phải biết môi trường hiện tại của bạn, Excel hay Power Query, vào bất kỳ thời điểm nào.
| Trang tính, dải băng 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 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 thấy trong trang tính Excel có thể có hoặc không thể có Power Query làm việc bên dưới để đị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 các tab trang tính một cách có ý nghĩa, đặc biệt nếu bạn có rất nhiều tab như vậy. Đ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 tải từ Trình soạn thảo Power Query. Ngay cả khi bạn chỉ có hai trang tính, một trang tính chứa bảng Excel, được gọi là Sheet1 và trang còn lại chứa truy vấn được tạo bằng cách nhập bảng Excel đó, được gọi là Table1, bạn vẫn dễ dàng bị nhầm lẫn. 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 đối với mình. Ví dụ, đổi tên Sheet1 thành DataTable và Table1 thành QueryTable. Giờ thì đã 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ừ nguồn dữ liệu bên ngoài.
- Chọn một ô trong dữ liệu, rồi chọnSửa Truyvấn>.
Tạo truy vấn trống
Có thể bạn 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áctruy vấn trống.
- Chọn Dữ liệu>, Lấy Dữ liệu>Khởi chạy Trình soạn thảo Power Query.
Lúc 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 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ệnhLấy Dữ liệu> trong ribbon Excel.
- Chọn Nguồn Gần đây để chọn từ nguồn dữ liệu bạn đã và đang làm việc cùng. Lệnh này giống như lệnh Nguồn Dữ liệu>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 để thử sử dụng Trình soạn thảo Power Query một cách độ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 truy vấn trở lại vào 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 những thao tác sau:
Để tải vào trang tính, hãy chọn Đóng Trang đầu>&ĐóngTải &> Tải.
Để tải vào Mô hình Dữ liệu, hãy chọn Đóng Trang đầu>& Tải Đóng>& Tải Tới.
Trong hộp thoạiNhập Dữ liệu , hãy 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 Đến bị mờ đi hoặc tắt. Đ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, hãy chọn Truy vấn Dữ liệu> & TruyvấnKết nối>, bấm chuột phải vào truy vấn, rồi chọn Tải Vào. Ngoài ra, trên ribbon Trình soạn thảo Power Query, hãy chọn Truy vấn>Tải 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 một truy vấn vào trang tính khác hoặc Mô hình Dữ liệu.
- Trong Excel, hãy chọn Truy vấn Dữ liệu>& Kết nối, rồi chọn tab Truy vấn .
- Trong danh sách truy vấn, hãy định vị truy vấn, bấm chuột phải vào truy vấn, rồi chọn Tải Vào. 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 hỏi (?).
Sửa truy vấn từ trang tính
Có vài cách để sửa một truy vấn được tải vào trang tính.
Sửa truy vấn từ dữ liệu trong trang tính Excel
- Để sửa một truy vấn, hãy tìm một 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ọnSửa Truyvấn>.
Sửa truy vấn từ ngăn Truy vấn & Kết nối
Bạn có thể thấy ngăn Truy vấn & 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 nhanh chóng tìm một truy vấn.
- Trong Excel, hãy chọn Truy vấn Dữ liệu>& Kết nối, rồi chọn tab Truy vấn .
- Trong danh sách truy vấn, hãy định vị truy vấn, bấm chuột phải vào truy vấn, rồi chọn Chỉnh sửa.
Sửa truy vấn từ hộp thoại Thuộc tính Truy vấn
- Trong Excel, chọnDữ liệuDữ liệu> & tabTruy 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 Sửa Truy vấn.
Mẹo Nếu bạn đang ở trong một trang tính có truy vấn, hãy chọnThuộc tínhDữ liệu>, chọn tab Đị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 bao gồm 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 Đến để 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. Để biết thêm thông tin về Mô hình Dữ liệu, hãy xem Tìm hiểu nguồn dữ liệu nào được 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ọnQuản lýPower Pivot>.
Ở cuối cửa sổ Power Pivot, hãy chọn tab trang tính của bảng bạn muốn.
Xác nhận rằng bảng chính xác hiển thị. Một Mô hình Dữ liệu có thể có nhiều bảng.
Ghi lại tên bảng.
Để đóng cửa sổ Power Pivot, hãy chọnĐóng Tệp>. Có thể mất một vài giây để lấy lại bộ nhớ.
Chọn Kết nối Dữ liệu>& Thuộc tính>Tab Truy vấn , bấm chuột phải vào truy vấn, rồi chọn Chỉnh sửa.
Khi hoàn tất các 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 một truy vấn vào Mô hình Dữ liệu mất nhiều thời gian lạ 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 trang tính, hãy kiểm tra các bước của Power Query để xem bạn có đang lọc cột văn bản hoặc cột có cấu trúc Danh sách bằng cách sử dụng toán tử Chứa hay không. Hành động này khiến Excel liệt kê lại toàn bộ bộ dữ liệu cho từng hàng. Hơn nữa, Excel không thể sử dụng thực thi đa phân luồng một cách hiệu quả. Hãy thử sử dụng toán tử khác, như Bằng hoặc Bắt đầu Với, như một giải pháp thay thế.
Microsoft đã biết về sự cố này và đang được điều tra.
Đặt tùy chọn tải truy vấn
Bạn có thể tải một Power Query:
Vào trang tính. Trong Trình soạn thảo Power Query, chọn Đóng Trang đầu>& Đóng & Tải Đóng>Tải.
vào một mô hình dữ liệu. Trong Trình soạn thảo Power Query, hãy chọn Đóng Trang đầu>&Đóng Tải & >Tải Đến.
Theo mặc định, Power Query tải các truy vấn đến một trang tính mới khi tải một truy vấn đơn lẻ 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 mình hoặc chỉ sổ làm việc hiện tại. Khi thiết lập 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 và chú thích của Mô hình Dữ liệu.
Bạn cũng có thể ghi đè tự động 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 & LoadTo.
Các thiết đặt chung áp dụng cho tất cả các sổ làm việc của bạn
Trong 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 , ở phía bên trái, dưới phần 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 thiết đặt tải tiêu chuẩn.
- Chọn Xác định thiết đặt tải mặc định tùy chỉnh, rồi chọn hoặc xóa Tải đến trang tính hoặc Tải đến 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 để trở về cài đặt mặc định một cách thuận tiện.
Các 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, dưới phần 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, hãy chọn hoặc bỏ chọn Phát hiện các loại cột và tiêu đề cho các nguồn không có cấu trúc.
Hành vi mặc định là phát hiện chúng. Bỏ chọn 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 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à nhập chúng cùng với dữ liệu. Bỏ chọn tùy chọn này nếu bạn muốn tự thực hiện thao tá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 các 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 làm mới các truy vấn đã được tải vào Mô hình Dữ liệu, Power Query tìm thấy các mối quan hệ hiện có giữa các bảng như khóa ngoạị 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 tạo ra 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 tải xuống bản xem trước dữ liệu trong nền.
Hành vi mặc định là tải xuống các bản xem trước dữ liệu trong nền. Bỏ chọn 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.