Tạo truy vấn tham số (Power Query)

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.

1. Chọn Dữ >Lấy Dữ liệu >Nguồn Khác để > khởi chạy Trình soạn thảo Power Query.

2. Trong hộp tìm Trình soạn thảo Power Query, chọn Trang đầu để > lý tham > số mới.

3. Trong hộp thoại Quản lý Tham số, chọn Mới.

4. Đặ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:Bất kỳ Giá trị Nào 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.

5. Để tạo tham số, chọn OK.

Đâ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ệu> Từ tệp > 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.

1. Chọn Trang đầu > quản lý tham > số mới.

2. 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

3. Chọn OK.

Bước 2: Thêm tham số vào truy vấn dữ liệu

1. Để đặ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.

2. Đả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.

3. 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ố.

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

2. Nhập vị trí mới vào hộp Giá trị Hiện tại, chẳng hạn như C:\DataFilesCSV2.

3. Chọn Trang > Đóng & Tải.

4. Để 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 Dữ liệu> Làm mới Tất cả).

Đô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.

1. Để mở 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. Để 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.

2. 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.
   
   

3. 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ố.

4. Nhập ngày mới vào hộp Giá trị Hiện tại, rồi chọn Trang đầu> Đóng & Tải.

5. Để 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 Dữ liệu> Làm mới 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.

6. Nhập ngày mới vào hộp Giá trị Hiện tại.

7. Chọn Trang > Đóng & Tải.

8. Để 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 Dữ liệu> Làm mới Tất cả).

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.

1. 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

2. Chọn một ô trong bảng Excel, sau đó 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.

3. 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. 

4. Để 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 công = #"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.

5. Chọn Trang > Đóng & tải >đóng & tải vào. 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.

6. Trong hộp thoại Nhập Dữ liệu, chọn Chỉ Tạo Kết nối, rồi chọn OK.

7. 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 Truy vấn> Sửa. Để 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.

8. 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 một lệnh lọc, chẳng hạn như Bộ lọc Văn bản > đầu bằng. Hộp thoại Lọc Hàng xuất hiện. 

9. 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.

10. 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"))

11. Chọn giá trị của bộ lọc. Trong công thức, chọn "G".

12. 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.

13. Chọn Trang > Đó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.

Bạn có thể kiểm soát việc truy vấn tham số có được cho phép hay không.

1. Trong hộp thoại Trình soạn thảo Power Query, chọn Tùy chọn > Tệp và Thiết > Chọn Truy vấn > Trình soạn thảo Power Query.

2. 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.

3. 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.