Power Query: Изтриване на това, изтриване на тези или изтриване на нищо? - Съвети за Excel

Съдържание

Забележка

Това е една от поредицата статии, описващи подробно решения, изпратени за предизвикателството на Podcast 2316.

Прекарвам си чудесно, като преживявам 29-те различни решения на предизвикателството за почистване на данни.

В моето решение и в много решения стигнах до точката, в която исках да изтрия реда с междинната сума.

Трябва да изтриете някои редове

Досега обаче имаше два много различни начина за справяне с това:

  • Крис Макнийл използва Pivot Quarters, Fill Up и запазва само редовете, които изтрих!
  • Майкъл Карпфен запази всичко. Защо да изтриете общите редове и да ги добавите обратно по-късно като обща колона?

Методът на Крис Макнийл

Крис добави графата Index и Modulo, както е описано тук.

Но след това той използва Модула и го завъртя, за да премине през колоните.

Методът на Крис Макнийл

Това остави куп нулеви стойности, които той запълни с Fill Up.

По-късно той запазва само имената на служителите. В реалния живот методът му няма да работи така, както е написан, защото ще имате истински имена на служители като Анди, Боб, Чарли вместо Служител1, Служител2, Служител3, но има и други начини да идентифицирате тези редове, които да запазите.

Ето кода на Крис

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"

Методът на Майкъл Карпфен

Майкъл имаше колона Modulo, както много други решения. Там, където повечето хора биха изтрили Modulo = 0, Майкъл го запази, но добави етикет TOTAL. Ако Modulo е 0, наречете го Total, в противен случай използвайте името от колоната Quarter. Това е умно.

Методът на Майкъл Карпфен

Тази колона вдясно по-късно беше завъртяна, за да премине през нея.

След това Майкъл направи пренаредени колони, за да премести Тоталите в най-дясната страна.

Преместване в края

Ето кода на Майкъл:

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"

Върнете се на главната страница за предизвикателството Podcast 2316.

Прочетете следващата статия от тази поредица: Power Query: изолиране на редовете, които представляват тримесечия.

Интересни статии...