[Power Query Tutorial] Cách dùng Promoted – Demoted Headers

Khi làm sạch dữ liệu trong Excel hoặc Power BI bằng Power Query, một trong những bước đầu tiên và quan trọng nhất là thiết lập cấu trúc tiêu đề cột chính xác. Dữ liệu nguồn từ các file CSV, Excel hoặc PDF thường không có định dạng chuẩn mực ngay từ đầu. Đôi khi tiêu đề bị biến thành hàng dữ liệu đầu tiên, hoặc ngược lại, tên cột thực sự lại bị ẩn dưới dạng dữ liệu thô.

Để giải quyết vấn đề này, Power Query cung cấp hai tính năng cực kỳ mạnh mẽ: Promoted – Demoted Headers. Bài viết này sẽ giúp bạn hiểu rõ bản chất, cách hoạt động và hướng dẫn chi tiết từng bước thao tác thực tế.

Chức năng Promoted – Demoted Headers trong Power Query

Promoted Headers

Khi bạn kết nối với một nguồn dữ liệu mới, Power Query có thể không tự động nhận diện được đâu là dòng tiêu đề. Thay vào đó, nó gán các tên cột mặc định như Column1, Column2, Column3… và đẩy dòng chứa tiêu đề thực sự xuống làm hàng dữ liệu số 1.

• Chức năng: Đẩy hàng dữ liệu đầu tiên lên làm tên cột chính thức.
• Được dùng khi:
  • Khi mới import file CSV, Text hoặc Excel mà dòng đầu tiên chứa các tên cột (như “card_id”, “client_id”, “amount”, …).
  • Khi bạn vừa lọc bỏ các dòng tiêu đề phụ/dòng trống ở phía trên và muốn đưa dòng chứa tiêu đề chính xác hiện tại lên đầu.

Trước khi thực hiện Promoted Headers, bạn cũng nên kiểm tra các bước như Choose Columns trong Power BI, Remove column & other column hoặc Keep Row để chỉ giữ lại những dữ liệu cần thiết, giúp mô hình gọn nhẹ và dễ quản lý hơn.

Demoted Headers

Đây là tính năng đảo ngược hoàn toàn của Promoted Headers.

• Chức năng: Hạ các tên cột hiện tại xuống thành hàng dữ liệu thứ nhất, đồng thời tạo ra các tên cột mặc định mới dạng Column1, Column2, Column3, …
• Được dùng khi:
  • Xử lý cấu trúc bảng phức tạp: Khi file dữ liệu của bạn có nhiều dòng tiêu đề chồng chéo, bạn cần hạ tiêu đề xuống để gộp chúng lại trước khi nâng lên lần nữa.
  • Sửa sai: Khi Power Query tự động nhận diện sai dòng tiêu đề và bạn muốn đưa bảng dữ liệu về cấu trúc gốc để xử lý thủ công.
  • Xử lý dữ liệu dạng Pivot/Unpivot: Một số trường hợp cần biến tên cột thành dữ liệu dòng để thực hiện unpivot.

Minh họa chi tiết cách sử dụng Promoted – Demoted Headers

Trạng thái ban đầu: chưa nhận diện tiêu đề

Khi dữ liệu vừa được tải vào từ nguồn:

• Bạn có thể thấy tên các cột hiện tại đang là Column1, Column2, Column3, …, Column8.
• Hàng số 1 đang chứa các giá trị: card_id, client_id, amount, date, errors, id, mcc, merchant_city.
• Tại khung giao diện Applied Steps bên phải, chỉ đang có 2 bước gốc là Source và Navigation.
• Đây là cấu trúc chưa chuẩn vì tiêu đề cột thực sự đang bị hiểu lầm là một dòng dữ liệu thô.

Thao tác Promoted Headers

Để đưa dòng chứa các tên trường dữ liệu lên làm tiêu đề cột chính xác:

• Vào tab Transform.
• Tại nhóm công cụ Table, tìm và nhấp chọn nút Use First Row as Headers.
• Một menu nhỏ xổ xuống, chọn chức năng đầu tiên Use First Row as Headers.

Ngay sau khi click chọn:

• Dòng dữ liệu thô ban đầu đã biến mất. Thay vào đó, tên các cột giờ đây đã hiển thị đúng là: card_id, client_id, amount, date, errors, mcc, merchant_city, …
• Khung Applied Steps bên phải tự động ghi nhận thêm 2 bước mới:
  • Promoted Headers: Bước ghi lại việc thăng cấp tiêu đề.
  • Changed Type: Power Query tự động nhận diện lại kiểu dữ liệu của các cột sau khi có tiêu đề mới.

Thao tác Demoted Headers

Trong trường hợp bạn muốn hạ các tiêu đề cột này xuống lại làm hàng dữ liệu thô:

• Vào tab Transform.
• Tại nhóm công cụ Table, tìm và nhấp chọn nút Use First Row as Headers.
• Tại menu thả xuống, chọn chức năng thứ hai: Use Headers as First Row.

Hệ thống sẽ thực hiện hạ cấp ngay lập tức:

• Tên các cột quay trở lại trạng thái generic là Column1, Column2, Column3,…
• Toàn bộ tiêu đề cột cũ (card_id, client_id, amount,…) đã được hạ xuống và xếp ở vị trí hàng dữ liệu số 1 (Row 1).
• Khung Applied Steps bên phải xuất hiện thêm các bước: Demoted Headers và Changed Type1.
• Từ đây, bạn có thể dễ dàng thực hiện lọc bỏ, thay thế giá trị hoặc gộp dòng tiêu đề theo ý muốn.

Sau khi cấu trúc tiêu đề đã chính xác, bạn có thể tiếp tục sử dụng Group by trong Power Query để tổng hợp dữ liệu hoặc Sort trong Power BI nhằm sắp xếp dữ liệu trước khi phân tích.

Các lưu ý khi thao tác chức năng Promoted – Demoted Headers

Lỗi “Expression.Error: The column ‘…’ of the table wasn’t found”

Lỗi này xảy ra rất phổ biến khi bạn thay đổi tiêu đề cột nhưng các bước xử lý phía sau (như Changed Type, Reordered Columns, Rename Columns) vẫn đang tham chiếu cứng đến tên cột cũ.

Khắc phục: Kiểm tra lại thanh công thức của bước báo lỗi hoặc xóa bước Changed Type tự động đi. Chỉ thực hiện gán kiểu dữ liệu ở bước sau cùng sau khi cấu trúc tiêu đề đã được cố định hoàn toàn.

Trim & Clean trước khi nâng cấp tiêu đề

Đôi khi tiêu đề ở hàng đầu tiên chứa các khoảng trắng vô hình ở đầu hoặc cuối (ví dụ: ” card_id” thay vì “card_id”). Khi nâng lên làm tiêu đề cột, điều này sẽ gây khó khăn và phát sinh lỗi khi viết công thức M-Code hoặc DAX sau này.

Giải pháp:

• Giữ nguyên bảng ở trạng thái chưa nâng cấp (hoặc dùng Demote Headers để đưa về dạng thô).
• Transpose (Xoay dòng thành cột) bảng dữ liệu tạm thời.
• Áp dụng tính năng Trim và Clean lên cột tiêu đề thô này để xóa hết khoảng trắng ẩn và ký tự rác.
• Transpose ngược lại bảng dữ liệu về dạng cũ.
• Áp dụng lại Promoted Headers để nâng dòng tiêu đề đã được dọn sạch lên.

Xử lý tiêu đề động

Với các tệp báo cáo cập nhật hàng tháng, vị trí các cột có thể bị đảo lộn hoặc thay đổi nhẹ. Nếu bạn sử dụng mã M-code tham chiếu cứng tên cột, truy vấn sẽ bị lỗi khi cấu trúc file nguồn thay đổi.

Giải pháp: Sử dụng các hàm list như Table.ColumnNames kết hợp với List.Select để viết các câu lệnh lọc cột động, thay vì chỉ định tên cột cụ thể trong thanh công thức.

Mã M đằng sau chức năng Promoted – Demoted Headers

Đằng sau mỗi cú click chuột trên thanh công cụ Power Query là những hàm ngôn ngữ M hoạt động ngầm. Hiểu rõ mã M giúp bạn tùy biến quy trình một cách linh hoạt mà giao diện kéo thả thông thường không hỗ trợ.

Hàm Table.PromoteHeaders

Khi bạn chọn Use First Row as Headers, Power Query sẽ ghi lại bước này bằng hàm Table.PromoteHeaders.

Cú pháp chuẩn: Table.PromoteHeaders(table as table, optional options as nullable record) as table

• Tham số tùy biến nâng cao options: Mặc định, hàm này chỉ thăng cấp các giá trị dạng chuỗi văn bản (text) hoặc số (number). Tuy nhiên, bạn có thể truyền thêm tham số cấu hình:
  • PromoteAllScalars: Nếu thiết lập là true, toàn bộ các giá trị vô hướng ở hàng đầu tiên (bao gồm cả ngày tháng, giá trị logic True/False) đều sẽ được đẩy lên làm tiêu đề.
  • Culture: Xác định định dạng văn hóa của dữ liệu khi chuyển đổi.

Hàm Table.DemoteHeaders

Khi bạn thực hiện hạ cấp tiêu đề bằng tính năng Use Headers as First Row, hệ thống sẽ sử dụng hàm Table.DemoteHeaders.

Cú pháp chuẩn: Table.DemoteHeaders(table as table) as table

Bẫy tự động Changed Type của Power Query

Hãy lưu ý kỹ đoạn mã M được tạo ra tự động khi bạn thực hiện Promoted Headers. Power Query rất chu đáo khi tự động chèn thêm bước định dạng kiểu dữ liệu ngay phía sau:Bước 1: Thăng cấp tiêu đề

CustomStep1 = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

Bước 2: Power Query tự động thêm (Đây là bẫy dễ gây lỗi)

CustomStep2 = Table.TransformColumnTypes(CustomStep1, {{“card_id”, Int64.Type}, {“amount”, type number}, {“date”, type datetime}})

Nếu sau này cột card_id trong file Excel gốc bị người dùng đổi tên thành CardID, bước CustomStep1 vẫn chạy thành công nhưng bước CustomStep2 sẽ lập tức báo lỗi vì nó không tìm thấy cột nào tên là card_id để chuyển kiểu dữ liệu sang Int64.Type.

Hãy chủ động gộp hoặc xóa bớt các bước Table.TransformColumnTypes thừa thãi này trong Advanced Editor để mã M của bạn tối giản và an toàn.

Tổng kết

Việc nắm vững kỹ thuật xử lý Promoted – Demoted Headers là một trong những nền tảng quan trọng nhất quyết định tính chính xác, hiệu năng và độ ổn định của toàn bộ hệ thống báo cáo trong Excel hay Power BI. Việc chuyển đổi linh hoạt giữa các dòng dữ liệu thô và tên cột thực tế không chỉ giúp bạn làm sạch các nguồn thông tin lộn xộn, mà còn tạo dựng một cấu trúc dữ liệu chặt chẽ và nhất quán xuyên suốt quy trình ETL của doanh nghiệp.