Notă
Acesta este unul dintr-o serie de articole care detaliază soluțiile trimise pentru provocarea Podcast 2316.
Mă distrez foarte bine prin cele 29 de soluții diferite pentru provocarea de curățare a datelor.
În soluția mea și în multe soluții, am ajuns până la punctul în care am vrut să șterg rândul subtotal.
Până în prezent, însă, au existat două moduri foarte diferite de a face față acestui lucru:
- Chris McNeil a folosit Pivot Quarters, Fill Up și a păstrat doar rândurile pe care le-am șters!
- Michael Karpfen a păstrat totul. De ce să ștergeți rândurile totale și să le adăugați mai târziu ca o coloană totală?
Metoda lui Chris McNeil
Chris a adăugat coloana Index și Modulo așa cum este descris aici.
Dar apoi a sculat Modulo și l-a pivotat pentru a trece peste coloane.
Acest lucru a lăsat o grămadă de valori nule, pe care le-a completat cu Fill Up.
Mai târziu, a păstrat doar numele angajaților. În viața reală, metoda sa nu va funcționa așa cum este scrisă, deoarece veți avea nume reale de angajați precum Andy, Bob, Charlie în loc de Angajat1, Angajat2, Angajat3, dar există și alte modalități de a identifica acele rânduri de păstrat.
Iată codul lui 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"Metoda lui Michael Karpfen
Michael avea o coloană Modulo ca multe alte soluții. În cazul în care majoritatea oamenilor ar șterge Modulo = 0, Michael l-a păstrat, dar a adăugat o etichetă TOTAL. Dacă Modulo este 0, numiți-l Total, altfel folosiți numele din coloana Quarter. Este inteligent.
Coloana din dreapta a fost ulterior pivotată pentru a trece peste.
Michael a făcut apoi o coloană reordonată pentru a muta totalul în partea dreaptă.
Iată codul lui 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"Reveniți la pagina principală pentru provocarea Podcast 2316.
Citiți următorul articol din această serie: Power Query: Isolating the Rows that represent Quarters.
