Wenn ich ein Live-Power-Excel-Seminar mache, biete ich an, dass jeder im Raum, der jemals ein seltsames Excel-Problem hat, es mir zur Hilfe schicken kann. Auf diese Weise erhielt ich dieses Problem mit der Datenbereinigung. Jemand hatte ein zusammenfassendes Arbeitsblatt, das so aussieht:
Sie wollten die Daten neu formatieren, um so auszusehen:
Ein interessanter Hinweis zu diesen Daten: Die 18 in G4 scheint eine Zwischensumme von H4: K4 zu sein. Es ist verlockend, die Spalten G, L usw. zu entfernen, aber zuerst müssen Sie den Mitarbeiternamen aus G3, L3 usw. extrahieren.
Es war 4 Uhr morgens am Sonntag, dem 9. Februar, als ich den Videorecorder einschaltete und einige klobige Schritte in Power Query aufzeichnete, um das Problem zu lösen. Da es Sonntag war, ein Tag, an dem ich normalerweise keine Videos mache, bat ich die Leute, ihre Ideen zur Lösung des Problems einzusenden. 29 Lösungen wurden eingesandt.
Jede Lösung bietet eine coole neue Verbesserung gegenüber meinem Prozess. Mein Plan ist es, eine Reihe von Artikeln zu starten, die die verschiedenen Verbesserungen meiner Methode zeigen.
Schau Video
Bevor ich diesen Prozess beginne, lade ich Sie ein, meine Lösung zu sehen:
Und der M-Code, den Power Query für mich generiert hat:
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))), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", ("Category Description"), "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter(("_"), QuoteStyle.Csv, false), ("Attribute.1", "Attribute.2")), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",(("Attribute.1", type text), ("Attribute.2", Int64.Type))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",(("Attribute.1", "TextValue"))), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Length", each Text.Length((TextValue))), #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Name", each if (Length)> 2 then (TextValue) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Name")), #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",("Category Description", "Name", "TextValue", "Value", "Length")), #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ((TextValue) = "Q1" or (TextValue) = "Q2" or (TextValue) = "Q3" or (TextValue) = "Q4")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each (Name) "Dept. Total"), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",("Length")), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"(TextValue)), "TextValue", "Value", List.Sum), #"Sorted Rows" = Table.Sort(#"Pivoted Column",(("Name", Order.Ascending))), #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Total", each (Q1)+(Q2)+(Q3)+(Q4)) in #"Added Custom1"Bevor wir uns mit den Lösungen befassen, wollen wir uns mit vielen allgemeinen Kommentaren befassen:
- Einige von Ihnen sagten, Sie würden rückwärts gehen, um herauszufinden, warum die Daten zunächst in diesem Format angezeigt werden. Ich freue mich über diese Kommentare. Jeder, der das gesagt hat, ist ein besserer Mensch als ich. Ich habe im Laufe der Jahre gelernt, dass, wenn Sie fragen "Warum?" Die Antwort betrifft normalerweise diesen ehemaligen Mitarbeiter, der diesen Weg vor 17 Jahren eingeschlagen hat, und jeder nutzt ihn weiterhin so, da wir jetzt alle daran gewöhnt sind.
- Außerdem - viele von Ihnen - sagten, die endgültige Lösung sollte ein hoher vertikaler Tisch sein und dann einen Pivot-Tisch verwenden, um die endgültigen Ergebnisse zu erzielen. Jonathan Cooper fasste dies am besten zusammen: "Ich stimme auch einigen anderen YouTube-Kommentaren zu, dass ein richtiger Datensatz keine" Summen "enthalten würde und am Ende nicht geschwenkt werden müsste. Aber wenn der Nutzer wirklich eine Ebene will alter Tisch, dann gibst du ihnen, was sie wollen. " Ich kann tatsächlich beide Seiten davon sehen. Ich liebe einen Pivot-Tisch und das einzige, was mehr Spaß macht als Power Query, ist Power Query mit einem schönen Pivot-Tisch darüber. Aber wenn wir das Ganze in Power Query machen können, dann ist eine Sache weniger zu brechen.
Hier finden Sie Hyperlinks zu verschiedenen Techniken
-
Power Query-Techniken
- Nummerierung von Datensatzgruppen
- Links zwei Zeichen extrahieren
- Gesamtspalte
- Sonst wenn Klauseln
- Mehrere identische Header in Power Query
- Was zu löschen ist
- Geteilt durch Q.
- Werbebuchungen sortieren
- Power Query-Lösungen von Excel-MVPs
-
Über die Power Query-Schnittstelle hinausgehen
- Table.Split
- Die Welt von Bill Szysz
-
Formellösungen
- Eine dynamische Array-Formel
- Hilfsspalten der alten Schule
- Formellösungen
-
Zusammengesetzt aus allen Ideen von oben und dem endgültigen Video
- Zusammenstellung der besten Ideen von allen
