Bạn có thể khá quen thuộc với truy vấn tham số với cách sử dụng chúng trong SQL hoặc Microsoft Query. Tuy nhiên, tham số Power Query có những điểm khác biệt chính:
- Tham số có thể được dùng trong bất kỳ bước truy vấn nào. Ngoài việc đóng vai trò là bộ lọc dữ liệu, tham số có thể được dùng để xác định những thứ như đường dẫn tệp hay tên máy chủ.
- Tham số không nhắc nhập dữ liệu. Thay vào đó, bạn có thể nhanh chóng thay đổi giá trị của chúng bằng cách dùng Power Query. Bạn thậm chí có thể lưu trữ và truy xuất giá trị từ các ô trong Excel.
- Các tham số được lưu trong một truy vấn tham số đơn giản, nhưng tách biệt với truy vấn dữ liệu mà chúng được dùng trong. Sau khi tạo, bạn có thể thêm tham số vào truy vấn khi cần.
Lưu ý Nếu bạn muốn sử dụng cách khác để tạo các 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 chỉ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 vào một 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>Lấy Dữ liệu>Các Nguồn> KhácCho chạy Trình soạn thảo Power Query.
Trong Trình soạn thảo Power Query, hãy chọn Trang đầu>Quản lý Tham số > Tham số Mới.
Trong hộp thoại Quản lý Tham số , chọn Mới.
Đặt như sau nếu cần:
Tên Điều này nên phản ánh chức năng của tham số, nhưng hãy giữ càng ngắn càng tốt. Mô tả Danh sách này có thể chứa bất kỳ chi tiết nào sẽ giúp mọi người sử dụng tham số đúng cách. Bắt buộc Thực hiện một trong những thao tác sau:
Giá trị bất kỳ Bạn có thể nhập giá trị bất kỳ 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 giá trị trong một danh sách cụ thể bằng cách nhập giá trị vào lưới nhỏ. Bạn cũng phải chọn Giá trị Mặc định và Giá trị Hiện thời ở bên dưới.
Truy vấn Chọn truy vấn danh sách giống như cột có cấu trúc Danh sách được phân tách bằng dấu phẩy và được đặt trong dấu ngoặc nhọn.
Ví dụ: một trường trạng thái sự cố có thể có ba giá trị: {"New", "Ongoing", "Closed"}. Bạn phải tạo truy vấn danh sách trước bằng cách mở Trình chỉnh sửa nâng cao (chọn Trang chủ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 hoàn tất việc tạo tham số, truy vấn danh sách sẽ được hiển thị trong các giá trị tham số của bạn.Loại Mục này sẽ chỉ định kiểu dữ liệu của tham số. Giá trị gợi ý Nếu muốn, bạn hãy thêm danh sách các giá trị hoặc chỉ định một truy vấn để cung cấp các đề xuất cho đầu vào. Giá trị Mặc định Tùy chọn này chỉ xuất hiện nếu Giá trị Đề xuất được đặt thành Danh sách giá trị và chỉ đị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 giá trị này 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 thờ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à một 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ự nhau, hãy tạo tham số để dễ dàng thay đổi nguồn dữ liệu và giúp ngăn các 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 vị trí sẽ thay đổi. Có thể một người quản lý cơ sở dữ liệu thỉnh thoảng hoán đổi máy chủ, một lượng tệp CSV thả hàng tháng vào một thư mục khác hoặc bạn cần dễ dàng chuyển đổi giữa 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 số tệp CSV mà bạn nhập bằng thao tác nhập thư mục (Chọn Dữ liệu>Lấy Dữ liệu>từ FilesFrom>Folder) 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 vào hộp thoại Quản lý Tham số :
Tên CSVFileDrop Mô tả Vị trí thả tệp thay thế Bắt buộc Có Loại Văn bản Giá trị gợi ý Giá trị bất kỳ 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 các 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 có thể chỉ cần cập nhật truy vấn tham số.
- Chọn Kết nối Dữ liệu>& Truy vấn Tab>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 Trang chủ>, Đóng & 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 với tham số được cập nhật (Chọn Dữ liệu>Làm mới Tất cả).
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 truy vấn để thu đượ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 ta thay đổi ngày để thuận tiện thay đổi bộ lọc dữ liệu.
Để mở một truy vấn, hãy tìm một truy vấn đã được 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>. Để biết thêm thông tin, hãy xem Tạo, tải hoặc chỉnh sửa truy vấn trong Excel.
Chọn mũi tên 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 một lệnh lọc, chẳng hạn như Ngày/Thời gian Lọc>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 một tham số hiện có, hãy 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 với tham số được cập nhật (Chọn Dữ liệu>Làm mới Tất cả). Ví dụ, thay đổi giá trị bộ lọc sang một ngày khác để xem kết quả mới.
Nhập ngày mới trong hộp Giá trị Hiện tại .
Chọn Trang chủ>, Đóng & 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 với tham số được cập nhật (Chọn Dữ liệu>Làm mới 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 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 một cột theo chữ cái đầu tiên nhưng muốn 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 nơi tải truy vấn bạn muốn lọc, hãy tạo một bảng Excel với hai ô: tiêu đề và giá trị.
Bộ lọc của Tôi G Chọn một ô trong bảng Excel, sau đó chọn Dữ liệu Lấy>dữ liệu>từ bảng/phạm vi. 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 cho tên có ý nghĩa hơn, chẳng hạn như FilterCellValue.
Để truyền giá trị trong bảng chứ không phải chính 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 trong bước 10, Power Query sẽ 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 có thể gây ra lỗi.Chọn Đóng Trang đầu>& Tải Đóng>& Tải Vào. Bây giờ bạn có một tham số truy vấn tên là "FilterCellValue" được 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 mà bạn muốn lọc với giá trị trong bảng FilterCellValue, một bảng đã được tải trước đó từ Trình soạn thảo Power Query, bằng cách chọn một ô trong dữ liệu, rồi chọnSửa Truyvấn>. Để biết thêm thông tin, hãy xem Tạo, tải hoặc chỉnh sửa truy vấn trong Excel.
Chọn mũi tên lọc trong bất kỳ tiêu đề cột nào để lọc dữ liệu của bạn, sau đó chọn một lệnh lọc, chẳng hạn như Bộ lọc> Văn bảnBắt đầu Với. Hộp thoại Lọc Hàng xuất hiện.
Nhập giá trị bất kỳ 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, hãy chọn "G".
Sử dụng M Intellisense, nhập vài chữ cái đầu tiên của bảng FilterCellValue bạn đã tạo, rồi chọn chữ cái đó từ danh sách xuất hiện.
ChọnĐóng Trang>đầu>Đó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. Để dùng giá trị mới, hãy 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 cho phép hoặc không cho phép truy vấn tham số.
- Trong Trình soạn thảo Power Query, hãy chọnTùy chọn Tệp> và Thiết đặt >Tùy chọn Truy>vấnTrình soạn thảo Power Query.
- Trong ngăn bên trái, dưới TOÀN CẦU, hãy chọn Trình soạn thảo Power Query.
- Trong ngăn bên phải, 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.