Bilješka
Ovo je jedan od niza članaka koji detaljno opisuju rješenja poslana za izazov Podcast 2316.
Jedan od problema s mojim rješenjem je taj što konačni slijed kategorija nije nužno odgovarao izvornom slijedu stupaca. Shvatila sam to na samom kraju svog videa, a kako to nije bilo posebno važno, nisam se brinula zbog toga.
Međutim, Josh Johnson poslao je rješenje koje je to riješilo. Kad je Josh rekao da koristi indeksni stupac, pretpostavio sam da je to više puta indeks i modul u Power Queryju: Brojčane skupine zapisa kao 1 do 5. Ali Joshova je upotreba bila potpuno drugačija.
Napomena: I Excel MVP John MacDougall također je koristio ovu metodu, ali je indeksni stupac povezao na kraj opisa kategorije. Pogledajte Johnov video ovdje: https://www.youtube.com/watch?v=Dqmb6SEJDXI, a više o njegovom kodu pročitajte ovdje: Excel MVP-ovi napadaju problem čišćenja podataka u Power Queryju.
Početkom procesa, kada je Josh još imao samo šest zapisa, dodao je indeks počevši od 1. Josh je kliknuo na traku s formulama i stupac Indeks preimenovao u Kategorija.
Stupac Kategorija bio je novi posljednji stupac. Koristio je Premjesti, da bi Početak premjestio da bude prvi:
Nakon ovoga događa se puno drugih koraka. To su koraci koji su inovativni, ali su do sada uglavnom obrađivani u ostalim člancima. Nakon mnogih takvih koraka počeo sam misliti da su kategorije brojevi od 1 do 6 samo pogreška. Mislila sam da će ih Josh možda izbrisati bez da ih koristi.
Josh Unpivots, zatim uvjetni stupac, zatim popunite dolje, pa pivoti, dodaje zbroj. Čini se da se nikad ne koristi tim stupcem Kategorija. Nakon mnogo koraka, on je ovdje:
Ali onda u posljednjim koracima Josh sortira podatke prema imenu zaposlenika, a zatim prema kategoriji!
U ovom trenutku može izbrisati stupac Kategorija. Konačna razlika: PTO dolazi prije Projekta A, baš kao što je bio u izvornim stupcima. To je lijep dodir.
Također ću naglasiti da je Josh poslao video kako prolazi kroz ove korake. Svaka čast Joshu za korištenje tipkovnih prečaca unutar Power Queryja!
Evo Josh-ovog koda:
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"Povratak na glavnu stranicu za izazov Podcast 2316.
Pročitajte sljedeći članak iz ove serije: Excel MVP-ovi napadaju problem čišćenja podataka u Power Queryju.
