Power Query: Xóa cái này, Xóa cái này hay không xóa gì? - Mẹo Excel

Mục lục

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.

Tôi đang có một khoảng thời gian tuyệt vời khi trải qua 29 giải pháp khác nhau cho thử thách Làm sạch dữ liệu.

Trong giải pháp của tôi và trong nhiều giải pháp, tôi đã đi đến điểm mà tôi muốn xóa hàng tổng phụ.

Cần xóa một số hàng

Tuy nhiên, cho đến nay, có hai cách rất khác nhau để giải quyết vấn đề này:

  • Chris McNeil đã sử dụng Pivot Quarters, Fill Up và chỉ giữ lại những hàng tôi đã xóa!
  • Michael Karpfen đã giữ mọi thứ. Tại sao lại xóa tổng số hàng và thêm lại chúng sau này dưới dạng tổng cột?

Phương pháp của Chris McNeil

Chris đã thêm cột Chỉ mục và Modulo như được mô tả ở đây.

Nhưng sau đó anh ấy công cụ Modulo và xoay nó để đi qua các cột.

Phương pháp của Chris McNeil

Điều này để lại một loạt các giá trị rỗng, mà anh ta đã điền vào Fill Up.

Sau đó, ông chỉ giữ lại tên nhân viên. Trong cuộc sống thực, phương pháp của anh ấy sẽ không hoạt động như đã viết, vì bạn sẽ có tên nhân viên thực như Andy, Bob, Charlie thay vì Employee1, Employee2, Employee3, nhưng có những cách khác để xác định các hàng đó cần giữ lại.

Đây là mã của Chris

let Source = Excel.CurrentWorkbook()((Name="Table1"))(Content), #"Changed Type" = Table.TransformColumnTypes(Source,(("Category Description", type text), ("Dept. Total", type number), ("Q1", type number), ("Q2", type number), ("Q3", type number), ("Q4", Int64.Type), ("Employee 1", type number), ("Q12", type number), ("Q23", type number), ("Q34", Int64.Type), ("Q45", Int64.Type), ("Employee 2", Int64.Type), ("Q16", Int64.Type), ("Q27", Int64.Type), ("Q38", Int64.Type), ("Q49", Int64.Type), ("Employee 3", Int64.Type), ("Q110", Int64.Type), ("Q211", Int64.Type), ("Q312", Int64.Type), ("Q413", Int64.Type), ("Employee 4", type number), ("Q114", type number), ("Q215", type number), ("Q316", type number), ("Q417", Int64.Type))), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", ("Category Description"), "Attribute", "Value"), #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1), #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod((Index), 5), type number), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Inserted Modulo", (("Modulo", type text)), "en-US"), List.Distinct(Table.TransformColumnTypes(#"Inserted Modulo", (("Modulo", type text)), "en-US")(Modulo)), "Modulo", "Value"), #"Sorted Rows" = Table.Sort(#"Pivoted Column",(("Index", Order.Ascending))), #"Filled Up" = Table.FillUp(#"Sorted Rows",("0", "1", "2", "3", "4")), #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ((Attribute) = "Employee 1" or (Attribute) = "Employee 2" or (Attribute) = "Employee 3" or (Attribute) = "Employee 4")), #"Sorted Rows1" = Table.Sort(#"Filtered Rows",(("Attribute", Order.Ascending), ("Category Description", Order.Ascending))), #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows1",("Category Description", "Attribute", "Index", "1", "2", "3", "4", "0")), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",(("0", "Total"), ("1", "Q1"), ("2", "Q2"), ("3", "Q3"), ("4", "Q4"))), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",("Index")) in #"Removed Columns"

Phương pháp của Michael Karpfen

Michael đã có một cột Modulo giống như nhiều giải pháp khác. Nơi hầu hết mọi người sẽ xóa Modulo = 0, Michael đã giữ nó, nhưng thêm nhãn TOTAL. Nếu Modulo là 0, hãy gọi nó là Tổng, nếu không thì sử dụng tên từ cột Quý. Điều này là thông minh.

Phương pháp của Michael Karpfen

Cột bên phải đó sau đó đã được xoay để đi qua.

Sau đó, Michael đã sắp xếp lại các cột để di chuyển Tổng số sang phía ngoài cùng bên phải.

Di chuyển đến cuối

Đây là mã của Michael:

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"

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: Truy vấn nguồn: Cô lập các hàng đại diện cho phần.

thú vị bài viết...