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.
Một trong những vấn đề với giải pháp của tôi là trình tự cuối cùng của các danh mục không nhất thiết phải khớp với trình tự ban đầu của các cột. Tôi nhận ra điều này ở cuối video của mình và vì nó không đặc biệt quan trọng nên tôi không lo lắng về điều đó.
Tuy nhiên, Josh Johnson đã gửi đến một giải pháp xử lý nó. Khi Josh nói rằng anh ấy sử dụng cột Chỉ mục, tôi cho rằng nó giống như Chỉ mục và Modulo trong Power Query: Number Groups of Records là 1 đến 5 lặp đi lặp lại. Nhưng cách sử dụng của Josh hoàn toàn khác.
Lưu ý: Excel MVP John MacDougall cũng sử dụng phương pháp này, nhưng anh ấy đã nối cột chỉ mục vào cuối mô tả danh mục. Xem video của John tại đây: https://www.youtube.com/watch?v=Dqmb6SEJDXI và đọc thêm về mã của anh ấy tại đây: Excel MVPs tấn công vấn đề làm sạch dữ liệu trong Power Query.
Đầu quá trình này, khi Josh vẫn chỉ có sáu bản ghi, anh ấy đã thêm một chỉ mục bắt đầu từ 1. Josh nhấp vào thanh công thức và đổi tên cột Chỉ mục thành Danh mục.
Cột Danh mục là cột cuối cùng mới. Anh ấy sử dụng Move, đến Beginning để chuyển nó lên vị trí đầu tiên:
Sau đó, rất nhiều bước khác sẽ xảy ra. Chúng là những bước mang tính sáng tạo nhưng hầu như đã được đề cập trong các bài báo khác cho đến nay. Sau nhiều bước như vậy, tôi bắt đầu nghĩ rằng các số từ 1 đến 6 chỉ là một sự nhầm lẫn. Tôi nghĩ rằng có thể Josh sẽ xóa chúng mà không sử dụng chúng.
Josh Unpivots, sau đó là cột điều kiện, sau đó điền xuống, sau đó xoay vòng, thêm tổng số. Anh ấy dường như không bao giờ sử dụng cột Category đó. Sau nhiều bước, anh ấy ở đây:
Nhưng sau đó ở bước cuối cùng, Josh sắp xếp dữ liệu theo Tên nhân viên rồi đến Danh mục!
Tại thời điểm này, anh ta có thể xóa cột Danh mục. Sự khác biệt cuối cùng: PTO xuất hiện trước Dự án A, giống như nó có trong các cột ban đầu. Đó là một liên lạc tốt đẹp.
Tôi cũng sẽ chỉ ra rằng Josh đã gửi trong một video về việc anh ấy thực hiện các bước này. Kudo to Josh vì đã sử dụng phím tắt bên trong Power Query!
Đây là mã của Josh:
let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Promoted Headers" = Table.PromoteHeaders(Source, (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Dept. Total", type number), ("Q1", type number), ("Q2", type number), ("Q3", type number), ("Q4", Int64.Type), ("Employee 1", type number), ("Q1_1", type number), ("Q2_2", type number), ("Q3_3", Int64.Type), ("Q4_4", Int64.Type), ("Employee 2", Int64.Type), ("Q1_5", Int64.Type), ("Q2_6", Int64.Type), ("Q3_7", Int64.Type), ("Q4_8", Int64.Type), ("Employee 3", Int64.Type), ("Q1_9", Int64.Type), ("Q2_10", Int64.Type), ("Q3_11", Int64.Type), ("Q4_12", Int64.Type), ("Employee 4", type number), ("Q1_13", type number), ("Q2_14", type number), ("Q3_15", type number), ("Q4_16", Int64.Type))), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Category", 1, 1), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",("Category", "Category Description", "Dept. Total", "Q1", "Q2", "Q3", "Q4", "Employee 1", "Q1_1", "Q2_2", "Q3_3", "Q4_4", "Employee 2", "Q1_5", "Q2_6", "Q3_7", "Q4_8", "Employee 3", "Q1_9", "Q2_10", "Q3_11", "Q4_12", "Employee 4", "Q1_13", "Q2_14", "Q3_15", "Q4_16")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category", "Category Description"), "Attribute", "Value"), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", (("Attribute", each Text.BeforeDelimiter(_, "_"), type text))), #"Added Conditional Column" = Table.AddColumn(#"Extracted Text Before Delimiter", "Employee Name", each if not Text.StartsWith((Attribute), "Q") then (Attribute) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee Name")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Attribute) = "Q1" or (Attribute) = "Q2" or (Attribute) = "Q3" or (Attribute) = "Q4") and ((Employee Name) "Dept. Total")), #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"(Attribute)), "Attribute", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Sorted Rows" = Table.Sort(#"Inserted Sum",(("Employee Name", Order.Ascending), ("Category", Order.Ascending))), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",("Category")) in #"Removed Columns"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: Excel MVPs tấn công sự cố làm sạch dữ liệu trong Power Query.
