Có thể bạn đã khá quen thuộc với truy vấn tham số khi sử dụng truy vấn trong SQL hoặc Microsoft Query. Tuy nhiên Power Query số này có những điểm khác biệt chính:
- Tham số có thể được sử dụng trong bất kỳ bước truy vấn nào. Ngoài chức năng như một bộ lọc dữ liệu, tham số có thể được sử dụng để xác định những thứ như đường dẫn tệp hoặc tên máy chủ.
- Tham số không nhắc nhập. Thay vào đó, bạn có thể nhanh chóng thay đổi giá trị của chúng Power Query. Bạn thậm chí có thể lưu trữ và truy xuất giá trị từ các ô trong Excel.
- Tham số được lưu trong một truy vấn tham số đơn giản, nhưng tách biệt với các truy vấn dữ liệu mà chúng được sử dụng trong đó. Sau khi đã tạo, bạn có thể thêm tham số vào truy vấn nếu cần.
Lưu ý Nếu bạn muốn có cách khác để tạo truy vấn tham số, hãy xem Tạo truy vấn tham số trong Microsoft Query.
Tạo tham số
Bạn có thể sử dụng tham số để tự động thay đổi giá trị trong truy vấn và tránh sửa truy vấn mỗi lần thay đổi giá trị. Bạn chỉ cần thay đổi giá trị tham số. Sau khi bạn tạo tham số, tham số sẽ được lưu trong truy vấn tham số đặc biệt mà bạn có thể thay đổi trực tiếp từ Excel một cách thuận tiện.
Chọn Dữ liệu>Khởi chạy>Nguồn Dữ liệu>Khác Trình soạn thảo Power Query.
Trong hộp Trình soạn thảo Power Query, chọn Trang chủ Quản lý>Tham số > Mới.
Trong hộp thoại Quản lý Tham số, chọn Mới.
Đặt những điều sau đây khi cần thiết:
Tên Điều này sẽ phản ánh hàm của tham số, nhưng giữ cho tham số càng ngắn càng tốt. Mô tả Thông tin này có thể chứa mọi chi tiết sẽ giúp mọi người sử dụng đúng tham số. Yêu cầu Thực hiện một trong những thao tác sau:
Giá trị Bất kỳ Bạn có thể nhập bất kỳ giá trị nào của bất kỳ kiểu dữ liệu nào vào truy vấn tham số.
Danh sách Giá trị Bạn có thể giới hạn các giá trị trong một danh sách cụ thể bằng cách nhập chúng vào lưới nhỏ. Bạn cũng phải chọn Giá trị Mặc định và Giá trị Hiện tại bên dưới.
Truy vấn Chọn một truy vấn danh sách, tương tự như cột Có cấu trúc danh sách được phân tách bởi dấu phẩy và được đặt trong dấu ngoặc nhọn.
Ví dụ: trường Trạng thái sự cố có thể có ba giá trị: {"Mới", "Đang diễn ra", "Đã đóng"}. Bạn phải tạo trước truy vấn danh sách bằng cách mở Trình chỉnh sửa nâng cao (>chọn Trang đầu Trình chỉnh sửa nâng cao), loại bỏ mẫu mã, nhập danh sách giá trị trong định dạng danh sách truy vấn, rồi chọn Xong.
Sau khi bạn tạo tham số xong, truy vấn danh sách sẽ được hiển thị trong các giá trị tham số của bạn.Loại Thao tác này chỉ định kiểu dữ liệu của tham số. Giá trị Đề xuất Nếu muốn, hãy thêm danh sách giá trị hoặc chỉ định truy vấn để cung cấp đề xuất cho dữ liệu đầu vào. Giá trị Mặc định Điều này chỉ xuất hiện nếu Giá trị Đề xuất được đặt là Danh sách giá trị và xác định mục danh sách nào là mặc định. Trong trường hợp này, bạn phải chọn mặc định. Giá trị Hiện tại Tùy thuộc vào vị trí bạn sử dụng tham số, nếu đây là giá trị trống thì truy vấn có thể không trả về kết quả nào. Nếu Bắt buộc được chọn, giá trị hiện tại không thể để trống. Để tạo tham số, chọn OK.
Sử dụng tham số để thay đổi nguồn dữ liệu
Đây là cách để quản lý các thay đổi đối với vị trí nguồn dữ liệu và giúp ngăn ngừa lỗi làm mới. Ví dụ: giả sử một sơ đồ và nguồn dữ liệu tương tự, hãy tạo một tham số để dễ dàng thay đổi nguồn dữ liệu và giúp ngăn ngừa lỗi làm mới dữ liệu. Đôi khi máy chủ, cơ sở dữ liệu, thư mục, tên tệp hoặc thay đổi vị trí. Có lẽ một người quản lý cơ sở dữ liệu đôi khi trao đổi ra một máy chủ, một thả hàng tháng của các tập tin CSV đi vào một thư mục khác nhau, hoặc bạn cần phải dễ dàng chuyển đổi giữa một môi trường phát triển / thử nghiệm / sản xuất.
Bước 1: Tạo truy vấn tham số
Trong ví dụ sau đây, bạn có một vài tệp CSV mà bạn nhập bằng cách sử dụng thao tác nhập thư mục (>>Chọn Dữ liệu Lấy Dữ liệutừ Files>Từ Thư mục) từ thư mục C:\DataFilesCSV1. Nhưng đôi khi một thư mục khác đôi khi được sử dụng làm vị trí để thả tệp, C:\DataFilesCSV2. Bạn có thể sử dụng tham số trong truy vấn làm giá trị thay thế cho thư mục khác.
Chọn Trang chủ Quản>lý tham số>mới.
Nhập thông tin sau đây vào hộp thoại Quản lý Tham số:
Tên CSVFileDrop Mô tả Vị trí thả tệp thay thế Yêu cầu Có Loại Văn bản Giá trị Đề xuất Bất kỳ giá trị nào Giá trị Hiện tại C:\DataFilesCSV1 Chọn OK.
Bước 2: Thêm tham số vào truy vấn dữ liệu
- Để đặt tên thư mục làm tham số, trong Thiết đặt Truy vấn, dưới Bước Truy vấn, chọn Nguồn, rồi chọn Sửa Thiết đặt.
- Đảm bảo tùy chọn Đường dẫn tệp được đặt thành Tham số, rồi chọn tham số bạn vừa tạo từ danh sách thả xuống.
- Chọn OK.
Bước 3: Cập nhật giá trị tham số
Vị trí thư mục vừa thay đổi, vì vậy bây giờ bạn chỉ cần cập nhật truy vấn tham số.
- Chọn Kết nối>Dữ & Truy vấn> Truy vấn, bấm chuột phải vào truy vấn tham số, rồi chọn Chỉnh sửa.
- Nhập vị trí mới vào hộp Giá trị Hiện tại, chẳng hạn như C:\DataFilesCSV2.
- Chọn Đóng>Màn hình & Tải.
- Để xác nhận kết quả của bạn, hãy thêm dữ liệu mới vào nguồn dữ liệu, rồi làm mới truy vấn dữ liệu bằng tham số cập nhật (Chọn Làm> mớiDữ liệu Tất cả).
Sử dụng tham số để lọc dữ liệu
Đôi khi bạn muốn một cách dễ dàng để thay đổi bộ lọc của một truy vấn để có được các kết quả khác nhau mà không cần sửa truy vấn hoặc tạo các bản sao hơi khác nhau của cùng một truy vấn. Trong ví dụ này, chúng tôi thay đổi ngày để thuận tiện thay đổi bộ lọc dữ liệu.
Để mở truy vấn, hãy định vị truy vấn đã được tải từ Trình soạn thảo Power Query trước đó, chọn một ô trong dữ liệu, rồi chọn Sửa Truy>vấn. Để biết thêm thông tin , hãy xem mục Tạo, tải hoặc chỉnh sửa truy vấn trong Excel.
Chọn mũi tên bộ lọc trong bất kỳ tiêu đề cột nào để lọc dữ liệu của bạn, rồi chọn lệnh lọc, chẳng hạn như Bộ lọc Ngày/Giờ Sau>. Hộp thoại Lọc Hàng xuất hiện.
Chọn nút ở bên trái hộp Giá trị, rồi thực hiện một trong các thao tác sau:
- Để sử dụng tham số hiện có, chọn Tham số, rồi chọn tham số bạn muốn từ danh sách xuất hiện ở bên phải.
- Để sử dụng tham số mới, chọn Tham số Mới, rồi tạo tham số.
Nhập ngày mới vào hộp Giá trị Hiện tại, rồi chọn Đóng Trang>đầu & tải.
Để xác nhận kết quả của bạn, hãy thêm dữ liệu mới vào nguồn dữ liệu, rồi làm mới truy vấn dữ liệu bằng tham số cập nhật (Chọn Làm> mớiDữ liệu Tất cả). Ví dụ: thay đổi giá trị bộ lọc thành một ngày khác để xem kết quả mới.
Nhập ngày mới vào hộp Giá trị Hiện tại.
Chọn Đóng>Màn hình & Tải.
Để xác nhận kết quả của bạn, hãy thêm dữ liệu mới vào nguồn dữ liệu, rồi làm mới truy vấn dữ liệu bằng tham số cập nhật (Chọn Làm> mớiDữ liệu Tất cả).
Sử dụng giá trị ô để lọc dữ liệu
Trong ví dụ này, giá trị trong tham số truy vấn được đọc từ một ô trong sổ làm việc của bạn. Bạn không cần phải thay đổi truy vấn tham số, bạn chỉ cần cập nhật giá trị ô. Ví dụ: bạn muốn lọc cột theo chữ cái đầu tiên nhưng dễ dàng thay đổi giá trị thành chữ cái bất kỳ từ A đến Z.
Trên trang tính trong sổ làm việc có tải truy vấn bạn muốn lọc, hãy tạo bảng Excel có hai ô: tiêu đề và giá trị.
Bộ lọc của Tôi G Chọn một ô trong bảng Excel, rồi chọn Dữ liệu Lấy>Dữ liệu>từ Bảng/Dải ô. Biểu Trình soạn thảo Power Query xuất hiện.
Trong hộp Tên của ngăn Thiết đặt Truy vấn ở bên phải, hãy thay đổi tên truy vấn để có ý nghĩa hơn, chẳng hạn như FilterCellValue.
Để truyền giá trị trong bảng chứ không phải bản thân bảng, hãy bấm chuột phải vào giá trị trong Xem trước Dữ liệu, rồi chọn Truy sâu Xuống.
Lưu ý rằng công thức đã thay đổi thành= #"Changed Type"{0}[MyFilter]
Khi bạn sử dụng Bảng Excel làm bộ lọc ở bước 10, Power Query tham chiếu giá trị Bảng làm điều kiện lọc. Tham chiếu trực tiếp đến Bảng Excel sẽ gây ra lỗi.Chọn Đóng>Màn hình & Tải>Đóng & Tải Tới. Bây giờ bạn có một tham số truy vấn có tên là "FilterCellValue" mà bạn sử dụng ở bước 12.
Trong hộp thoại Nhập Dữ liệu, chọn Chỉ Tạo Kết nối, rồi chọn OK.
Mở truy vấn bạn muốn lọc với giá trị trong bảng FilterCellValue, trước đó đã tải một truy vấn từ Trình soạn thảo Power Query, bằng cách chọn một ô trong dữ liệu, > rồi chọn Sửa Truyvấn. Để biết thêm thông tin , hãy xem mục Tạo, tải hoặc chỉnh sửa truy vấn trong Excel.
Chọn mũi tên bộ lọc trong bất kỳ tiêu đề cột nào để lọc dữ liệu của bạn, rồi chọn lệnh lọc, chẳng hạn như Bộ lọc Văn bản>Bắt đầu Với. Hộp thoại Lọc Hàng xuất hiện.
Nhập bất kỳ giá trị nào vào hộp Giá trị, chẳng hạn như "G" rồi chọn OK. Trong trường hợp này, giá trị là chỗ dành sẵn tạm thời cho giá trị trong bảng FilterCellValue mà bạn nhập ở bước tiếp theo.
Chọn mũi tên ở bên phải thanh công thức để hiển thị toàn bộ công thức. Đây là ví dụ về điều kiện lọc trong công thức:
= Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))
Chọn giá trị của bộ lọc. Trong công thức, chọn "G".
Sử dụng M Intellisense, nhập một vài chữ cái đầu tiên của bảng FilterCellValue bạn đã tạo, rồi chọn bảng đó từ danh sách xuất hiện.
Chọn Trang>chủ Đóng>Đóng & Tải.
Kết quả
Truy vấn của bạn bây giờ sử dụng giá trị trong Bảng Excel mà bạn đã tạo để lọc kết quả truy vấn. Để sử dụng một giá trị mới, hãy chỉnh sửa nội dung ô trong bảng Excel gốc ở bước 1, thay đổi "G" thành "V", rồi làm mới truy vấn.
Kiểm soát việc sử dụng truy vấn tham số
Bạn có thể kiểm soát việc truy vấn tham số có được cho phép hay không.
- Trong hộp kiểm Trình soạn thảo Power Query chọn Tùy chọn Tệp>và Tùy chọn Truy>vấn Thiết>đặt Trình soạn thảo Power Query.
- Trong ngăn bên trái, bên dưới MỤC TOÀN CẦU, chọn Trình soạn thảo Power Query.
- Trong ngăn bên phải, bên dưới Tham số, chọn hoặc bỏ chọn Luôn cho phép tham số hóa trong hộp thoại nguồn dữ liệu và chuyển đổi.