Power Query buổi 1: Gộp 12 file bán hàng thành một bảng sạch

1. Data → Get Data → From File → From Folder → chọn thư mục Bán hàng → bấm Transform Data (chưa bấm Combine).
2. Lọc cột Extension chỉ giữ .xlsx.
3. Lọc tiếp cột Name → Text Filters → Does Not Begin With → gõ ~$. Đây là file tạm Excel tạo ra khi đang mở một file, cũng có đuôi .xlsx.

1. Bấm nút hai mũi tên ở góc phải tiêu đề cột Content (Combine Files).
2. Ở ô Sample File chọn Bán hàng T01.xlsx. Đừng để First file, vì file đầu danh sách có thể là T11.
3. Chọn sheet BaoCao → OK.
4. Trong khung Queries bên trái, bấm vào Transform Sample File. Xoá bước Promoted Headers và Changed Type nếu có.

Đang ở Transform Sample File.

1. Home → Remove Rows → Remove Top Rows → gõ 3 → OK.
2. Trên thanh công thức, sửa số 3 thành each [Column1] <> "Ngày". Power Query sẽ bỏ các dòng đầu cho tới khi gặp dòng tiêu đề, nên file nào phần đầu dài hay ngắn cũng chạy đúng.
3. Bấm Use First Row as Headers, rồi xoá bước Changed Type mà Power Query vừa tự thêm.
4. Lọc cột Ngày: bỏ chọn (null) và Tổng cộng (không thấy Tổng cộng thì gõ vào ô Search).

= Table.Skip(#"Navigation", each [Column1] <> "Ngày")

Cột nào xong hẳn mới sang cột sau.

1. Ngày: Home → Replace Values, Value To Find gõ ngày (có dấu cách ở cuối), Replace With để trống. Chuột phải cột Ngày → Change Type → Using Locale → đổi cả hai ô: Data Type chọn Date, Locale chọn Vietnamese (Vietnam). Từ đây không đổi kiểu cột Ngày thêm lần nào nữa.
2. Số CT → Tên sản phẩm: bấm tiêu đề Số CT, giữ Shift bấm Tên sản phẩm (5 cột) → Transform → Data Type → Text.
3. Mã NV: chuột phải → Fill → Down.
4. SKU: Transform → Format → Trim, rồi Format → UPPERCASE.
5. Số lượng: Data Type → Text. Replace Values, Value To Find gõ thùng (có dấu cách ở đầu), Replace With để trống, không tick Match entire cell contents. Rồi Data Type → Whole Number.
6. Đơn giá → Chiết khấu: bấm Đơn giá, giữ Shift bấm Chiết khấu (3 cột) → Data Type → Whole Number.

Hai dòng tô vàng ở bảng trên chỉ trùng hẳn sau khi đã điền Mã NV và chuẩn hoá SKU. Vì vậy bỏ trùng phải để cuối.

1. Ctrl+A (chọn tất cả cột) → Home → Keep Rows → Keep Duplicates để xem dòng trùng. Xem xong thì xoá bước này.
2. Ctrl+A lần nữa → Remove Rows → Remove Duplicates.

1. Bấm sang query Bán hàng. Chuột phải bước Expanded Table Column1 → Delete Until End.
2. Bấm nút hai mũi tên ở cột Transform File → bỏ tick Use original column name as prefix → OK.
3. Đặt kiểu từ trái qua phải: Ngày → Date; Số CT → Tên sản phẩm → Text; Số lượng → Chiết khấu → Whole Number.
4. Chọn cột Source.Name → Add Column → Extract → Text Between Delimiters, giữa T và . → đổi tên thành Tháng → Change Type → Whole Number.

1. Lọc cột Tháng: Number Filters → Equals → 7. Đừng tick trong danh sách, vì danh sách chỉ lấy 1.000 dòng đầu, có thể chưa có số 7.
2. Power Query báo lỗi The column 'Doanh thu' of the table wasn't found. Đây là lỗi cố ý của dữ liệu.
3. Mở file tháng 7 xem tên cột (xem xong đóng ngay): cột ghi Doanh số, không phải Doanh thu.

4. Sang Transform Sample File, chuột phải bước Promoted Headers → Insert Step After, sửa công thức thành:

= Table.RenameColumns(#"Promoted Headers", {{"Doanh số", "Doanh thu"}}, MissingField.Ignore)

MissingField.Ignore nghĩa là: file nào có cột Doanh số thì đổi tên, file nào không có thì bỏ qua, không báo lỗi.

5. Bấm bước Filtered Rows: công thức phải bắt đầu bằng Table.SelectRows(Custom1,. Quay lại query Bán hàng, xoá bước lọc Tháng = 7.

1. Home → Close & Load To → Table → New worksheet → OK.
2. Chép Tháng mới\Bán hàng T12.xlsx vào thư mục Bán hàng → Data → Refresh All.

Ghi nhớ

• Thứ tự làm sạch: việc của cả bảng trước (bỏ dòng đầu, lấy tiêu đề, lọc dòng thừa), rồi từng cột từ trái qua phải, cột nào xong hẳn mới sang cột sau. Bỏ trùng làm cuối cùng.
• Ngày theo vùng: máy kiểu Mỹ đọc 04/07 là ngày 7 tháng 4, kiểu Việt Nam đọc là ngày 4 tháng 7. Using Locale là cách nói cho Power Query biết dữ liệu viết theo kiểu Việt Nam.
• Combine & Load cũng gộp đủ các file như Combine & Transform, chỉ là nạp thẳng vào Excel mà không mở trình chỉnh sửa.

Lỗi hay gặp

File Bài tập kiểu dữ liệu.csv trong thư mục buổi 1.

1. Get Data → From Text/CSV → chọn file. Trong cửa sổ xem trước, chỉnh 3 ô: File Origin 65001: Unicode (UTF-8), Delimiter Semicolon, Data Type Detection Do not detect data types → bấm Transform Data (không bấm Load).
2. Applied Steps chỉ còn Source và Promoted Headers, mọi cột vẫn là chữ, bảng có 10 dòng.
3. Xử lý lần lượt từng cột từ trái qua phải. Ở mọi bước Using Locale, đổi cả hai ô: Data Type chọn đúng kiểu ghi bên dưới, Locale chọn Vietnamese (Vietnam).

Thư mục Biểu mẫu chi nhánh có 3 file báo cáo, phần đầu dài ngắn khác nhau (dòng tiêu đề bảng nằm ở dòng 8, 5 và 9), bên dưới có dòng Tổng cộng và phần chữ ký. Không có con số Remove Top Rows nào đúng cho cả 3 file, nên phải bỏ theo điều kiện.

Mẹo: để Power Query tự tạo công thức bằng nút trên ribbon, rồi chỉ sửa con số trên thanh công thức, tên bước trong ngoặc giữ nguyên.

1. Data → Get Data → From File → From Folder → chọn thư mục Biểu mẫu chi nhánh → Transform Data → bấm nút hai mũi tên ở cột Content. Sample File chọn Biểu mẫu chi nhánh HCM.xlsx, sheet Báo cáo → OK. Bấm vào Transform Sample File, xoá bước Promoted Headers và Changed Type nếu có. Kiểm tra: dòng 1 của Column1 là CÔNG TY CP THỰC PHẨM AN VY.
2. Bỏ phần đầu: Home → Remove Rows → Remove Top Rows → gõ 1 → OK. Sửa số 1 thành each [Column1] <> "STT". Bấm Use First Row as Headers, xoá bước Changed Type vừa tự thêm. Kiểm tra: tiêu đề là STT, Ngày, Số CT, SKU, Số lượng, Doanh thu.
3. Bỏ phần dưới: Home → Keep Rows → Keep Top Rows → gõ 1 → OK. Sửa số 1 thành each [STT] <> null and [STT] <> "Tổng cộng". Kiểm tra: file HCM còn 8 dòng (STT 1 đến 8).
4. Đổi kiểu từ trái qua phải: STT → Whole Number; Ngày → Date; Số CT, SKU → Text; Số lượng, Doanh thu → Whole Number. Kiểm tra: cột Ngày hiện dạng ngày, không phải số như 46317.
5. Sang query chính: chuột phải bước Expanded Table Column1 → Delete Until End. Bấm nút hai mũi tên ở cột Transform File, bỏ tick Use original column name as prefix → OK. Đặt lại kiểu như bước 4. Cột Source.Name: Replace Values Biểu mẫu thành rỗng, rồi .xlsx thành rỗng, đổi tên cột thành Chi nhánh.

= Table.Skip(#"Navigation", each [Column1] <> "STT")
= Table.FirstN(#"Promoted Headers", each [STT] <> null and [STT] <> "Tổng cộng")

Table.FirstN giữ các dòng từ trên xuống và dừng ở dòng đầu tiên không thoả điều kiện, nên bỏ được cả phần chữ ký dù phần đó ghi gì.

Không tạo query mới. Mở lại file Excel đã làm ở bước 08 và thêm cột vào query Bán hàng (đã có đủ 12 tháng, 2.085 dòng).

1. Khung Queries & Connections → bấm đúp query Bán hàng → trong Applied Steps chọn bước cuối cùng. Kiểm tra: ô Name ghi Bán hàng, cột Doanh thu có biểu tượng 123.
2. Add Column → Conditional Column. New column name: Loại đơn.
   • If Doanh thu is less than 2000000 → Output Nhỏ
   • Bấm Add Clause: Else If Doanh thu is less than 10000000 → Output Vừa
   • Else: Lớn → OK
3. Chọn cột Loại đơn → Transform → Group By → Count Rows để xem số đơn mỗi loại. Xem xong xoá bước này rồi Close & Load.

File Bán hàng Q1 nhiều sheet.xlsx có 3 sheet T01, T02, T03, cùng 8 cột và đã sạch. Mở cả file (không chọn sheet nào) thì Power Query trả về một bảng, mỗi sheet là một dòng: cột Name là tên sheet, cột Data là nội dung sheet. Bung cột Data ra là 3 sheet nối thành một bảng, còn cột Name trở thành cột Tháng.

1. Data → Get Data → From File → From Excel Workbook → chọn file. Trong Navigator, bấm vào dòng tên file ở trên cùng (không bấm T01, T02, T03) → Transform Data. Nếu nút Transform Data bị mờ: bấm T01 → Transform Data, rồi xoá bước Navigation, chỉ giữ bước Source.
2. Chọn 2 cột Name và Data → chuột phải → Remove Other Columns.
3. Bấm nút hai mũi tên ở cột Data → bỏ tick Use original column name as prefix → OK. Các cột là Name, Column1 … Column8.
4. Home → Use First Row as Headers → xoá bước Changed Type vừa tự thêm. Cột đầu bị đặt tên là T01 (giá trị dòng đầu của cột Name): bấm đúp tiêu đề, đổi tên thành Tháng.
5. Lọc cột Ngày: bỏ chọn giá trị Ngày (dòng tiêu đề của sheet T02 và T03 bị lẫn vào giữa bảng).
6. Cột Tháng: Replace Values T thành rỗng → Data Type → Whole Number.
7. Đổi kiểu từ trái qua phải: Ngày → Date; Số CT → SKU → Text; Số lượng → Doanh thu → Whole Number. Xong thì Close & Load.