Notă
Acesta este unul dintr-o serie de articole care detaliază soluțiile trimise pentru provocarea Podcast 2316.
Una dintre problemele cu soluția mea este că secvența finală a categoriilor nu se potrivea neapărat cu secvența inițială a coloanelor. Am realizat acest lucru chiar la sfârșitul videoclipului meu și, din moment ce nu era deosebit de important, nu mi-am făcut griji.
Cu toate acestea, Josh Johnson a trimis o soluție care s-a ocupat de ea. Când Josh a spus că folosește o coloană Index, am presupus că este ca Indexul și Modulo în Power Query: Number Groups of Records ca 1 la 5 în mod repetat. Dar utilizarea lui Josh a fost complet diferită.
Notă: MVP-ul Excel, John MacDougall, a folosit și această metodă, dar a concatenat coloana index până la sfârșitul descrierii categoriei. Vedeți videoclipul lui John aici: https://www.youtube.com/watch?v=Dqmb6SEJDXI și citiți mai multe despre codul său aici: MVP-urile Excel atacă problema de curățare a datelor în Power Query.
La începutul procesului, când Josh încă avea doar șase înregistrări, a adăugat un index începând cu 1. Josh a dat clic pe bara de formule și a redenumit coloana Index ca Categorie.
Coloana Categorie a fost noua ultimă coloană. El a folosit Move, to Beginning pentru a-l muta pentru a fi primul:
După aceasta, se întâmplă o mulțime de alți pași. Sunt pași care sunt inovatori, dar au fost acoperiți în cea mai mare parte în celelalte articole până acum. După mulți astfel de pași, începeam să cred că categoriile de la 1 la 6 erau doar o greșeală. Am crezut că, probabil, Josh avea să le șteargă fără să le folosească.
Josh Unpivots, apoi coloana condițională, apoi completează, apoi pivotează, adaugă totalul. Nu pare să folosească niciodată coloana Categorie respectivă. După mulți pași, el este aici:
Dar apoi, în ultimii pași, Josh sortează datele după numele angajatului, apoi pe categorie!
În acest moment, poate șterge coloana Categorie. Diferența finală: PTO vine înainte de Proiectul A, la fel ca în coloanele originale. Este o atingere frumoasă.
Voi sublinia, de asemenea, că Josh a trimis într-un videoclip cu el trecând prin acești pași. Felicitări lui Josh pentru utilizarea comenzilor rapide de la tastatură în Power Query!
Iată codul lui Josh:
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))), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Category", 1, 1), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",("Category", "Category Description", "Dept. Total", "Q1", "Q2", "Q3", "Q4", "Employee 1", "Q1_1", "Q2_2", "Q3_3", "Q4_4", "Employee 2", "Q1_5", "Q2_6", "Q3_7", "Q4_8", "Employee 3", "Q1_9", "Q2_10", "Q3_11", "Q4_12", "Employee 4", "Q1_13", "Q2_14", "Q3_15", "Q4_16")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category", "Category Description"), "Attribute", "Value"), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", (("Attribute", each Text.BeforeDelimiter(_, "_"), type text))), #"Added Conditional Column" = Table.AddColumn(#"Extracted Text Before Delimiter", "Employee Name", each if not Text.StartsWith((Attribute), "Q") then (Attribute) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee Name")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Attribute) = "Q1" or (Attribute) = "Q2" or (Attribute) = "Q3" or (Attribute) = "Q4") and ((Employee Name) "Dept. Total")), #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"(Attribute)), "Attribute", "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 Name", Order.Ascending), ("Category", Order.Ascending))), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",("Category")) in #"Removed Columns"Reveniți la pagina principală pentru provocarea Podcast 2316.
Citiți următorul articol din această serie: MVP-urile Excel atacă problema de curățare a datelor în Power Query.
