Chào mọi người,
Dạo này mình làm việc với nhiều file báo cáo từ hệ thống khác về, và thường xuyên gặp phải dạng dữ liệu khá 'khó chịu': các bảng dữ liệu được lồng vào nhau trong một ô duy nhất. Ví dụ, trong một ô có thể chứa cả danh sách sản phẩm kèm số lượng, hoặc thông tin chi tiết của một đơn hàng. Excel mặc định không xử lý được dạng này, rất bất tiện cho việc phân tích.
Sau một hồi loay hoay, mình tìm ra cách xử lý khá hiệu quả bằng Power Query. Hôm nay chia sẻ lại cho anh em nào đang gặp tình huống tương tự.
Tình huống:
- Dữ liệu nguồn có một cột chứa các bảng Excel khác (hoặc text mô phỏng dạng bảng).
- Cần tách các bảng lồng nhau này ra thành các hàng riêng biệt để dễ dàng phân tích.
Cách làm với Power Query:
- Đưa dữ liệu vào Power Query (Data > From Table/Range).
- Chọn cột chứa dữ liệu dạng bảng lồng nhau.
- Vào tab Add Column > Custom Column.
- Trong ô Formula, nhập tên cột mới (ví dụ: ExtractedTable) và công thức sau (giả sử cột chứa dữ liệu lồng là NestedData):
if Text.Contains([NestedData], "[Table]") then Table.FromText([NestedData], [Delimiter = ",", Columns = 2, Encoding = 65001]) else nullLưu ý: Công thức này cần điều chỉnh tùy thuộc vào cấu trúc và ký tự phân cách trong dữ liệu lồng của bạn. Ví dụ trên giả định dữ liệu lồng là text, phân cách bằng dấu phẩy và có 2 cột. Nếu là bảng thực sự thì cách làm sẽ khác chút.
Sau đó, bạn sẽ thấy xuất hiện các bảng mới trong cột ExtractedTable. Tiếp tục nhấp vào biểu tượng mũi tên đôi ở tiêu đề cột này để Expand (mở rộng) dữ liệu ra các hàng.
Cách này tuy hơi thủ công lúc đầu thiết lập, nhưng khi có dữ liệu mới, chỉ cần refresh là Power Query tự động xử lý hết. Tiết kiệm được rất nhiều thời gian so với làm tay.
Có anh em nào có cách nào khác hay hơn không, chia sẻ thêm cho mọi người học hỏi nhé!