Power BI 2 — Power Query & ngôn ngữ M (ETL)

Trước khi có mô hình, phải có dữ liệu sạch

Trong bài Tổng quan Power BI chúng ta đã thấy Power BI gồm nhiều tầng: lấy dữ liệu, biến đổi, mô hình hoá, tính toán và trình bày. Bài này đi sâu vào tầng chuẩn bị dữ liệu — nơi dữ liệu thô từ hàng chục nguồn khác nhau (CSV, Excel, SQL Server, Oracle, API, SharePoint...) được làm sạch, chuẩn hoá và định hình lại trước khi nạp vào mô hình.

Công cụ làm việc này trong Power BI (và cả Excel) là Power Query, còn được biết đến dưới tên Get & Transform trên thanh ribbon. Đây là một công cụ ETL (Extract - Transform - Load) trực quan: bạn kéo, bấm, chọn menu, và Power Query lo phần biến đổi. Điều quan trọng cần hiểu ngay: mỗi thao tác bạn thực hiện đều sinh ra một dòng code phía sau, viết bằng ngôn ngữ M. Giao diện trực quan chỉ là lớp vỏ; bản chất luôn là code M.

Một nguyên tắc nền tảng: Power Query không sửa dữ liệu nguồn. Nó ghi lại một chuỗi các bước biến đổi và áp dụng chuỗi này lại từ đầu mỗi lần làm mới (refresh). Nguồn gốc luôn được giữ nguyên; bạn chỉ định nghĩa "công thức" để đi từ dữ liệu thô đến dữ liệu sạch.

Applied Steps — chuỗi bước biến đổi

Khi bạn mở Power Query Editor và làm việc với một bảng (query), bên phải màn hình có bảng Applied Steps (Các bước đã áp dụng). Mỗi lần bạn đổi kiểu một cột, lọc một dòng, tách một cột... một bước mới được thêm vào danh sách này theo thứ tự trên xuống.

Đây là điểm rất khác so với cách nghĩ SQL. Trong SQL bạn viết một câu lệnh mô tả kết quả cuối cùng. Trong Power Query bạn xây một pipeline tuần tự: bước sau nhận đầu ra của bước trước làm đầu vào. Bạn có thể bấm vào bất kỳ bước nào để "tua" về trạng thái dữ liệu tại thời điểm đó — cực kỳ tiện để gỡ lỗi.

Vì mỗi bước có tên, một thói quen tốt là đổi tên bước cho có nghĩa (ví dụ đổi Changed Type thành Đổi kiểu cột ngày). Sau vài tháng quay lại, một pipeline với các bước được đặt tên rõ ràng đọc như một tài liệu; còn pipeline mặc định Changed Type1, Filtered Rows2... thì gần như không thể bảo trì.

Ngôn ngữ M — nền tảng phía sau

M (tên chính thức: Power Query M formula language) là ngôn ngữ khai báo, thiên hàm (functional) đứng sau mọi thao tác. Vài đặc điểm cần nhớ:

• Phân biệt hoa thường (case-sensitive): Table.SelectRows khác table.selectrows. Đây là nguồn lỗi phổ biến với người quen SQL.
• Cấu trúc chủ đạo là biểu thức let ... in: phần let khai báo một loạt bước (biến), phần in chỉ ra biểu thức trả về.
• Có hệ kiểu giá trị phong phú: table (bảng), record (bản ghi — tập cặp khoá/giá trị), list (danh sách), cùng các kiểu vô hướng number, text, date, logical...

Mỗi query trong Applied Steps chính là một khối let ... in. Mỗi dòng trong let tương ứng một bước; tên bước là tên biến. Ví dụ minh hoạ một query đọc bảng SQL và làm sạch:

// (minh hoạ) Làm sạch bảng giao dịch
let
    Nguon = Sql.Database("srv-dwh", "NCB_DWH"),
    BangGD = Nguon{[Schema="dbo", Item="GiaoDich"]}[Data],
    DoiKieu = Table.TransformColumnTypes(
        BangGD,
        {{"NgayGD", type date}, {"SoTien", type number}}
    ),
    LocHopLe = Table.SelectRows(DoiKieu, each [SoTien] > 0),
    BoCotThua = Table.RemoveColumns(LocHopLe, {"GhiChuNoiBo"})
in
    BoCotThua

Chú ý cách BangGD dùng đầu ra của Nguon, DoiKieu dùng BangGD... — đây chính là chuỗi Applied Steps thể hiện dưới dạng code. Bạn hiếm khi phải viết M bằng tay; nhưng khi cần chỉnh những thứ giao diện không làm được, Advanced Editor cho phép sửa trực tiếp khối let ... in.

Một record viết bằng cặp ngoặc nhọn [...], ví dụ [Ten="An", Tuoi=30]. Một list viết bằng ngoặc nhọn {...}, ví dụ {1, 2, 3}. Truy cập phần tử list bằng {n} (0-based), truy cập trường record bằng [Ten]. Nắm ba kiểu table/record/list là đủ để đọc hầu hết code M sinh tự động.

Các phép biến đổi thường dùng

Đây là "bộ đồ nghề" hằng ngày trong Power Query. Đa số nằm sẵn trên ribbon, chỉ cần bấm chuột:

• Đổi kiểu dữ liệu (Change Type): quan trọng bậc nhất. Cột ngày phải là kiểu date, cột tiền phải là number. Sai kiểu ở đây gây lỗi lan xuống cả mô hình DAX.
• Tách / gộp cột (Split / Merge Columns): tách "Hà Nội - CN Ba Đình" thành tỉnh và chi nhánh; hoặc gộp họ và tên.
• Unpivot / Pivot: chuyển dữ liệu dạng "rộng" (mỗi tháng một cột) sang dạng "dài" (một cột tháng, một cột giá trị) — Unpivot là thao tác cực kỳ hữu ích để chuẩn hoá báo cáo Excel sang dạng bảng chuẩn cho BI. Pivot làm điều ngược lại.
• Group By (Nhóm): tổng hợp theo khoá — ví dụ tổng số tiền theo chi nhánh.
• Merge (join) và Append (union): Merge = kết hợp hai bảng theo cột khoá (giống JOIN của SQL), có đủ Inner / Left / Right / Full / Anti. Append = xếp chồng các bảng có cùng cấu trúc (giống UNION).
• Replace Values (thay giá trị) và Fill Down / Up (điền giá trị từ ô trên/dưới xuống ô trống — rất hợp với bảng Excel có ô gộp).
• Custom Column (thêm cột tuỳ chỉnh): viết một biểu thức M để tính cột mới, ví dụ phân loại CASA/tiết kiệm theo kỳ hạn.
• Xử lý lỗi: Power Query đánh dấu ô lỗi bằng giá trị Error. Bạn có thể Remove Errors (bỏ dòng lỗi), Replace Errors (thay bằng giá trị mặc định), hoặc bọc bằng try ... otherwise trong M để bắt lỗi từng ô một cách chủ động.

// (minh hoạ) Cột tuỳ chỉnh + bắt lỗi
AddLoai = Table.AddColumn(
    BoCotThua, "LoaiTK",
    each if [KyHan] = 0 then "CASA" else "Tiết kiệm", type text
),
AnToanChia = Table.AddColumn(
    AddLoai, "TyLe",
    each try [SoTien] / [TongNhom] otherwise 0, type number
)

Query folding — trái tim của hiệu năng

Đây là khái niệm quan trọng nhất của toàn bài, và cũng là thứ hay bị bỏ qua nhất.

Query folding là khả năng Power Query dịch ngược các bước biến đổi thành câu lệnh gốc của nguồn (thường là SQL) và đẩy công việc về cho nguồn thực hiện, thay vì tự kéo toàn bộ dữ liệu về rồi mới xử lý trên máy. Nếu bạn lọc [SoTien] > 0 trên một bảng SQL và bước này fold được, Power Query sẽ gửi xuống server một câu WHERE [SoTien] > 0 — server trả về đúng phần cần thiết.

Vì sao điều này quan trọng đến vậy:

• Ít dữ liệu truyền qua mạng: nguồn lọc/tổng hợp trước, chỉ gửi kết quả đã gọn.
• Tận dụng sức mạnh của database: index, engine tối ưu của SQL Server/Oracle làm nhanh hơn nhiều so với engine M chạy trên máy client.
• Refresh nhanh hơn nhiều lần với bảng lớn — khác biệt có thể là vài giây so với vài chục phút.

Thao tác nào phá folding

Một khi một bước không fold được, mọi bước sau nó cũng mất khả năng fold — dù bản thân chúng vốn có thể fold. Vì vậy thứ tự các bước rất quan trọng. Các thao tác thường phá folding:

• Kết hợp dữ liệu từ hai nguồn khác nhau (ví dụ merge bảng SQL với file Excel): hai nguồn không chung một engine nên Power Query buộc phải kéo hết về bộ nhớ.
• Cột tuỳ chỉnh với logic phức tạp hoặc dùng hàm M không có tương đương SQL (ví dụ Text.Trim, Text.Clean, các hàm ngày đặc thù, if/then/else lồng nhau phức tạp).
• Pivot / Unpivot trong nhiều trường hợp.
• Một số nguồn (như file phẳng CSV/Excel) vốn không hỗ trợ folding vì không có "engine truy vấn" để đẩy việc xuống.

Cách kiểm tra folding

Bấm chuột phải vào một bước trong Applied Steps và chọn View Native Query (Xem truy vấn gốc). Nếu mục này bật và hiện ra câu SQL tương ứng, nghĩa là bước đó (và toàn bộ chuỗi tới đó) đang fold. Nếu mục bị mờ (disabled), folding đã bị phá tại hoặc trước bước đó. Ngoài ra Power Query còn có step folding indicators (biểu tượng cạnh mỗi bước) giúp nhận biết trực quan bước nào còn fold.

Query folding đặc biệt sống còn với chế độ DirectQuery — nơi Power BI không nhập dữ liệu mà truy vấn trực tiếp nguồn theo thời gian thực. Với DirectQuery, nếu một bước không fold được thì nó đơn giản là không hoạt động hoặc gây lỗi/chậm nghiêm trọng. Chủ đề này được đào sâu ở bài Chế độ lưu trữ & DirectQuery.

Parameters & Functions — tái sử dụng và linh hoạt

Parameters (tham số)

Parameter là một giá trị đặt tên, có thể thay đổi mà không phải sửa từng query. Ví dụ điển hình: tham số hoá tên server, tên database, hay đường dẫn thư mục. Khi chuyển từ môi trường DEV sang PROD, bạn chỉ đổi một tham số thay vì sửa hàng chục query. Tham số cũng dùng để tạo báo cáo linh hoạt (ví dụ chọn năm cần phân tích).

// (minh hoạ) dùng parameter cho nguồn
Nguon = Sql.Database(pServerName, pDatabaseName)

Custom functions (hàm tuỳ chỉnh)

Một custom function trong M là một query nhận tham số đầu vào và trả về kết quả — cú pháp (thamso) => let ... in .... Sức mạnh của nó là lặp một logic biến đổi trên nhiều đối tượng: nhiều file, nhiều bảng, nhiều sheet. Bạn định nghĩa cách xử lý một file, rồi gọi hàm đó cho tất cả file.

// (minh hoạ) hàm làm sạch một file
(noiDung as binary) as table =>
let
    Doc = Csv.Document(noiDung, [Delimiter=",", Encoding=65001]),
    Header = Table.PromoteHeaders(Doc),
    DoiKieu = Table.TransformColumnTypes(Header, {{"Ngay", type date}})
in
    DoiKieu

Dataflow — ETL tái sử dụng trên Service

Mọi thứ ở trên chạy trong Power BI Desktop, gắn liền với một file .pbix. Nhưng nếu năm báo cáo khác nhau cùng cần bảng "Danh mục khách hàng đã làm sạch" thì sao? Sao chép logic Power Query vào từng file là công thức cho thảm hoạ bảo trì.

Dataflow giải quyết đúng vấn đề này. Đó là Power Query chạy online trên Power BI Service (đám mây), tách rời khỏi dataset. Bạn định nghĩa logic ETL một lần trên trình duyệt (cùng trải nghiệm Power Query quen thuộc), dataflow chạy làm mới theo lịch và ghi kết quả vào storage (dạng Common Data Model/Delta). Nhiều report và dataset khác nhau sau đó kết nối tới dataflow như một nguồn dùng chung — logic làm sạch được viết một lần, dùng lại nhiều nơi.

Dataflow Gen2 là thế hệ mới trong Microsoft Fabric, xây trên cùng engine Power Query nhưng có nhiều cải tiến:

• Chọn đích ghi (output destination) linh hoạt: ghi kết quả vào Lakehouse, Warehouse, SQL Database... trong Fabric — điều Gen1 không có.
• Hiệu năng tốt hơn và tính năng Fast Copy để nạp nhanh khối lượng lớn.
• Tự động lưu tiến trình khi soạn thảo.
• Gen2 cần Fabric capacity (hoặc Fabric trial / Power BI Premium capacity) để chạy.

Nói ngắn gọn: Desktop hợp cho ETL của một báo cáo; Dataflow / Gen2 hợp cho ETL dùng chung ở quy mô tổ chức.

Use case thực tế

1. Gộp nhiều file CSV bằng custom function

Một tình huống rất phổ biến ở ngân hàng: mỗi chi nhánh xuất một file CSV giao dịch hằng ngày vào một thư mục chung, hàng trăm file cùng cấu trúc.

Cách làm gọn gàng:

1. Dùng nguồn Folder trỏ vào thư mục → Power Query liệt kê tất cả file thành một bảng (mỗi dòng một file).
2. Viết một custom function xử lý một file (promote header, đổi kiểu, lọc rác) — đúng như hàm minh hoạ ở trên.
3. Thêm một Invoke Custom Function để gọi hàm đó cho cột nội dung của mọi file, rồi Expand để bung tất cả thành một bảng duy nhất.

Kết quả: thêm hay bớt file trong thư mục không cần sửa gì — cứ refresh là pipeline tự áp dụng cho toàn bộ. Đây là mẫu "combine files" kinh điển và đáng đầu tư học kỹ.

2. Giữ query folding cho bảng lớn

Với bảng GiaoDich 200 triệu dòng trên SQL Server, khác biệt giữa fold và không fold là refresh vài phút so với refresh cả tiếng và tràn bộ nhớ.

Nguyên tắc thực chiến:

• Lọc và bỏ cột sớm nhất có thể, khi các bước còn fold được — để câu SQL sinh ra chỉ kéo về đúng phần cần.
• Đẩy các thao tác phá folding xuống cuối pipeline (custom column phức tạp, unpivot...), sau khi dữ liệu đã được thu nhỏ.
• Kiểm tra bằng View Native Query ở bước cuối cùng còn kỳ vọng fold — nếu nó mờ, tìm bước nào đã phá và cân nhắc sắp xếp lại.
• Nếu buộc phải viết SQL gốc, dùng cờ [EnableFolding = true] để cho phép các bước sau tiếp tục fold lên trên câu SQL đó.

Ghi nhớ

• Power Query = ETL trực quan (Get & Transform); mỗi thao tác là một Applied Step và sinh code M phía sau. Nguồn không bị sửa; bạn chỉ định nghĩa chuỗi bước.
• M khai báo, phân biệt hoa thường, xoay quanh let ... in; ba kiểu cốt lõi: table, record, list.
• Nắm bộ biến đổi cơ bản: đổi kiểu, tách/gộp, unpivot/pivot, group by, merge (join) & append (union), fill, custom column, xử lý lỗi bằng try ... otherwise.
• Query folding đẩy biến đổi về nguồn (thành SQL) → refresh nhanh. Một bước phá fold làm mọi bước sau mất fold. Kiểm bằng View Native Query. Sống còn với DirectQuery.
• Parameters để tham số hoá nguồn/đường dẫn; custom function để lặp logic trên nhiều file/bảng.
• Dataflow (và Gen2 trong Fabric) đưa ETL lên Service để tái sử dụng và ghi vào storage.
• Thói quen tốt: đặt tên bước rõ ràng, giữ folding càng lâu càng tốt, giảm cột/dòng sớm, tách staging query (làm sạch) khỏi presentation query (bảng cuối đưa vào model).

Đọc tiếp

• Power BI 1 — Tổng quan
• Power BI 3 — Mô hình dữ liệu
• Power BI 6 — Chế độ lưu trữ & DirectQuery