Nếu bạn thấy mình đang ở trong tình huống cần áp dụng cùng một tập hợp các phép biến đổi cho các truy vấn hoặc giá trị khác nhau, việc tạo một hàm tùy chỉnh Power Query có thể được sử dụng lại nhiều lần tùy ý có thể sẽ hữu ích. Hàm tùy chỉnh Power Query là phép ánh xạ từ một tập hợp các giá trị đầu vào thành một giá trị đầu ra duy nhất và được tạo từ M hàm và toán tử gốc.

Bạn có thể tự tạo hàm tùy chỉnh Power Query của riêng mình bằng ngôn ngữ công thức Power Query M hoặc giao diện người dùng Power Query cung cấp cho bạn các tính năng giúp tăng tốc, đơn giản hóa và nâng cao quy trình tạo và quản lý hàm tùy chỉnh.

Đầu tiên, chúng ta sẽ tìm hiểu các bước cơ bản để tạo một hàm tùy chỉnh bằng mã trong UI , sau đó chúng ta sẽ tập trung vào việc sử dụng giao diện để biến các hành động phức tạp thành một hàm có thể tái sử dụng .

1. Sử dụng trải nghiệm kết nối để kết nối với dữ liệu của bạn tại nơi lưu trữ. Sau khi chọn dữ liệu, hãy chọn nút Chuyển đổi Dữ liệu hoặc Chỉnh sửa . Thao tác này sẽ đưa bạn đến trải nghiệm Power Query.
2. Nhấp chuột phải vào chỗ trống trong ngăn Truy vấn ở bên trái.
3. Chọn truy vấn trống .
4. Trong cửa sổ truy vấn trống mới, hãy chọn menu Trang chủ , sau đó chọn Trình soạn thảo nâng cao .
5. Thay thế mẫu bằng hàm tùy chỉnh của bạn. Ví dụ:
   Power Query M

   let
    HelloWorld = () => ("Hello World")
   in
    HelloWorld
   

6. Chọn Xong .

Để biết thêm thông tin về cách phát triển các hàm tùy chỉnh bằng ngôn ngữ công thức Power Query M, hãy xem bài viết này: Tìm hiểu về các hàm Power Query M. Trong các phần sau, có các hướng dẫn mô tả cách bạn có thể sử dụng giao diện người dùng Power Query để phát triển các hàm tùy chỉnh mà không cần viết mã, cũng như hướng dẫn cách gọi hàm tùy chỉnh trong truy vấn của bạn.

Bạn có thể làm theo ví dụ này bằng cách tải xuống các tệp mẫu được sử dụng trong bài viết này từ liên kết tải xuống sau . Để đơn giản, bài viết này sử dụng trình kết nối Thư mục. Để tìm hiểu thêm về trình kết nối Thư mục, hãy truy cập Thư mục . Mục tiêu của ví dụ này là tạo một hàm tùy chỉnh có thể áp dụng cho tất cả các tệp trong thư mục đó trước khi kết hợp tất cả dữ liệu từ tất cả các tệp vào một bảng duy nhất.

Bắt đầu bằng cách sử dụng trình kết nối Thư mục để điều hướng đến thư mục chứa tệp của bạn và chọn Chuyển đổi Dữ liệu hoặc Chỉnh sửa . Các bước này sẽ đưa bạn đến trải nghiệm Power Query. Nhấp chuột phải vào giá trị Nhị phân bạn chọn từ trường Nội dung và chọn tùy chọn Thêm dưới dạng Truy vấn Mới . Trong ví dụ này, lựa chọn được thực hiện cho tệp đầu tiên trong danh sách, đó là tệp April 2019.csv .

Tùy chọn này thực sự tạo một truy vấn mới với bước điều hướng trực tiếp đến tệp đó dưới dạng tệp nhị phân, và tên của truy vấn mới này là đường dẫn tệp của tệp đã chọn. Đổi tên truy vấn này thành Tệp Mẫu .

Tạo một tham số mới với tên Tham số Tệp và kiểu là Nhị phân . Sử dụng truy vấn Tệp Mẫu làm Giá trị Mặc định và Giá trị Hiện tại .

Nhấp chuột phải vào Tham số Tệp từ ngăn Truy vấn . Chọn tùy chọn Tham chiếu .

Đổi tên truy vấn mới tạo từ Tham số tệp (2) thành Tệp mẫu chuyển đổi .

Nhấp chuột phải vào tệp truy vấn Mẫu chuyển đổi mới này và chọn tùy chọn Tạo hàm .

Thao tác này thực sự tạo một hàm mới liên kết với truy vấn tệp Mẫu Biến đổi . Mọi thay đổi bạn thực hiện đối với truy vấn tệp Mẫu Biến đổi sẽ được tự động sao chép sang hàm tùy chỉnh của bạn. Trong quá trình tạo hàm mới này, hãy sử dụng tệp Biến đổi làm tên Hàm .

Sau khi tạo hàm, hãy lưu ý rằng một nhóm mới sẽ được tạo cho bạn với tên hàm của bạn. Nhóm mới này bao gồm:

• Tất cả các tham số được tham chiếu trong truy vấn tệp Mẫu chuyển đổi của bạn .
• Tệp truy vấn mẫu chuyển đổi của bạn , thường được gọi là truy vấn mẫu .
• Hàm mới tạo của bạn, trong trường hợp này là Transform file .

Sau khi tạo hàm mới, hãy chọn truy vấn có tên là Transform Sample file . Truy vấn này hiện được liên kết với hàm Transform file , do đó mọi thay đổi được thực hiện đối với truy vấn này đều được phản ánh trong hàm. Kết nối này được gọi là khái niệm truy vấn mẫu được liên kết với một hàm.

Phép biến đổi đầu tiên cần thực hiện cho truy vấn này là phép biến đổi để diễn giải tệp nhị phân. Bạn có thể nhấp chuột phải vào tệp nhị phân từ khung xem trước và chọn tùy chọn CSV để diễn giải tệp nhị phân thành tệp CSV.

Định dạng của tất cả các tệp CSV trong thư mục đều giống nhau. Tất cả đều có tiêu đề trải dài bốn hàng đầu tiên. Tiêu đề cột nằm ở hàng 5 và dữ liệu bắt đầu từ hàng 6 trở xuống, như minh họa trong hình tiếp theo.

Các bước chuyển đổi tiếp theo cần áp dụng cho tệp Mẫu chuyển đổi là:

1. Xóa bốn hàng trên cùng — Hành động này sẽ xóa các hàng được coi là một phần của phần tiêu đề của tệp.

    Ghi chú

   Để tìm hiểu thêm về cách xóa hàng hoặc lọc bảng theo vị trí hàng, hãy truy cập Lọc theo vị trí hàng .

2. Nâng cấp tiêu đề — Tiêu đề cho bảng cuối cùng của bạn hiện nằm ở hàng đầu tiên của bảng. Bạn có thể nâng cấp chúng như trong hình ảnh tiếp theo.

Sau khi quảng bá tiêu đề cột, Power Query theo mặc định sẽ tự động thêm bước Loại Thay đổi mới để tự động phát hiện kiểu dữ liệu cho mỗi cột. Tệp truy vấn Mẫu Chuyển đổi của bạn trông giống như hình ảnh tiếp theo.

Với hàm tùy chỉnh hiện đã được tạo và tất cả các bước chuyển đổi đã được kết hợp, bạn có thể quay lại truy vấn ban đầu, nơi bạn có danh sách các tệp từ thư mục ( Tệp CSV trong ví dụ này). Bên trong tab Thêm cột trong ruy-băng, chọn Gọi hàm tùy chỉnh từ nhóm Chung . Bên trong cửa sổ Gọi hàm tùy chỉnh , nhập Bảng đầu ra làm tên cột Mới . Chọn tên hàm của bạn, Tệp chuyển đổi , từ danh sách thả xuống Truy vấn hàm . Sau khi bạn chọn hàm từ menu thả xuống, tham số cho hàm sẽ được hiển thị và bạn có thể chọn cột nào từ bảng để sử dụng làm đối số cho hàm này. Chọn cột Nội dung làm giá trị/đối số sẽ được truyền cho Tham số tệp .

Sau khi bạn chọn OK , một cột mới có tên là Bảng Đầu Ra sẽ được tạo. Cột này chứa các giá trị Bảng trong các ô của nó, như thể hiện trong hình ảnh tiếp theo. Để đơn giản, hãy xóa tất cả các cột khỏi bảng này ngoại trừ Tên và Bảng Đầu Ra .

Hàm của bạn đã được áp dụng cho từng hàng trong bảng bằng cách sử dụng các giá trị từ cột Nội dung làm đối số cho hàm. Bây giờ dữ liệu đã được chuyển đổi thành hình dạng bạn mong muốn, bạn có thể mở rộng cột Bảng Đầu ra bằng cách chọn biểu tượng Mở rộng. Không sử dụng bất kỳ tiền tố nào cho các cột được mở rộng.

Bạn có thể xác minh rằng bạn có dữ liệu từ tất cả các tệp trong thư mục bằng cách kiểm tra các giá trị trong cột Tên hoặc Ngày . Trong trường hợp này, bạn có thể kiểm tra các giá trị từ cột Ngày , vì mỗi tệp chỉ chứa dữ liệu của một tháng trong một năm nhất định. Nếu bạn thấy nhiều hơn một, điều đó có nghĩa là bạn đã kết hợp thành công dữ liệu từ nhiều tệp vào một bảng duy nhất.

Hãy tưởng tượng có một yêu cầu mới được thêm vào những gì bạn đang xây dựng. Yêu cầu mới này yêu cầu trước khi kết hợp các tệp, bạn phải lọc dữ liệu bên trong chúng để chỉ lấy các hàng có Quốc gia bằng Panama .

Để thực hiện yêu cầu này, hãy tạo một tham số mới có tên là Market với kiểu dữ liệu là text. Đối với Current Value , hãy nhập giá trị Panama .

Với tham số mới này, hãy chọn truy vấn tệp Mẫu chuyển đổi và lọc trường Quốc gia bằng giá trị từ tham số Thị trường .

Áp dụng bước mới này vào truy vấn của bạn sẽ tự động cập nhật hàm Tệp chuyển đổi , hiện yêu cầu hai tham số dựa trên hai tham số mà tệp Mẫu chuyển đổi của bạn sử dụng.

Tuy nhiên, truy vấn tệp CSV có một dấu hiệu cảnh báo bên cạnh. Giờ hàm của bạn đã được cập nhật, nó yêu cầu hai tham số. Vì vậy, bước bạn gọi hàm sẽ dẫn đến các giá trị lỗi, vì chỉ có một đối số được truyền cho hàm Chuyển đổi tệp trong bước Gọi Hàm Tùy chỉnh .

Để sửa lỗi, hãy nhấp đúp vào Invoked Custom Function trong mục Applied Steps để mở cửa sổ Invoked Custom Function . Trong tham số Market , hãy nhập thủ công giá trị Panama .

Bây giờ bạn có thể quay lại Bảng Đầu ra Mở rộng trong mục Các Bước Áp dụng . Hãy kiểm tra truy vấn của bạn để xác thực rằng chỉ những hàng có Quốc gia bằng Panama mới hiển thị trong tập kết quả cuối cùng của truy vấn Tệp CSV .

Nếu bạn có nhiều truy vấn hoặc giá trị yêu cầu cùng một tập hợp các phép biến đổi, bạn có thể tạo một hàm tùy chỉnh hoạt động như một phần logic có thể tái sử dụng. Sau đó, hàm tùy chỉnh này có thể được gọi để xử lý các truy vấn hoặc giá trị bạn chọn. Hàm tùy chỉnh này có thể giúp bạn tiết kiệm thời gian và quản lý tập hợp các phép biến đổi tại một vị trí trung tâm, nơi bạn có thể chỉnh sửa bất cứ lúc nào.

Ví dụ, hãy tưởng tượng một truy vấn có nhiều mã dưới dạng chuỗi văn bản và bạn muốn tạo một hàm giải mã các giá trị đó, như trong bảng mẫu sau:

Đầu tiên, bạn cần có một tham số có giá trị làm ví dụ. Trong trường hợp này, đó là giá trị PTY-CM1090-LAX .

Từ tham số đó, bạn tạo một truy vấn mới để áp dụng các phép biến đổi cần thiết. Trong trường hợp này, bạn muốn chia mã PTY-CM1090-LAX thành nhiều thành phần:

• Nguồn gốc = PTY
• Điểm đến = LAX
• Hãng hàng không = CM
• FlightID = 1090

Mã M sau đây minh họa tập hợp các phép biến đổi đó.

let
    Source = code,
    SplitValues = Text.Split( Source, "-"),
    CreateRow = [Origin= SplitValues{0}, Destination= SplitValues{2}, Airline=Text.Start( SplitValues{1},2), FlightID= Text.End( SplitValues{1}, Text.Length( SplitValues{1} ) - 2) ],
    RowToTable = Table.FromRecords( {  CreateRow } ),
    #"Changed Type" = Table.TransformColumnTypes(RowToTable,{{"Origin", type text}, {"Destination", type text}, {"Airline", type text}, {"FlightID", type text}})
in
    #"Changed Type"

Sau đó, bạn có thể chuyển đổi truy vấn đó thành một hàm bằng cách nhấp chuột phải vào truy vấn và chọn Tạo Hàm . Cuối cùng, bạn có thể gọi hàm tùy chỉnh của mình vào bất kỳ truy vấn hoặc giá trị nào, như minh họa trong hình ảnh tiếp theo.

Sau một vài lần chuyển đổi nữa, bạn có thể thấy rằng mình đã đạt được kết quả mong muốn và áp dụng logic cho phép chuyển đổi như vậy từ một hàm tùy chỉnh.