POWER QUERY – CHỨC NĂNG TÙY CHỈNH

Siêu Marketing sẽ giới thiệu trong bài viết này về hàm tùy chỉnh trong Power Query. Chúng là gì, khi nào cần sử dụng và cách tạo chúng như thế nào. Với hơn 600 hàm có sẵn trong Power Query, hãy cùng Siêu Marketing tìm hiểu rõ hơn.

Mặc dù có sẵn nhiều hàm, chúng không phải lúc nào cũng mang lại kết quả theo yêu cầu hay theo cách hiệu quả nhất. Do đó, chúng ta có thể cần đến các hàm tùy chỉnh để có kết quả mong muốn.

Chức năng tùy chỉnh là gì?

Chức năng là các quá trình hoặc bước thực hiện công việc một cách nhất quán. Chẳng hạn, Hàm SUM trong Excel sẽ liên tục cộng dồn giá trị hoặc hàm VLOOKUP sẽ liên tục tìm và trả về giá trị tương ứng từ danh sách.

Nhưng không chỉ có tính toán số học và xử lý văn bản, các hàm còn có thể xử lý nhiều tác vụ khác. Trong Power Query, các hàm tùy chỉnh có thể được dùng để thu thập thông tin từ trang web hay đọc dữ liệu từ một file. Trong Power Query, nên xem các hàm như là các công cụ để áp dụng cho các tác vụ lặp đi lặp lại.

Tôi đã suy nghĩ kỹ về việc đưa các chức năng tùy chỉnh vào loạt bài giảng này vì chúng có phần phức tạp. Tuy nhiên, tôi nhận ra rằng đó là một điều cần thiết để giới thiệu. Bài viết này yêu cầu một ít kỹ năng lập trình và việc chỉnh sửa mã M tự động, do đó chú ý theo dõi chi tiết nhé.

Chúng ta sẽ xem xét này thông qua hai ví dụ: (1) một hàm tùy chỉnh đơn giản và (2) một hàm tùy chỉnh dựa trên các chuyển đổi.

Downloads

Hãy tải các tệp mẫu về để làm theo các ví dụ dưới đây. Nhấp ‘Click here’ để đăng ký và truy cập mục Downloads.

Chúng ta sẽ sử dụng cùng một tệp từ bài học trước. Các ví dụ dưới đây sẽ dựa vào tệp có tên là Example 15 – If Function.xlsx.

File mẫu chỉ bao gồm một bảng. Để nhập dữ liệu vào Power Query, chọn bất kỳ ô nào trong bảng đó và nhấp vào Data -> From Table/Range.

Đặt tên cho truy vấn một cách hợp lý – Tôi đã đặt tên nó là Data và tôi sẽ sử dụng tên này để tham chiếu đến truy vấn trong bài viết.

Chức năng tùy chỉnh dễ dàng

Trong ví dụ đầu tiên này, chúng ta sẽ tạo ra một hàm tùy chỉnh rất cơ bản, chỉ để minh họa cho quy trình. Điều này giúp chuẩn bị cho ví dụ tiếp theo, giúp mọi thứ trở nên dễ theo dõi.

Bắt đầu bằng cách tạo truy vấn mới từ ‘Blank Query’ qua các bước Home -> New Source -> Other Sources -> Blank Query.

Đặt tên cho truy vấn này. Để minh họa, tôi đã đặt tên nó là myFunction1 (không phải là tên thực sự, bạn nên đặt một tên mô tả hơn, nhưng nó sẽ phục vụ cho ví dụ).

Mở Advanced Editor cho truy vấn bằng cách nhấp vào View -> Advanced Editor hoặc Home -> Advanced Editor).

Cửa sổ Advanced Editor sẽ xuất hiện như sau:

Ngay cả khi là truy vấn mới, chúng ta vẫn có một vài dòng mã mặc định. Lệnh ‘let’ và ‘in’ sẽ vẫn được giữ trong mã cuối cùng, nhưng ta sẽ sửa phần còn lại.

Mục tiêu của ví dụ đầu tiên này là tạo một hàm sẽ nhân giá trị đầu vào với 10.

Điền vào văn bản sau:

Biên soạn cấp cao.

ĐIỀU QUAN TRỌNG: Mã M phân biệt giữa chữ viết hoa và không viết hoa, do đó nhập mã một cách thận trọng.

Chi tiết về mã sẽ được làm rõ trong bài viết sau, tuy nhiên hiện tại bạn chỉ cần bấm vào Nút Hoàn tất để đóng lại cửa sổ Biên soạn cấp cao.

Sau đây chúng ta sẽ quan sát những phần tiếp theo. Tại đây, chúng ta có thể kiểm tra tính năng mới được lập trình. Hãy nhập một giá trị vào ô Giá trị và bấm vào Gọi hàm.

Nếu mọi việc được thực hiện đúng đắn, hệ thống sẽ tính toán và trả về một giá trị chính xác.

Power Query sẽ tạo một truy vấn mới với tên Gọi Hàm khi bạn gọi hàm, dù chúng ta không cần đến nó nên có thể xóa bỏ. Trong danh sách truy vấn, bạn sẽ thấy truy vấn mà chúng ta đã tạo ban đầu (mà chúng tôi đặt tên là Dữ liệu) và hàm chúng ta vừa tạo (được gọi là myFunction1).

Bây giờ, hãy mở truy vấn Dữ liệu mà chúng ta đã tạo từ trước. Thêm một Cột Tùy Chỉnh (Thêm Cột -> Cột Tùy Chỉnh) và áp dụng chức năng tùy chỉnh chúng ta vừa lập trình.

Mã cần sử dụng trong cửa sổ Cột Tùy Chỉnh là:

=myFunction1([Giá Trị])

Chú ý: Từ [Giá Trị] trong hàm chỉ đến tên cột được dùng làm đối số.

Áp dụng hàm tùy chỉnh không khác gì việc sử dụng hàm Power Query thông thường.

Phiên mã M

Hãy giải mã lại mã chúng ta đã lập trình để hiểu cách thức hoạt động của nó.

Khởi tạo tham số

Dòng đầu của mã mô tả các tham số (hoặc đầu vào) cho hàm. Trong trường hợp này, chúng ta chỉ có một tham số, được gọi là Giá Trị.

Dòng tiếp theo cho biết điểm khởi đầu cho quá trình chuyển đổi; không cần thiết phải thay đổi gì ở đây.

Sau đó, chúng ta sẽ xây dựng một bước mới, tên là nameOfStep. Có những quy tắc đặc biệt về việc đặt tên bước, bao gồm cả việc không dùng dấu cách. Để đơn giản, nên bỏ quen việc thêm khoảng cách khi bạn đang tự mình tạo các bước.

nameOfStep sẽ thực hiện việc lấy Giá Trị (tham số đã khai báo ở dòng đầu) và nhân nó với 10.

Cuối cùng, kết quả của hàm được trả lại trong phần in ở cuối các bước chuyển đổi. Câu lệnh sau từ khóa in sẽ cho biết tên bước cần trả lại. Về phần chúng ta, vì chỉ có một bước nên chúng ta sẽ trả lại nameOfStep.

Đa tham số và các loại dữ liệu

Khi sử dụng hàm Power Query chuẩn, bạn sẽ thấy chúng thường yêu cầu nhiều loại tham số và dữ liệu cụ thể. Chúng ta cũng có thể thiết kế những điều tương tự cho hàm tùy chỉnh của chính mình, và tất cả đều được giải quyết khi bạn đặt tham số đầu tiên của truy vấn:

Khởi tạo kiểu dữ liệu đầu vào

Để thiết lập kiểu dữ liệu văn bản, chúng ta thay đổi mã như sau:

Nếu muốn chỉ sử dụng bảng dữ liệu làm tham số, thì chúng ta sẽ thực hiện:

Việc này rất đơn giản, phải không nào. Như bạn đã biết, có một loạt kiểu dữ liệu khác nhau. Nếu bạn còn phân vân về cách viết mã cho kiểu dữ liệu nào đó, hãy thử với một truy vấn khác, thay đổi kiểu dữ liệu qua giao diện người dùng thông thường và sau đó kiểm tra mã mà nó tạo ra để biết cách thay đổi như thế nào.

Khởi tạo kiểu dữ liệu đầu ra

Power Query cũng cần xác định kiểu dữ liệu cho kết quả trả về của một hàm; điều này thường được chỉ ra bên ngoài cặp ngoặc đơn.

Ví dụ trên chỉ ra rằng, hàm sẽ nhận một tham số kiểu văn bản và kết quả trả về là kiểu dữ liệu bảng.

Khởi tạo nhiều tham số

Để xác định nhiều tham số cho một hàm, chúng ta viết chúng liền kề nhau trong cặp ngoặc đơn.

Khởi tạo đã giới thiệu:

  • Hai tham số được gọi là (1) myText (kiểu dữ liệu văn bản) và (2) myTable (kiểu dữ liệu bảng).
    • Kiểu dữ liệu của đầu ra đã được định nghĩa là kiểu số.

    Phép biến đổi trong các hàm tùy biến

    Sự nhập dữ liệu từ web đã được chúng ta áp dụng trong một bài trước, sử dụng phương pháp tương tự. Hãy tham khảo bài viết đó để thực hành thêm. Nhưng lần này công việc chúng ta thực hiện sẽ phức tạp hơn đôi chút.

    Chúng ta sẽ tính phần trăm giao dịch so với tổng số lần mua của một khách hàng cụ thể trong ví dụ sau. Điều này có nghĩa, nếu khách hàng ABC giảm giá đã chi tiêu $122, thì tỷ lệ phần trăm của mỗi giao dịch là bao nhiêu. Hình dưới đây cho thấy kết quả mà chúng ta đang cố đạt được. ABC đã thực hiện hai giao dịch, lần lượt chiếm 37,7% cho giao dịch đầu và 62,3% cho giao dịch thứ hai.

    Đối với Excel, biến đổi này tương đương với việc chia giá trị trong mỗi dòng cho SUMIFS của Khách hàng.

    Sao chép truy vấn

    Hãy nhân bản Truy vấn Data mà trước đó chúng ta đã nhập. Lý do là chúng ta cần (1) một truy vấn dưới dạng hàm và (2) một truy vấn để tiếp nhận hàm này. Sau đó nhấn chuột phải vào truy vấn trong danh sách và chọn Duplicate.

    Đổi tên cho truy vấn

    Tên truy vấn mà chúng ta gán sẽ dùng để gọi hàm.

    Truy vấn của tôi đã được đặt tên là fnCustomerTotal. Đặt fn ở trước tên là một quy ước thông dụng khi tạo hàm tùy chỉnh, giúp dễ nhận biết đây là một hàm.

    Tiến hành chuyển đổi

    Loại bỏ mọi bước không liên quan, chỉ giữ lại bước Source.

    Sau đó chọn Transform -> Group By. Group By đã được giới thiệu trong một phần trước đây của loạt bài. Trong cửa sổ Group By, hãy nhập các thông tin sau:

    • Column: Customer
    • New column name: Customer Total
    • Operation: Sum
    • Column: Value

    Nhấp chọn OK.

    Lọc cột Customer để chọn một khách hàng đơn lẻ (trong trường hợp này là ABC Discounts).

    Chuyển truy vấn thành hàm

    Mở Advanced Editor:

    Phần này khá phức tạp… tiến hành sửa đổi mã M cho chính xác.

    Khai báo tham số ở dòng đầu tiên của mã. Chúng ta cần hai tham số (1) bảng sẽ sử dụng và (2) tên khách hàng để bộ lọc.

    Như chúng ta định dùng bảng được khai báo làm nguồn dữ liệu, không cần giữ lại bước Source trong mã. Hãy xóa dòng đó đi.

    Dòng tiếp theo cần được cập nhật để tham chiếu đến tham số mới thay vì bước Source đã bị xóa. Cần cập nhật dòng đó để nó trỏ đến tham số mới.

    Sửa đổi:

    #”Grouped Rows” = Table.Group(Source, {“Customer”}, {{“Customer Total”, each List.Sum([Value]), type number}}),

    Thành:

    #”Grouped Rows” = Table.Group(sourceTable, {“Customer”}, {{“Customer Total”, each List.Sum([Value]), type number}}),

    Giờ đây, cần thay thế tên Customer bằng đối số customerName đã được khai báo: Thay đổi như sau:

    #”Filtered Rows” = Table.SelectRows(#”Grouped Rows”, each ([Customer] = “ABC Discounts”))

    Thành:

    #”Filtered Rows” = Table.SelectRows(#”Grouped Rows”, each ([Customer] = customerName))

    Hiện tại, chúng ta có thể sử dụng truy vấn trong trạng thái hiện tại. Nếu muốn, nó sẽ trả lại một Table chứa một giá trị duy nhất, chứ không phải một giá trị đơn. Tuy nhiên, chúng ta có thể thêm một bước nữa để chỉ lấy về giá trị đó.

    Thay đổi mã M như sau:

    #”Filtered Rows” = Table.SelectRows(#”Grouped Rows”, each

    (customerName là [Customer])

    trong

    #”Các cột đã bị loại bỏ”

    Sửa đổi như sau:

    #”Hàng đã được lọc” = Table.SelectRows(#”Nhóm hàng”, mỗi ([Customer] là customerName)),

    Tổng khách hàng = Record.Field(#”Hàng đã được lọc”{0}, “Tổng Khách Hàng”)

    trong

    Tổng khách hàng

    Record.Field là hàm Power Query trả kết quả là giá trị duy nhất từ cột:

    • #”Hàng đã được lọc”{0} biểu thị cho dòng đầu tiên trong bảng sau khi đã thực hiện bước lọc hàng
    • ”Tổng Khách Hàng”ghi tên cột cần truy xuất

    Chú ý: có thấy dấu phẩy thêm vào ở cuối dòng của#”Các cột đã bị loại bỏ” không? Nó là yếu tố bắt buộc, do đó, đảm bảo bạn đừng quên nếu không nó đã bị xóa.

    Đây là mã M được hoàn thiện:

    (sourceTable là bảng, customerName là văn bản) =>

    let

    #”Nhóm hàng” = Table.Group(sourceTable, {“Khách Hàng”}, {{“Tổng Khách Hàng”, mỗi List.Sum([Giá Trị]), loại số}}),

    #”Hàng đã được lọc” = Table.SelectRows(#”Nhóm hàng”, mỗi ([Customer] là customerName)),

    Tổng khách hàng = Record.Field(#”Hàng đã được lọc”{0}, “Tổng Khách Hàng”)

    trong

    Tổng khách hàng

    Nhấn vào Hoàn thành để đóng cửa sổ Trình soạn thảo Nâng cao.

    Kiểm tra

    Kiểm thử cho hàm. Chọn Data query làm sourceTable và gõ vào Tên Khách Hàng. Nhấn chọn Gọi Hàm.

    Kết quả trả về là

    Cực kỳ ngạc nhiên

    Áp dụng hàm

    Chúng tôi cần làm bây giờ là tận dụng hàm đã tùy biến. Mở truy vấn dữ liệu và thêm Cột Tùy Biến. Nhập dữ liệu sau đây cho công thức:

    • Tham chiếu đến tên của bước (nghĩa là bảng sẽ được sử dụng ngay sau khi hoàn thành bước “Nguồn”)
    • [Customer] chỉ cột khách hàng

    Nhấn OK để xác nhận hàm

    Cuối cùng, hãy thực hiện các bước chuyển đổi dưới đây:

    • Chuyển đổi cột mới thành loại dữ liệu phần trăm
    • Sắp xếp lại theo cột Khách Hàng
    • Xóa cột Người Bán Ra

    Giao diện Xem trước giờ sẽ hiển thị tỷ lệ % cho mỗi giao dịch của khách hàng như sau.

    Bước tiếp theo

    Nếu bạn muốn nâng cao hiệu suất, sao không thử chỉnh sửa hàm để nó có thể vận hành trên bất kỳ cột nào chứ không riêng gì cột có tên “Khách Hàng”.

    Kết luận

    Bài viết này đã giới thiệu về cách tạo ra các hàm tùy chỉnh cơ bản và dựa vào chuyển đổi. Cách thức này có thể áp dụng mọi lúc mỗi khi chúng ta cần giá trị hoặc một chuỗi giá trị không đồng hàng. Dưới đây là một số ví dụ điển hình:

    • Tổng cộng đang chạy
    • Tỷ lệ % tổng thể
    • Giá trị của hàng trước hoặc hàng sau

    Việc thao tác với mã M có thể khá phức tạp và dễ mắc lỗi, đặc biệt nếu như chúng ta chưa có nhiều kinh nghiệm. Do đó, giống như nhiều tính năng khác của Power Query, điều tốt nhất là nên thực hành nhiều.

    Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop