M Language Nâng Cao: Custom Functions, Error Handling Và Recursion Trong Power Query

1. M Language: Ngôn Ngữ Ẩn Sau Power Query #

Mỗi thao tác UI trong Power Query đều generate M code phía sau. Khi GUI không đủ, bạn cần viết M trực tiếp. M Language là functional programming language — mọi thứ là expression, không có statements. Bài viết hướng dẫn 3 kỹ thuật nâng cao: Custom Functions, Error Handling, và Recursive.

Nơi viết M: Advanced Editor (Ctrl+Shift+M trong Power Query), Formula Bar, hoặc Custom Column dialog. M case-sensitive — Table khác table. Mỗi query là 1 let...in expression.

2. Custom Functions — Tái Sử Dụng Logic #

Custom Function trong M giống hàm trong Python/JS: nhận parameters, trả về kết quả. Rất hữu ích khi áp dụng cùng transformation cho nhiều queries hoặc nhiều files.

// Custom function: Clean Vietnamese phone number
let
    CleanPhone = (rawPhone as text) as text =>
    let
        trimmed = Text.Trim(rawPhone),
        cleaned = Text.Remove(trimmed, {" ", "-", ".", "(", ")"}),
        normalized = if Text.StartsWith(cleaned, "+84") 
            then "0" & Text.Middle(cleaned, 3)
            else cleaned
    in
        normalized
in
    CleanPhone

Invoke function: Add Column → Invoke Custom Function → chọn function và column input. Function sẽ chạy cho mỗi dòng trong bảng.

3. Error Handling — try...otherwise #

M dùng try...otherwise để bắt lỗi. Không có try-catch như Python — thay vào đó try trả về record {HasError, Value, Error}. Dùng otherwise để set default value khi lỗi.

// Cách 1: try...otherwise (default value)
let
    result = try Number.FromText("abc") otherwise 0
in
    result  // 0

// Cách 2: try record (kiểm tra chi tiết)
let
    attempt = try Number.FromText(inputText),
    output = if attempt[HasError] 
        then "Error: " & attempt[Error][Message]
        else Text.From(attempt[Value])
in
    output

4. Table.Combine — Gộp Nhiều Bảng Programmatically #

Table.Combine gộp list of tables thành 1 table (giống UNION ALL). Kết hợp với Folder.Files() để auto-combine tất cả Excel/CSV files trong folder.

// Combine tất cả CSV trong folder
let
    Source = Folder.Files("C:\Data\Monthly"),
    FilteredCSV = Table.SelectRows(Source, each [Extension] = ".csv"),
    AddContent = Table.AddColumn(FilteredCSV, "Data", each 
        Csv.Document([Content], [Delimiter=",", Encoding=65001])
    ),
    Combined = Table.Combine(AddContent[Data])
in
    Combined

5. Recursive Functions — Xử Lý Đệ Quy #

M hỗ trợ recursion qua @self reference hoặc named function. Ứng dụng: API pagination (gọi liên tục đến hết pages), xử lý nested JSON, flatten hierarchical data.

// Recursive: fetch paginated API
let
    GetAllPages = (url as text) as list =>
    let
        response = Json.Document(Web.Contents(url)),
        currentData = response[results],
        nextUrl = try response[next] otherwise null,
        allData = if nextUrl <> null 
            then currentData & @GetAllPages(nextUrl)
            else currentData
    in
        allData,
    
    AllResults = GetAllPages("https://api.example.com/data?page=1")
in
    Table.FromList(AllResults)

6. Record Và List Operations #

M có 3 data structures: Table, Record (= object/dictionary), List (= array). Record.FieldValues() chuyển record → list. List.Transform() map function trên list. Table.FromRecords() chuyển list of records → table. Hiểu 3 cấu trúc này = viết M linh hoạt.

7. Performance Tips #

1) Table.Buffer() cache bảng trong RAM — tránh re-evaluate. 2) Dùng List.Generate thay recursion khi có thể (nhanh hơn). 3) Filter TRƯỚC join/combine để giảm data. 4) Table.SelectColumns sớm — bỏ cột không cần. 5) Native query folding: kiểm tra View Native Query trong Applied Steps.

8. Kết Luận #

M Language nâng cao mở ra sức mạnh thực sự của Power Query. Custom Functions tái sử dụng logic, try...otherwise xử lý data dirty, Table.Combine + Folder.Files auto-merge files, Recursive xử lý API pagination. Invest time học M = ROI rất cao cho ETL workflow.