Dữ liệu xuất khẩu thương mại điện tử thường rối. Một file có thể chứa nhiều biến thể SKU, thông tin khách hàng không nhất quán, trường địa chỉ gộp, giá trị trùng lặp hoặc null, và chi tiết giá phân tán. Nếu bạn từng phân tích dữ liệu như vậy trong Excel hoặc Power BI, bạn biết mọi thứ phức tạp nhanh đến đâu. Đây là lúc Power Query cực kỳ hữu ích. Dù dùng công cụ nào—Excel hay Power BI—Power Query giúp làm sạch và định hình lại dữ liệu thô thương mại điện tử không cần công thức phức tạp.
Trong hướng dẫn này, chúng ta học 5 biến đổi Power Query thiết yếu cho dữ liệu bán hàng thương mại điện tử đặc biệt hữu ích cho tập dữ liệu cửa hàng trực tuyến.
Unpivot (Chuyển Ngược) Các Cột Biến Thể SKU
Nhiều file xuất khẩu thương mại điện tử lưu sản phẩm đặt hàng qua nhiều cột, như Stock_S, SKU 1, SKU 2, SKU 3, Variant 1, Variant 2, Variant 3, mỗi cột chứa số lượng. Cấu trúc này khó phân tích vì mỗi đơn hàng lan chi tiết sản phẩm qua nhiều cột. Pivot table, DAX measures, và công thức SUM không tổng hợp hiệu quả qua định dạng rộng này. Unpivot tạo bảng sự thật (fact table) dài, chuẩn hóa, lý tưởng cho phân tích.
Các bước:
- Tải dữ liệu vào Power Query
- Chọn cột SKU (Stock S, M, L, XL)
- Vào tab Transform >> chọn Unpivot Columns
- Power Query chuyển các cột SKU thành hai cột mới và đổi tên:
- Attribute: Kích thước (Size)
- Value: Số lượng (Quantity)
Dùng biến đổi này khi xuất khẩu lưu nhiều sản phẩm đặt hàng trong cột riêng thay vì hàng riêng.
Tách Chuỗi Địa Chỉ Giao Hàng Thành Nhiều Trường
Khoảng 90% file thô thương mại điện tử lưu toàn bộ địa chỉ giao hàng trong một chuỗi. Tách trường này cho phép báo cáo thuế cấp bang, tối ưu chi phí vận chuyển, phân tích hiệu suất khu vực, và bản đồ.
Các bước:
- Chọn cột Shipping Address
- Vào tab Home >> chọn Split Column >> chọn By Delimiter
- Dựa trên dữ liệu, dùng phân tách như:
- Dấu phẩy
- Dấu gạch ngang
- Ngắt dòng
- Phân tách tùy chỉnh
- Với dữ liệu hiện có, chọn Dấu phẩy làm phân tách
- Nhấn OK
- Đổi tên cột kết quả thành nhãn có nghĩa: Đường, Thành phố, Mã bưu chính, Quốc gia
Sau khi tách địa chỉ, bạn phân tích đơn hàng theo thành phố, hiệu suất giao hàng theo khu vực, doanh số theo vùng, và khách hàng quay lại từ vị trí cụ thể. Đặc biệt hữu ích cho doanh nghiệp giao hàng cục bộ và báo cáo bán hàng theo vùng.
Ghi chú nâng cao: Không phải địa chỉ nào cũng cùng định dạng. Một số có phần thừa, thiếu, hoặc chi tiết căn hộ. Trong trường hợp này, bạn có thể:
- Cắt khoảng trắng sau tách
- Gộp lại các phần chọn
- Dùng Trích xuất Văn bản Trước/Sau Phân Tách
- Tạo cột tùy chỉnh xử lý ngoại lệ
Cắt, Làm Sạch, Và Chuẩn Hóa Kiểu Dữ Liệu
Trong tập dữ liệu thương mại điện tử lớn, tên khách hàng, SKU, email, danh mục sản phẩm thường chứa khoảng trắng đầu/cuối, ký tự không in, và viết hoa không nhất quán. Kiểu dữ liệu cũng có thể không khớp, như giá trị số lưu dạng văn bản.
Những vấn đề nhỏ này gây rắc rối lớn. Cùng dữ liệu xuất hiện như giá trị khác, thất bại thao tác gộp (merge), hoặc tạo nhóm trùng lặp.
Làm Sạch Dữ Liệu:
- Cắt khoảng trắng:
- Chọn cột văn bản
- Vào Transform >> chọn Format >> chọn Trim
- Loại bỏ khoảng trắng đầu và cuối
- Làm sạch ký tự ẩn:
- Với cùng cột chọn,
- Chọn Format >> chọn Clean
- Loại bỏ ký tự không in
- Chuẩn hóa viết hoa:
- Vào Transform >> chọn Format >> chọn:
- Viết Hoa Mỗi Từ (Capitalize Each Word) cho tên
- Chữ Thường (Lowercase) cho email nếu cần
Chuẩn Hóa Kiểu Dữ Liệu:
- Chọn cột ngày
- Vào tab Home >> chọn Data Type >> chọn Date
- Nếu cần, thêm cột:
- Vào tab Add Column >> mở rộng Date
- Chọn Năm (Year), Tháng (Month), Ngày (Day), Quý (Quarter), Ngày Trong Tuần (Day of Week)
- Chọn cột số như Số lượng và Giá
- Mở rộng biểu tượng tiêu đề cột >> chọn Số Thập Phân (Decimal Number)
- Hoặc vào tab Transform >> chọn Data Type >> chọn Số Thập Phân hoặc Số Nguyên (Whole Number)
Biến đổi này thiết yếu vì nó làm cho nhóm, gộp, lọc, và tra cứu đáng tin cậy hơn nhiều.
Xóa Trùng Lặp Và Xử Lý Giá Trị Null
Null phổ biến trong dữ liệu thương mại điện tử. Một số vô hại, những cái khác phá vỡ tính toán. Cổng thanh toán và quy trình đồng bộ có thể tạo OrderID trùng, và null số lượng hoặc giá phá vỡ tổng và trực quan. Nếu null không xử lý đúng, kết quả bị sai lệch.
Xóa trùng lặp:
- Chọn OrderID và OrderDate làm khóa (ngăn xóa đơn hàng lặp lại cùng ngày hợp pháp)
- Vào tab Home >> chọn Remove Duplicates
- Chỉ xóa hàng thực sự hỏng:
- Vào tab Home >> chọn Remove Blank Rows (lọc hàng OrderID null trước)
Thay thế giá trị:
- Nếu giảm giá trống nghĩa là không giảm giá, thay null bằng 0
- Chọn tất cả cột Số lượng và Giá
- Vào tab Transform >> chọn Replace Values:
- Giá trị tìm: null
- Thay bằng: 0
Làm sạch giá trị văn bản:
- Loại bỏ tiền tố không cần như Stock_
- Chọn cột Size
- Vào tab Transform >> chọn Replace Values:
- Giá trị tìm: Stock_
- Thay bằng: (để trống)
Giờ giá trị Size sạch và đọc dễ.
Điền xuống giá trị lặp lại:
- Đôi khi chỉ hàng đầu tiên đơn hàng chứa OrderID hoặc tên khách hàng
- Chọn cột
- Vào Transform >> chọn Fill >> chọn Down
Cảnh báo quan trọng: Không thay thế mọi null một cách mù quáng. Giá trị thiếu có thể nghĩa "không áp dụng", "không biết", hoặc vấn đề dữ liệu. Luôn hiểu ý nghĩa kinh doanh trước khi thay thế.
Tạo Cột Tùy Chỉnh Tính Toán
Từ dữ liệu thương mại điện tử, bạn suy ra cột doanh thu hoặc biên. Mặc dù làm được với công thức Excel, những công thức đó vỡ khi làm mới. Cột tùy chỉnh Power Query tính lại tự động và vẫn là một phần đường ống ETL.
Net_Revenue (Doanh Thu Ròng):
- Vào tab Add Column >> chọn Custom Column
- Nhập tên
- Chèn công thức sau
[Unit_Price] * [Qty] * (1 - [Discount_Pct])
Gross_Margin (Biên Gộp):
[Net_Revenue] - ([Cost_Per_Unit] * [Qty])
Margin_Pct (%):
if [Net_Revenue] = 0 then 0 else [Gross_Margin] / [Net_Revenue]
- Đặt kiểu dữ liệu rõ ràng: Net_Revenue và Gross_Margin thành Currency, Margin_Pct thành Percentage
Quan trọng: Luôn bảo vệ công thức chia. Số 0 ở mẫu số gây lỗi toàn cột trong Power Query và có thể dẫn đến hàng bị bỏ khi tải. Dùng mẫu if [X] = 0 then null else … cho bất kỳ tỷ số tính toán nào.
Nhóm Và Tổng Hợp Cho Bảng Tóm Tắt Hiệu Suất Cao
File thô thương mại điện tử hàng triệu hàng làm chậm báo cáo. Nhóm tạo bảng tổng hợp hiệu quả cho bảng điều khiển đồng thời giữ truy vấn chi tiết cho phân tích sâu (drill-through).
Các bước:
- Vào Transform >> chọn Group By
- Chọn Category >> nhấn Add aggregation:
- Tổng Net_Revenue
- Trung bình Margin_Pct
- Đếm Order_ID (hàng)
- Đặt tên truy vấn mới Sales_Summary và giữ gốc Sales_Detail cho drill-through
Mẹo Pro: Trong Power BI, tắt "Enable load" trên truy vấn chi tiết và chỉ tải bảng tóm tắt và bảng chiều để cải thiện hiệu suất.
Bước cuối:
- Vào tab Home >> chọn Close & Apply tải dữ liệu
Kết Luận
Bây giờ bạn áp dụng 5 biến đổi Power Query thiết yếu cho dữ liệu bán hàng thương mại điện tử. Power Query là một trong những công cụ hiệu quả nhất làm sạch tập dữ liệu thương mại điện tử vì nó xử lý cả biến đổi đơn giản và phức tạp theo cách lặp lại được. Nó giảm đáng kể thời gian chuẩn bị dữ liệu. Đây không phải bước làm sạch chung chung — chúng giải quyết trực tiếp thách thức báo cáo thương mại điện tử thực tế và làm phân tích hạ lưu hiệu quả hơn nhiều. Khi bạn thoải mái với các biến đổi này, bạn xây dựng mẫu Power Query tái dùng cho mọi lần xuất khẩu marketplace mới.
Nhận Bài Tập Excel Nâng Cao Miễn Phí Kèm Giải Pháp!