Opomba
To je eden iz serije člankov, ki podrobno opisujejo rešitve, poslane za izziv Podcast 2316.
Ena od težav z mojo rešitvijo je, da se končno zaporedje kategorij ni nujno ujemalo z izvirnim zaporedjem stolpcev. To sem spoznal čisto na koncu svojega videoposnetka in ker ni bil posebej pomemben, me ni skrbelo.
Vendar je Josh Johnson poslal rešitev, ki jo je obravnavala. Ko je Josh rekel, da je uporabil stolpec Indeks, sem domneval, da je večkrat indeks in modul v Power Query: Številčne skupine zapisov od 1 do 5. Toda Josh je bil povsem drugačen.
Opomba: Tudi Excel MVP John MacDougall je uporabil to metodo, vendar je stolpec indeksa združil na konec opisa kategorije. Oglejte si Johnov video tukaj: https://www.youtube.com/watch?v=Dqmb6SEJDXI in preberite več o njegovi kodi tukaj: Excel MVP-ji napadajo problem čiščenja podatkov v Power Query.
Na začetku postopka, ko je imel Josh še le šest zapisov, je dodal indeks, ki se je začel pri 1. Josh je kliknil v vrstico s formulami in stolpec Indeks preimenoval v Kategorija.
Stolpec Kategorija je bil novi zadnji stolpec. Uporabil je Premakni, da Začetek premakne, da bo prvi:
Po tem se zgodi še veliko drugih korakov. Gre za inovativne korake, ki pa so bili doslej večinoma zajeti v drugih člankih. Po številnih takih korakih sem začel razmišljati, da so kategorije od 1 do 6 samo napaka. Mislil sem, da jih bo Josh morda izbrisal, ne da bi jih uporabil.
Josh Unpivots, nato pogojni stolpec, nato izpolnite navzdol, nato pivoti, doda skupni znesek. Zdi se, da tega stolpca kategorije nikoli ne uporablja. Po številnih korakih je tu:
Toda v zadnjih korakih Josh podatke razvrsti po imenu zaposlenega in nato po kategoriji!
V tem trenutku lahko izbriše stolpec Kategorija. Končna razlika: PTO je pred projektom A, tako kot v prvotnih stolpcih. Lep dotik.
Poudaril bom tudi, da je Josh poslal videoposnetek, kako gre skozi te korake. Pohvale Joshu za uporabo bližnjic na tipkovnici v programu Power Query!
Tu je Josh-ova 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"Vrnite se na glavno stran izziva Podcast 2316.
Preberite naslednji članek v tej seriji: Excel MVP-ji v Power Query napadajo problem čiščenja podatkov.
