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 Thử thách truy vấn nguồn của tôi, một trong những bước là lấy trường tên từ mỗi bản ghi thứ 5 và sao chép nó xuống năm bản ghi. Giải pháp ban đầu của tôi khá khó hiểu, dựa trên thực tế là độ dài của tên sẽ dài hơn 2 ký tự.
Một số người, bao gồm MF Wong, Michael Karpfen, Peter Bartholomew, Chris McNeil, Jamie Rogers đã sử dụng một giải pháp tốt hơn nhiều liên quan đến cột Chỉ mục.
Hãy bắt đầu quá trình dữ liệu trông như thế này:
Đầu tiên, MF Wong lưu ý rằng bạn không cần năm bản ghi đầu tiên. Bạn đã có thể sử dụng
Home, Remove Rows, Remove Top Rows… , 5 Rows.
Excel MVP Oz du Soleil từ Excel on Fire cũng loại bỏ năm điều đó, nhưng anh ấy đã làm điều đó khi chúng vẫn còn là cột.
Sau đó, Thêm cột, Thêm cột chỉ mục, Từ 0. Điều này tạo ra một cột mới từ 0 đến NN.
Với cột Chỉ mục mới được chọn, hãy chuyển đến tab Chuyển đổi và chọn menu thả xuống Chuẩn từ nhóm Tab Số. Hãy cẩn thận: có một trình đơn thả xuống tương tự trên tab Thêm cột, nhưng việc chọn trình đơn trên tab Chuyển đổi sẽ ngăn không cho thêm cột bổ sung. Chọn Modulo từ menu thả xuống này và sau đó chỉ định rằng bạn muốn phần còn lại sau khi chia cho 5.
Sau đó
Điều này tạo ra một chuỗi các số từ 0 đến 4 được lặp đi lặp lại.
Từ đây, các bước để chuyển tên nhân viên qua tương tự như video gốc của tôi.
Thêm một cột có điều kiện mang lại tên hoặc giá trị Null và sau đó Điền xuống. Tìm thấy nhiều cách khác để tính toán cột này trong Power Query: Sử dụng Điều khoản Nếu khác trong Cột có Điều kiện.
Điền xuống để điền tên từ hàng đầu tiên đến năm hàng tiếp theo.
Cảm ơn MF Wong về video của anh ấy. Đảm bảo bật CC cho phụ đề tiếng Anh.
https://www.youtube.com/watch?v=So1n7sLE_Mg
Video của Peter Bartholomew:
https://www.youtube.com/watch?v=gb3OPfF_BNc
Michael Karpfen cũng nhận ra rằng không cần phải xóa các tổng và thêm lại sau. Mã M của anh ấy là:
let Quelle = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Höher gestufte Header" = Table.PromoteHeaders(Quelle, (PromoteAllScalars=true)), #"Geänderter Typ" = Table.TransformColumnTypes(#"Höher gestufte Header",(("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))), #"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Geänderter Typ", ("Category Description"), "Attribut", "Wert"), #"Hinzugefügter Index" = Table.AddIndexColumn(#"Entpivotierte andere Spalten", "Index", 1, 1), #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Hinzugefügter Index", "Benutzerdefiniert", each Number.Mod((Index)-1,5)), #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "Benutzerdefiniert.1", each if (Benutzerdefiniert)=0 then (Attribut) else null), #"Nach unten gefüllt" = Table.FillDown(#"Hinzugefügte benutzerdefinierte Spalte1",("Benutzerdefiniert.1")), #"Neu angeordnete Spalten" = Table.ReorderColumns(#"Nach unten gefüllt",("Benutzerdefiniert.1", "Attribut", "Category Description", "Wert", "Index", "Benutzerdefiniert")), #"Geänderter Typ1" = Table.TransformColumnTypes(#"Neu angeordnete Spalten",(("Benutzerdefiniert", type text))), #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Geänderter Typ1", "Benutzerdefiniert.2", each if (Benutzerdefiniert) = "0" then "TOTAL" else "Q"&(Benutzerdefiniert)), #"Neu angeordnete Spalten1" = Table.ReorderColumns(#"Hinzugefügte benutzerdefinierte Spalte2",("Attribut", "Category Description", "Benutzerdefiniert.1", "Wert", "Index", "Benutzerdefiniert", "Benutzerdefiniert.2")), #"Entfernte Spalten" = Table.RemoveColumns(#"Neu angeordnete Spalten1",("Attribut", "Index", "Benutzerdefiniert")), #"Pivotierte Spalte" = Table.Pivot(#"Entfernte Spalten", List.Distinct(#"Entfernte Spalten"(Benutzerdefiniert.2)), "Benutzerdefiniert.2", "Wert", List.Sum), #"Neu angeordnete Spalten2" = Table.ReorderColumns(#"Pivotierte Spalte",("Benutzerdefiniert.1", "Category Description", "Q1", "Q2", "Q3", "Q4", "TOTAL")), #"Sortierte Zeilen" = Table.Sort(#"Neu angeordnete Spalten2",(("Benutzerdefiniert.1", Order.Ascending))), #"Umbenannte Spalten" = Table.RenameColumns(#"Sortierte Zeilen",(("Benutzerdefiniert.1", "Employee Name"))) in #"Umbenannte Spalten"Lưu ý rằng Josh Johnson cũng đã sử dụng cột Chỉ mục, nhưng là một trong những bước đầu tiên và sử dụng cột đó như một cách sắp xếp ở một trong những bước cuối cùng.
Quay lại trang chính cho thử thách Podcast 2316.
Đọc bài tiếp theo của loạt bài này: Power Query: Trích xuất 2 ký tự còn lại từ một cột.
