Piezīme
Šis ir viens no rakstu sērijas, kurā sīki aprakstīti Podcast 2316 izaicinājumam nosūtītie risinājumi.
Viena no problēmām ar manu risinājumu ir tā, ka kategoriju galīgā secība ne vienmēr sakrīt ar sākotnējo kolonnu secību. Es to sapratu sava videoklipa pašās beigās, un, tā kā tas nebija īpaši svarīgi, es par to neuztraucos.
Tomēr Džošs Džonsons nosūtīja risinājumu, kas ar to tika galā. Kad Džošs teica, ka viņš izmantoja sleju Indekss, es pieņēmu, ka tas ir tāds pats kā Indekss un Modulo Power Query: Ierakstu grupas atkārtoti numurē no 1 līdz 5. Bet Džoša izmantošana bija pilnīgi atšķirīga.
Piezīme: Excel MVP Džons Makdugals izmantoja arī šo metodi, taču indeksu kolonnu viņš saīsināja līdz kategorijas apraksta beigām. Skatiet Jāņa videoklipu šeit: https://www.youtube.com/watch?v=Dqmb6SEJDXI un lasiet vairāk par viņa kodu šeit: Excel MVP Attack Data Cleansing Problem in Power Query.
Procesa sākumā, kad Džošam joprojām bija tikai seši ieraksti, viņš pievienoja indeksu, kas sākas ar 1. Džošs noklikšķināja formulu joslā un pārdēvēja Indeksa kolonnu kā Kategorija.
Kolonna Kategorija bija jaunā pēdējā kolonna. Viņš izmantoja pārvietošanos līdz sākumam, lai to pārvietotu kā pirmo:
Pēc tam notiek daudz citu darbību. Tie ir soli, kas ir novatoriski, bet līdz šim galvenokārt ir apskatīti citos rakstos. Pēc daudzām šādām darbībām es sāku domāt, ka kategoriju numuri no 1 līdz 6 bija tikai kļūda. Es domāju, ka, iespējams, Džošs tos izdzēsīs, tos neizmantojot.
Josh Unpivots, tad nosacītā kolonna, pēc tam aizpildiet, pēc tam pagriežot, tiek pievienota kopsumma. Šķiet, ka viņš nekad neizmanto šo kategorijas sleju. Pēc daudziem soļiem viņš ir šeit:
Bet tad pēdējos soļos Džošs sakārto datus pēc darbinieka vārda un pēc tam pēc kategorijas!
Šajā brīdī viņš var izdzēst kolonnu Kategorija. Pēdējā atšķirība: PTO ir pirms projekta A, tāpat kā tas bija sākotnējās slejās. Tas ir jauks pieskāriens.
Es arī norādīšu, ka Džošs iesūtīja video, kurā viņš redz šos soļus. Gods Džošam par īsinājumtaustiņu izmantošanu Power Query iekšpusē!
Šeit ir Džoša kods:
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"Atgriezieties uz Podcast 2316 izaicinājuma galveno lapu.
Izlasiet nākamo šīs sērijas rakstu: Excel MVP uzbrukums datu tīrīšanas problēmai Power Query.
