[Power Query Tutorial] Cách sử dụng Fill trong Power BI

Trong quá trình xử lý dữ liệu bằng Power BI, người làm phân tích thường xuyên gặp phải tình trạng dữ liệu gộp ô từ Excel hoặc báo cáo dạng phân cấp bị trống dòng. Để giải quyết triệt để vấn đề này, chức năng Fill trong Power BI là công cụ không thể thiếu. Bài viết này, Starttrain sẽ giúp bạn hiểu rõ bản chất, cách thao tác và vai trò của chức năng Fill trong Power BI qua ví dụ minh họa chi tiết.

Chức năng Fill trong Power BI gồm có gì

Chức năng Fill trong Power BI là công cụ nằm trong giao diện Power Query Editor, cho phép tự động điền giá trị vào các ô bị trống (mang giá trị null) dựa trên giá trị của ô liền kề phía trên hoặc phía dưới.

Chức năng này gồm hai chế độ xử lý chính:

• Fill Down (Điền xuống dưới): Hệ thống sẽ lấy giá trị của ô hợp lệ gần nhất ở phía trên và sao chép xuống tất cả các ô null phía dưới cho đến khi gặp một giá trị mới.
• Fill Up (Điền lên trên): Hệ thống sẽ lấy giá trị của ô hợp lệ gần nhất ở phía dưới và sao chép ngược lên tất cả các ô null phía trên cho đến khi gặp một giá trị mới.

Lưu ý: Chức năng Fill trong Power BI chỉ hoạt động trên các ô chứa giá trị null thực sự. Nếu ô trống chứa chuỗi ký tự rỗng (blank string “”), bạn cần chuyển đổi các ô đó thành null trước khi thực hiện thao tác fill.

Minh họa chức năng Fill trong Power BI

Để hiểu rõ hơn về cơ chế vận hành của chức năng Fill trong Power BI, hãy cùng quan sát qua các hình ảnh minh họa thực tế từ giao diện Power Query Editor.

Thao tác Fill Down

Trong giao diện Power Query Editor, tại cột KPI by App & Division, dữ liệu ban đầu chỉ xuất hiện ở một số dòng đại diện như dòng 6 (Productivity Apps), dòng 22 (Game Apps), dòng 35 (Utility Apps). Tất cả các ô còn lại giữa các nhóm này đều có giá trị null. Để thực hiện fill down, người dùng chọn cột KPI by App & Division, trên thanh Ribbon chuyển sang thẻ Transform, tìm đến nhóm công cụ Any Column, nhấn chọn biểu tượng Fill và chọn menu xổ xuống là Down.

Sau khi chọn Down, thanh công thức hiển thị hàm M: Table.FillDown(#”Changed Type”, {“KPI by App & Division”}). Trong danh sách Applied Steps bên phải xuất hiện bước Filled Down. Quan sát bảng dữ liệu: Toàn bộ ô null từ dòng 6 đến dòng 21 đã được điền tự động bằng chuỗi ký tự Productivity Apps. Tương tự, từ dòng 22 trở đi, giá trị Game Apps đã được điền đầy đủ cho các ô trống phía dưới trước khi gặp phân nhóm tiếp theo.

Thao tác Fill Up

Khi dữ liệu mẫu nằm ở phía dưới của danh mục và các dòng phía trên bị trống (null), ta cần dùng thao tác fill up. Trên giao diện Power Query Editor, người dùng click chọn cột cần xử lý (KPI by App & Division), chọn thẻ Transform > bấm chọn Fill > chọn Up.

Hệ thống thực thi lệnh với mã M tương ứng là Table.FillUp(#”Changed Type”, {“KPI by App & Division”}). Lịch sử thao tác ghi nhận bước Filled Up. Trên bảng kết quả, giá trị Game Apps tại dòng 22 đã được sao chép ngược lên phía trên để thay thế các ô null từ dòng 14 đến dòng 21. Tương tự, giá trị Utility Apps ở dòng 35 được điền ngược lên cho các ô trống phía trên đến tận dòng 23.

Vai trò của chức năng Fill trong Power BI

• Khắc phục lỗi dữ liệu gộp ô:
  • Khi nhập các file Excel chứa các ô Merged Cells vào Power Query, chỉ có duy nhất ô góc trên bên trái giữ lại giá trị, tất cả các ô gộp còn lại sẽ tự động quy đổi thành giá trị null.
  • Nếu giữ nguyên cấu trúc này, các dòng chứa null sẽ bị coi là không thuộc về bất kỳ danh mục nào. Chức năng fill giúp biến các báo cáo dạng phân cấp đa tầng (Hierarchical/Cross-tab) thành mô hình dữ liệu dạng bảng phẳng (Flat Table / Tidy Data), trong đó mỗi dòng là một bản ghi độc lập và đầy đủ thông tin ngữ cảnh.
• Đảm bảo tính toàn vẹn dữ liệu:
  • Trong Power BI, để thiết lập mối quan hệ (Relationships) chuẩn theo mô hình ngôi sao (Star Schema) giữa bảng chiều (Dimension Table) và bảng thực thể (Fact Table), các cột liên kết (Key Columns) không được phép chứa khoảng trống vô nghĩa.
  • Nếu cột danh mục chứa nhiều ô null, các phép nối bảng (Merge Queries), nhóm dữ liệu (Group By) hoặc liên kết bảng sẽ bỏ sót dữ liệu, dẫn đến tình trạng thất thoát số liệu doanh thu, sản lượng hoặc tạo ra các nhóm Blank vô danh trên biểu đồ báo cáo.
• Đơn giản hóa việc viết hàm DAX:
  • Khi dữ liệu dòng/cột được lấp đầy chuẩn xác, bạn không cần phải viết thêm các câu lệnh DAX phức tạp để xử lý ngoại lệ cho các ô null (như dùng hàm ISBLANK, COALESCE hay tạo cột phụ logic).
  • Dữ liệu sạch giúp bộ diễn giải DAX Engine vận hành nhanh hơn, các hàm tính toán theo ngữ cảnh bộ lọc (Filter Context) như CALCULATE, SUMX, ALL hoạt động mượt mà và trả về kết quả chính xác 100%.
• Tự động hóa quy trình ETL:
  • Tất cả thao tác Fill trong Power Query Editor đều được ghi lại dưới dạng một bước xử lý (Applied Step) bằng mã ngôn ngữ M.
  • Điều này có nghĩa là khi dữ liệu nguồn cập nhật hàng tháng hay hàng ngày (Data Refresh trên Power BI Service), hệ thống sẽ tự động thực hiện lại quy trình fill down/fill up cho dữ liệu mới mà bạn không cần phải tốn thời gian thao tác hay chỉnh sửa thủ công bằng tay.

Mã M đằng sau chức năng Fill trong Power BI

Khi thao tác trên giao diện người dùng của Power Query Editor, mọi hành động click chuột đều được Power BI tự động biên dịch thành ngôn ngữ M (Power Query M Formula Language). Việc hiểu rõ bản chất mã M đằng sau chức năng fill trong power bi sẽ giúp bạn chủ động tùy biến, viết script nâng cao hoặc tối ưu hóa hiệu năng xử lý dữ liệu.

Cú pháp hàm Table.FillDown

Khi bạn chọn thao tác Fill Down, Power Query sẽ sinh ra hàm Table.FillDown.

Cú pháp chuẩn của hàm: Table.FillDown(table as table, columns as list) as table

Trong đó:

• table: Bảng dữ liệu cần xử lý (thường là bước xử lý liền trước đó trong danh sách Applied Steps).
• columns: Danh sách tên các cột (đặt trong dấu ngoặc nhọn {}) mà bạn muốn áp dụng điền dữ liệu từ trên xuống.

Ví dụ thực tế từ thanh công thức Power Query: = Table.FillDown(#”Changed Type”, {“KPI by App & Division”})

Ở đây, Power BI lấy kết quả của bước #”Changed Type” và áp dụng quy tắc điền xuống đối với cột KPI by App & Division.

Cú pháp hàm Table.FillUp

Tương tự, khi chọn Fill Up, hệ thống sẽ gọi hàm Table.FillUp.

Cú pháp chuẩn của hàm: Table.FillUp(table as table, columns as list) as table

Ví dụ thực tế: = Table.FillUp(#”Changed Type”, {“KPI by App & Division”})

Điền dữ liệu cho nhiều cột đồng thời bằng mã M

Điểm ưu việt khi hiểu được mã M đằng sau chức năng Fill trong Power BI là bạn có thể mở rộng danh sách cột cần fill trực tiếp trên thanh công thức mà không cần thực hiện lại nhiều thao tác click chuột.

Ví dụ, nếu bạn muốn fill down đồng thời 3 cột Category, SubCategory và Region, bạn chỉ cần sửa mã M trên thanh công thức thành: = Table.FillDown(#”Changed Type”, {“Category”, “SubCategory”, “Region”})

Cách làm này giúp mã M gọn gàng hơn, giảm bớt số lượng bước (Applied Steps) không cần thiết và tối ưu hóa thời gian tính toán của Power Query.

Những lưu ý khi sử dụng chức năng Fill trong Power BI

Kiểm soát thứ tự dòng trước khi Fill

Chức năng Fill hoạt động dựa trên vị trí tương quan tuyệt đối của các dòng theo chiều dọc từ trên xuống hoặc từ dưới lên. Điều này có nghĩa là thứ tự dòng tại thời điểm thao tác quyết định toàn bộ tính đúng đắn của kết quả.

• Rủi ro: Nếu bạn lỡ tay thực hiện sắp xếp tăng/giảm (Sort Ascending/Descending) trên bất kỳ cột nào trước khi thực hiện Fill Down, vị trí của các ô chứa giá trị gốc và các ô null sẽ bị xáo trộn. Khi đó, thao tác Fill Down sẽ lấy sai giá trị đại diện và điền nhầm dữ liệu sang các danh mục khác.
• Giải pháp chuẩn: Luôn giữ nguyên thứ tự dữ liệu gốc từ nguồn. Nếu bắt buộc phải thực hiện các thao tác biến đổi làm ảnh hưởng thứ tự dòng, hãy tạo một cột chỉ số thứ tự (Index Column) ngay tại bước đầu tiên của quy trình ETL. Sau khi hoàn thành thao tác Fill, bạn có thể dựa vào cột Index này để sắp xếp lại đúng thứ tự nguyên bản.

Phân biệt giữa Null, Blank và khoảng trắng

Power Query được lập trình để chỉ ghi nhận và xử lý trên các ô có giá trị null chuẩn hệ thống. Các định dạng trống khác sẽ bị tính năng Fill bỏ qua hoàn toàn.

• Hiện tượng lỗi: Bạn bấm Fill Down nhưng các dòng phía dưới vẫn trống không, không có dữ liệu nào được điền vào.
• Nguyên nhân: Các ô tưởng chừng như “trống” đó thực chất đang chứa chuỗi ký tự rỗng “”, ký tự xuống dòng \n, hoặc các khoảng trắng ẩn (spaces) do quá trình nhập liệu hoặc xuất file Excel bị lỗi. Power Query xem các ký tự này là dữ liệu hợp lệ và giữ nguyên chúng thay vì ghi đè bằng giá trị từ ô phía trên.
• Trước khi Fill, bạn cần chuẩn hóa cột bằng cách:
  • Chọn cột > Thẻ Transform > Format > Trim (để xóa khoảng trắng dư thừa ở đầu/cuối).
  • Chọn cột > Thẻ Transform > Replace Values. Tại mục Value To Find, nhập chuỗi rỗng hoặc bấm phím Spacebar, mục Replace With để trống (hoặc nhập null tùy theo phiên bản).
  • Hoặc bạn có thể click chuột phải vào ô trống tiêu biểu > chọn Replace Values > thay thế bằng null. Khi các ô đã chuyển sang chữ null nghiêng màu xám, thao tác Fill mới hoạt động chính xác.

Thứ tự xử lý khi dữ liệu có nhiều cấp danh mục

Nếu bảng dữ liệu thô chứa nhiều danh mục phân cấp (ví dụ: Cấp 1: Miền -> Cấp 2: Tỉnh/Thành -> Cấp 3: Đại lý), các ô null sẽ xuất hiện đan xen trên nhiều cột khác nhau.

• Quy tắc thực hiện: Bạn phải luôn luôn thực hiện thao tác Fill Down/Fill Up theo thứ tự từ cấp danh mục cao nhất (lớn nhất) đến các cấp danh mục thấp hơn (nhỏ hơn).
• Lý do: Việc điền xong dữ liệu cho danh mục cấp cao hơn sẽ tạo nền tảng ngữ cảnh chuẩn xác cho các danh mục cấp con. Nếu thực hiện ngược lại, bạn dễ dẫn đến tình trạng gán nhầm danh mục con vào sai phân nhóm mẹ.

Tổng kết

Chức năng Fill trong Power BI là một công cụ đơn giản nhưng cực kỳ mạnh mẽ trong Power Query Editor, đóng vai trò bản đề giúp biến đổi các cấu trúc dữ liệu gộp ô, dữ liệu phân cấp phức tạp thành dạng bảng phẳng chuẩn hóa. Việc nắm vững cơ chế vận hành của Fill, hiểu rõ bản chất mã M đằng sau cũng như áp dụng các lưu ý về làm sạch ô null và kiểm soát thứ tự dòng sẽ giúp nhà phân tích xây dựng mô hình dữ liệu chính xác, tối ưu hiệu năng tính toán và tự động hóa toàn bộ quy trình báo cáo trên Power BI.