Hinweis
Dies ist einer aus einer Reihe von Artikeln, in denen Lösungen für die Podcast 2316-Herausforderung aufgeführt sind.
Eines der Probleme bei meiner Lösung ist, dass die endgültige Reihenfolge der Kategorien nicht unbedingt mit der ursprünglichen Reihenfolge der Spalten übereinstimmt. Ich habe das ganz am Ende meines Videos bemerkt und da es nicht besonders wichtig war, habe ich mir darüber keine Sorgen gemacht.
Josh Johnson schickte jedoch eine Lösung, die sich darum kümmerte. Als Josh sagte, er habe eine Indexspalte verwendet, nahm ich an, dass sie wie Index und Modulo in Power Query: Number Groups of Records wiederholt als 1 bis 5 war. Aber Joshs Gebrauch war völlig anders.
Hinweis: Excel MVP John MacDougall verwendete diese Methode ebenfalls, verkettete jedoch die Indexspalte bis zum Ende der Kategoriebeschreibung. Sehen Sie Johns Video hier: https://www.youtube.com/watch?v=Dqmb6SEJDXI und lesen Sie hier mehr über seinen Code: Excel-MVPs greifen das Datenbereinigungsproblem in Power Query an.
Zu Beginn des Prozesses, als Josh noch nur sechs Datensätze hatte, fügte er einen Index ab 1 hinzu. Josh klickte in die Formelleiste und benannte die Indexspalte in Kategorie um.
Die Kategorie-Spalte war die neue letzte Spalte. Er benutzte Move, um zu beginnen, es als erstes zu verschieben:
Danach passieren viele andere Schritte. Dies sind Schritte, die innovativ sind, aber bisher hauptsächlich in den anderen Artikeln behandelt wurden. Nach vielen solchen Schritten begann ich zu denken, dass die Kategorienummern 1 bis 6 nur ein Fehler waren. Ich dachte, dass Josh sie möglicherweise löschen würde, ohne sie zu benutzen.
Josh Unpivots, dann bedingte Spalte, dann Ausfüllen, dann Pivots, addiert die Summe. Er scheint diese Kategoriespalte nie zu benutzen. Nach vielen Schritten ist er hier:
Aber in den letzten Schritten sortiert Josh die Daten nach Mitarbeiternamen und dann nach Kategorie!
Zu diesem Zeitpunkt kann er die Spalte Kategorie löschen. Der letzte Unterschied: PTO kommt vor Projekt A, genau wie in den ursprünglichen Spalten. Es ist eine nette Geste.
Ich werde auch darauf hinweisen, dass Josh ein Video gesendet hat, in dem er diese Schritte durchläuft. Ein großes Lob an Josh für die Verwendung von Tastaturkürzeln in Power Query!
Hier ist Joshs Code:
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"Kehren Sie zur Hauptseite für die Podcast 2316-Herausforderung zurück.
Lesen Sie den nächsten Artikel in dieser Reihe: Excel-MVPs greifen das Datenbereinigungsproblem in Power Query an.
