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.
Kudos to Fowmy cho giải pháp này. Nó sẽ không hoạt động trong cuộc sống thực, nhưng nó hoạt động tốt với dữ liệu giả được đưa ra.
Có một điểm trong mỗi giải pháp mà bạn cần tìm ra hàng nào chứa tên nhân viên và hàng nào chứa phần tư.
Thay vì sử dụng cột có điều kiện, Fowmy phân tách bằng dấu phân cách, sử dụng Q làm dấu phân cách. Hãy kiểm tra điều này… tất cả các phần tư bây giờ đều ở trong một cột mới.
Ở phần trên, trước khi bạn có thể Fill Down, bạn cần có khoảng trống trong Tên nhân viên để nói là Không có. Fowmy giải quyết điều này bằng:
= Table.ReplaceValue(#"Split Column by Delimiter","",null,Replacer.ReplaceValue,("Employee Name"))
Đây là lý do tại sao điều này sẽ không hoạt động với dữ liệu thực: Sẽ có một nhân viên có chữ Q trong tên của họ. Công ty sẽ thuê Angelique, Dominique hoặc Ezequiel. Điều đó sẽ làm hỏng giải pháp Cột Chia tuyệt vời từ Fowmy.
Sau đó, khi Fowmy muốn số 1, 2, 3, 4 nói Q1, Q2, Q3, Q4, hãy sử dụng Định dạng, Thêm tiền tố:
Dưới đây là mã của Fowmy:
let Source = Excel.CurrentWorkbook()((Name="Table1"))(Content), #"Transposed Table" = Table.Transpose(Source), #"Split Column by Delimiter" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByEachDelimiter(("Q"), QuoteStyle.Csv, false), ("Employee Name", "Qtr")), #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter","",null,Replacer.ReplaceValue,("Employee Name")), #"Filled Down" = Table.FillDown(#"Replaced Value",("Employee Name")), #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", (PromoteAllScalars=true)), #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ((Category Description) "Dept. Total") and ((Column2) null)), #"Added Prefix" = Table.TransformColumns(#"Filtered Rows", (("Column2", each "Q" & Text.From(_, "en-US"), type text))), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Prefix", ("Category Description", "Column2"), "Attribute", "Value"), #"Reordered Columns" = Table.ReorderColumns(#"Unpivoted Other Columns",("Attribute", "Category Description", "Column2", "Value")), #"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"(Column2)), "Column2", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Renamed Columns" = Table.RenameColumns(#"Inserted Sum",(("Category Description", "Employee Name"), ("Attribute", "Category Description"))), #"Sorted Rows" = Table.Sort(#"Renamed Columns",(("Employee Name", Order.Ascending), ("Category Description", Order.Ascending))) in #"Sorted Rows"Jonathan Cooper đã gửi một biến thể về kỹ thuật này. Jonathan đã sử dụng bốn bước để thay đổi “Q1” thành “_Q1”, sau đó “Q2” thành “_Q2”, sau đó “Q3” thành “_Q3” rồi “Q4” thành “_Q4”. Đây là ngay sau khi thay đổi Q2:
Sau đó, khi Jonathan bị chia đôi bởi Dấu gạch dưới, anh ta ngăn cản Quentin bị chia cắt. Anh ta cũng tránh việc sau này phải thêm Q trước cột.
Tôi cũng thích rằng Jonathan đã thay thế tất cả các số không bằng các giá trị null trong giải pháp cuối cùng. Đây là mã của anh ấy:
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))), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", ("Category Description"), "Attribute", "Value"), #"replace 0 with null" = Table.ReplaceValue(#"Unpivoted Other Columns",0,null,Replacer.ReplaceValue,("Value")), #"prep qtr to be split1" = Table.ReplaceValue(#"replace 0 with null","Q1","_Q1",Replacer.ReplaceText,("Attribute")), #"prep qtr to be split2" = Table.ReplaceValue(#"prep qtr to be split1","Q2","_Q2",Replacer.ReplaceText,("Attribute")), #"prep qtr to be split3" = Table.ReplaceValue(#"prep qtr to be split2","Q3","_Q3",Replacer.ReplaceText,("Attribute")), #"prep qtr to be split4" = Table.ReplaceValue(#"prep qtr to be split3","Q4","_Q4",Replacer.ReplaceText,("Attribute")), #"Split Column by underscore" = Table.SplitColumn(#"prep qtr to be split4", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), ("Employee", "Period", "Attribute.3")), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by underscore",(("Employee", type text), ("Period", type text), ("Attribute.3", Int64.Type))), #"prep to fill down" = Table.ReplaceValue(#"Changed Type1","",null,Replacer.ReplaceValue,("Employee")), #"Filled Down employee" = Table.FillDown(#"prep to fill down",("Employee")), #"remove dept totals" = Table.SelectRows(#"Filled Down employee", each (Employee) "Dept. Total"), #"remove employee totals" = Table.SelectRows(#"remove dept totals", each (Period) null), #"Removed extra column" = Table.RemoveColumns(#"remove employee totals",("Attribute.3")), #"Pivoted Column" = Table.Pivot(#"Removed extra column", List.Distinct(#"Removed extra column"(Period)), "Period", "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", Order.Ascending), ("Category Description", Order.Ascending))) in #"Sorted Rows"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: Sắp xếp Mục hàng.
