Ghi chú
Đây là một trong loạt bài viết chi tiết các giải pháp được gửi đến cho thử thách Podcast 2316.
Trong vấn đề định hình dữ liệu ban đầu của tôi, tôi đã gặp phải sự cố rất sớm trong quá trình này. Dữ liệu đến sẽ có nhiều cột với tiêu đề Q1.
Trong giải pháp của mình, tôi đã tạo một dải ô được đặt tên là “UglyData” và nhập dải ô đó vào Power Query. Điều này dẫn đến kết quả không vui khi Power Query đổi tên các cột của tôi thành Q1_1.
Sau đó, sau khi giải nén, tôi phải trích xuất chỉ hai ký tự còn lại từ các tiêu đề đó.
Có ba giải pháp riêng biệt cho vấn đề này:
- Wyn Hopkins và Demote Headers
- MF Wong và bỏ chọn Bảng của tôi có tiêu đề (cũng do Peter Bartholomew gợi ý)
- Jason M và chỉ cần xóa Tiêu đề Quảng cáo (cũng được đề xuất bởi Ondřej Malinský và Excel MVP John MacDougall)
Sự đổi mới đầu tiên là của Wyn Hopkins tại Access Analytic. Thay vì một dải ô được đặt tên, Wyn đã chuyển đổi dữ liệu thành một bảng bằng Ctrl + T. Tại thời điểm này, thiệt hại cho các tiêu đề đã được thực hiện, vì Excel đã chuyển đổi các tiêu đề thành:
Sau khi Wyn lấy dữ liệu vào Power Query, sau đó anh ta mở menu thả xuống Sử dụng Hàng đầu tiên làm Tiêu đề và chọn Sử dụng Tiêu đề làm Hàng đầu tiên. Tôi không bao giờ nhận ra điều này ở đó. Nó tạo ra một bước được gọi là Table.DemoteHeaders.
Nhưng, ngay cả với sự cải tiến của Wyn, sau này anh ta vẫn phải trích xuất 2 ký tự đầu tiên từ những tiêu đề đó.
Cải tiến thứ hai là kỹ thuật của MF Wong. Khi tạo bảng, anh ấy đã bỏ chọn Bảng của tôi có tiêu đề!
Điều này đảm bảo rằng Excel để nguyên nhiều tiêu đề Q1 và không cần trích xuất thêm hậu tố sau này.
Tôi hiểu có những người trong trại "Tôi yêu bàn". Video của MF Wong đã chứng minh cách anh có thể thêm nhân viên mới vào bên phải dữ liệu và bảng tự động mở rộng. Có rất nhiều lý do chính đáng để sử dụng bảng.
Nhưng, bởi vì tôi yêu thích Tổng phụ, Chế độ xem tùy chỉnh và Lọc theo lựa chọn, tôi có xu hướng không sử dụng Bảng. Vì vậy, tôi đánh giá cao giải pháp từ Jason M. Anh ấy đã giữ dữ liệu dưới dạng phạm vi được đặt tên của UglyData. Ngay sau khi nhập dữ liệu vào Power Query, anh ấy đã xóa hai bước sau:
Bây giờ, với dữ liệu đơn giản trong Hàng 1, không có gì rắc rối khi có nhiều cột được gọi là Q1.
Đây là mã của Wyn Hopkin hiển thị DemotedHeaders:
let Source = Excel.CurrentWorkbook()((Name="Table1"))(Content), #"Demoted Headers1" = Table.DemoteHeaders(Source), #"Transposed Table1" = Table.Transpose(#"Demoted Headers1"), #"Added Custom" = Table.AddColumn(#"Transposed Table1", "Custom", each if Text.Start((Column1),1) = "Q" then null else (Column1)), #"Filled Down" = Table.FillDown(#"Added Custom",("Custom")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Custom) "Dept. Total")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.StartsWith((Column1), "Employee")), #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", (PromoteAllScalars=true)), #"Extracted First Characters" = Table.TransformColumns(#"Promoted Headers", (("Category Description", each Text.Start(_, 2), type text))), #"Reordered Columns" = Table.ReorderColumns(#"Extracted First Characters",("Category Description_1", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category Description_1", "Category Description"), "Attribute", "Value"), #"Reordered Columns1" = Table.ReorderColumns(#"Unpivoted Other Columns",("Category Description_1", "Attribute", "Category Description", "Value")), #"Pivoted Column" = Table.Pivot(#"Reordered Columns1", List.Distinct(#"Reordered Columns1"(#"Category Description")), "Category Description", "Value", List.Sum), #"Reordered Columns2" = Table.ReorderColumns(#"Pivoted Column",("Attribute", "Category Description_1", "Q1", "Q2", "Q3", "Q4")), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns2",(("Attribute", "Cat Deasc"), ("Category Description_1", "Emp Name"))), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",(("Emp Name", type text), ("Q1", Int64.Type), ("Q2", Int64.Type), ("Q3", Int64.Type), ("Q4", Int64.Type))), #"Inserted Sum" = Table.AddColumn(#"Changed Type", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), Int64.Type) in #"Inserted Sum"Quay lại trang chính cho thử thách Podcast 2316.
Đọc bài viết tiếp theo trong loạt bài này: Power Query: Xóa cái này, Xóa những cái đó hoặc không xóa gì ?.
