2026-08-06 · Power Query & Excel
Bạn có một file kế hoạch doanh thu quen thuộc: cột đầu là mã khách hàng, sau đó là mười hai cột T1, T2 cho đến T12. Nhìn trên giấy thì rất dễ đọc. Nhưng khi đưa vào Pivot Table hay Power BI để hỏi "doanh thu quý 2 của nhóm khách miền Bắc là bao nhiêu", bạn phát hiện không làm được, hoặc phải cộng tay từng cột.
Sang năm, file có thêm cột T13, T14 cho hai tháng nhuận báo cáo, hoặc đổi sang bảng 12 tháng của năm mới. Mọi công thức và Pivot dựng trên bảng cũ vỡ hết.
Vấn đề không nằm ở Excel mà nằm ở hình dạng của bảng. Bài này đội ngũ EzTech hướng dẫn dùng Unpivot trong Power Query để đưa bảng ngang về dạng chuẩn trong năm bước, kèm ba cái bẫy hay làm hỏng số.
Bảng cho người đọc khác bảng cho máy đọc
Bảng ngang được thiết kế cho mắt người: mỗi tháng một cột, nhìn ngang là thấy cả năm. Máy thì cần ngược lại, gọi là dạng bảng chuẩn hay dạng dọc, với ba đặc điểm:
- Mỗi dòng là một quan sát, ở đây là một khách hàng trong một tháng.
- Mỗi cột là một thuộc tính, ở đây là Mã khách, Tháng, Doanh thu.
- Tên cột không mang dữ liệu. "T1" là một giá trị, không được làm tên cột.
Chính điều thứ ba là gốc rễ mọi rắc rối. Khi tháng nằm ở tên cột, bạn không lọc theo tháng được, không so cùng kỳ được, không nối được với bảng lịch, và cứ thêm một tháng là phải sửa lại toàn bộ công thức phía sau.
Đổi hình dạng bảng không làm mất dữ liệu. Một bảng 50 khách nhân 12 tháng trở thành 600 dòng, vẫn đúng từng con số, chỉ khác cách sắp xếp. Và từ dạng dọc bạn dựng lại bảng ngang bất cứ lúc nào bằng Pivot Table trong ba giây, nên đây luôn là chiều đi có lợi.
Năm bước unpivot trong Power Query
Bước 1: đưa bảng vào Power Query
Đặt con trỏ trong vùng dữ liệu, vào tab Data rồi bấm From Table/Range. Excel hỏi vùng dữ liệu và có dòng tiêu đề hay không, xác nhận là cửa sổ Power Query Editor mở ra.
Nếu dữ liệu nằm ở file khác, dùng Data > Get Data > From File để lấy vào. Cách gộp nhiều file cùng lúc nằm trong bài gộp nhiều file Excel bằng Power Query.
Bước 2: chọn những cột cần GIỮ NGUYÊN
Đây là bước quyết định và cũng là bước hay làm sai. Bạn không chọn mười hai cột tháng, mà chọn những cột định danh cần giữ: Mã khách, Tên khách, Miền, Nhóm hàng.
Bước 3: chuột phải, chọn Unpivot Other Columns
Bấm chuột phải lên phần tiêu đề của các cột vừa chọn, chọn Unpivot Other Columns. Power Query gập toàn bộ những cột còn lại thành hai cột mới: Attribute chứa tên cột cũ, tức là tháng, và Value chứa con số.
Trong menu có ba lệnh gần giống nhau, chọn nhầm là kết quả sai:
| Lệnh | Nó làm gì | Khi nào dùng |
|---|---|---|
| Unpivot Columns | Gập đúng những cột bạn đang chọn | Chỉ khi danh sách cột cố định vĩnh viễn |
| Unpivot Other Columns | Gập tất cả cột không được chọn | Mặc định nên dùng — thêm tháng mới vẫn chạy |
| Unpivot Only Selected Columns | Gập đúng cột đang chọn, khóa cứng danh sách | Khi muốn chặn cột lạ tự lọt vào |
Khác biệt lộ ra vào tháng sau. Nếu bạn dùng Unpivot Columns và file nguồn có thêm cột T13, cột mới đó nằm ngoài danh sách đã ghi trong bước, nên nó sẽ nằm chình ình như một cột riêng thay vì gập vào. Dùng Unpivot Other Columns thì cột mới tự động được gập, không phải sửa gì.
Bước 4: đổi tên và đổi kiểu dữ liệu
Nhấp đúp vào tiêu đề cột để đổi Attribute thành Tháng và Value thành Doanh thu. Sau đó bấm vào biểu tượng kiểu dữ liệu bên trái tên cột, đặt cột số về Decimal Number và cột tháng về Text hoặc Date tùy bước tiếp theo.
Bước 5: Close & Load
Bấm Close & Load để đổ kết quả ra sheet mới, hoặc Close & Load To rồi chọn Only Create Connection nếu bạn định đưa thẳng vào Data Model. Từ đây trở đi, tháng sau chỉ cần thay file nguồn rồi bấm Refresh, toàn bộ năm bước tự chạy lại.
Đổi cột tháng thành ngày thật
Sau khi unpivot, cột Tháng thường chứa chữ dạng "T1", "Tháng 1" hoặc "Jan". Ở dạng chữ, nó sắp xếp theo bảng chữ cái, nên T10 đứng ngay sau T1 và biểu đồ của bạn chạy sai thứ tự. Nó cũng không nối được với bảng lịch để tính lũy kế hay so cùng kỳ.
Cách xử lý gọn nhất là thêm một cột ngày thật. Vào Add Column > Custom Column, tách lấy phần số trong chuỗi rồi ghép với năm để tạo ra ngày đầu tháng. Từ ngày đó bạn sinh tiếp cột Quý, cột Năm, và nối được vào bảng lịch trong Power BI.
Nếu năm nằm ở tên file hoặc tên sheet chứ không có trong bảng, hãy lấy nó ra thành một cột trước khi ghép. Đây cũng là lý do nên giữ tên file theo quy tắc cố định ngay từ đầu.
Ba cái bẫy hay làm hỏng số
1. Cột "Tổng cộng" lọt vào phần bị gập
Bảng ngang do người làm tay gần như luôn có cột Tổng cộng ở cuối. Nếu bạn để nguyên rồi unpivot, cột đó biến thành một "tháng" tên là Tổng cộng, và mọi tổng sau này bị đếm gấp đôi.
Con số vẫn ra, báo cáo vẫn chạy, chỉ là gấp đôi. Hãy xóa cột tổng ngay trong Power Query trước khi unpivot, bằng cách chuột phải lên tiêu đề rồi chọn Remove. Cần tổng thì để Pivot Table hoặc measure tự tính.
2. Tiêu đề hai tầng do ô gộp
Nhiều file có dòng đầu ghi "Quý 1" gộp ba ô, dòng dưới mới ghi T1, T2, T3. Khi nạp vào Power Query, ô gộp chỉ giữ giá trị ở ô đầu, các ô còn lại thành null, nên bảng vào sai ngay từ đầu.
Cách xử lý: nạp bảng ở dạng không có tiêu đề, dùng Transform > Fill > Right để lấp phần null của dòng quý, ghép hai dòng tiêu đề lại thành một, rồi mới promote lên làm header và unpivot. Nếu file nguồn là của bạn, tốt hơn hết là bỏ hẳn ô gộp ở vùng dữ liệu.
3. Ô trống bị mất khi unpivot
Power Query mặc định loại bỏ những dòng có giá trị rỗng sau khi unpivot. Phần lớn trường hợp đây là điều tốt, vì bảng nhẹ đi. Nhưng nếu bạn cần đủ mười hai dòng cho mỗi khách kể cả tháng chưa phát sinh, hãy thay ô trống bằng số 0 trước khi unpivot bằng Transform > Replace Values.
Một biến thể khó chịu hơn: ô trống thực ra là chuỗi rỗng hoặc dấu gạch ngang do người nhập gõ vào. Những giá trị đó không bị loại, nhưng làm cả cột nhảy sang kiểu Text và mọi phép cộng phía sau trả về lỗi. Nghiên cứu của EuSpRIG nhiều năm qua cho thấy phần lớn bảng tính đang dùng trong doanh nghiệp có chứa lỗi, và loại lỗi âm thầm kiểu này là một trong những nguồn phổ biến nhất.
Thói quen nên có: sau mỗi lần unpivot, so tổng cột Doanh thu ở bảng mới với tổng của bảng gốc. Bằng nhau thì yên tâm, lệch là biết ngay có cột tổng lọt vào hoặc có dòng bị mất.
Câu hỏi thường gặp
Unpivot có làm mất dữ liệu gốc không?
Không. Power Query đọc dữ liệu và tạo ra bảng kết quả ở chỗ khác, file gốc giữ nguyên. Bạn cũng xóa được bất kỳ bước nào trong khung Applied Steps để quay lại trạng thái trước đó.
Làm ngược lại từ bảng dọc về bảng ngang thì sao?
Dùng lệnh Pivot Column trong tab Transform, chọn cột Tháng làm tiêu đề và cột Doanh thu làm giá trị. Nhưng thường bạn không cần, vì Pivot Table trong Excel hoặc ma trận trong Power BI đã hiển thị dạng ngang sẵn cho người xem.
Bảng của tôi hơn một triệu dòng sau khi unpivot, Excel có chịu nổi không?
Sheet Excel chỉ chứa được hơn một triệu dòng, nhưng Power Query và Data Model thì không bị giới hạn đó. Hãy chọn Only Create Connection và tích Add this data to the Data Model thay vì đổ ra sheet, rồi làm báo cáo bằng Pivot Table chạy trên model.
Mỗi tháng tôi nhận file mới, có phải làm lại từ đầu không?
Không, đó chính là điểm mạnh của Power Query. Toàn bộ các bước được ghi lại, tháng sau chỉ cần thay file nguồn hoặc bỏ file vào đúng thư mục rồi bấm Refresh. Bạn có thể tự kiểm tra mức độ thành thạo bằng bộ trắc nghiệm Power Query miễn phí của EzTech.
Đổi hình dạng bảng một lần, dùng được nhiều năm
Unpivot là một lệnh chỉ mất vài giây nhưng thay đổi hẳn cách bạn làm việc với dữ liệu. Bảng đã ở dạng chuẩn thì Pivot Table, Power BI và mọi công cụ phân tích đều dùng được ngay, và file sang năm không còn làm vỡ báo cáo của năm nay.
Nếu dữ liệu của bạn xuất ra từ phần mềm kế toán và còn nhiều thứ phải dọn trước khi unpivot, bài xử lý dữ liệu MISA bằng Power Query đi qua đủ các bước đó. Muốn học Power Query bài bản để tự động hóa báo cáo hằng tháng, khóa DATA FINANCE dành cho dân kế toán tài chính đi đúng hướng này. Cần tư vấn lộ trình phù hợp, bạn cứ liên hệ với đội ngũ EzTech.
