Hinweis
Dies ist einer aus einer Reihe von Artikeln, in denen Lösungen für die Podcast 2316-Herausforderung aufgeführt sind.
Bei meinem ursprünglichen Datenformungsproblem bin ich sehr früh auf ein Problem gestoßen. Die eingehenden Daten sollten viele Spalten mit der Überschrift Q1 enthalten.
In meiner Lösung habe ich einen benannten Bereich "UglyData" erstellt und diesen in Power Query importiert. Dies führte zu dem unglücklichen Ergebnis, dass Power Query meine Spalten in Q1_1 umbenannte.
Später, nachdem ich mich nicht mehr gedreht hatte, musste ich nur die beiden linken Zeichen aus diesen Überschriften extrahieren.
Es gab drei verschiedene Lösungen für dieses Problem:
- Wyn Hopkins und Demote Header
- MF Wong und deaktivieren Sie "Mein Tisch hat Überschriften" (auch von Peter Bartholomew vorgeschlagen).
- Jason M und einfach gesponserte Header löschen (auch vorgeschlagen von Ondřej Malinský und Excel MVP John MacDougall)
Die erste Innovation stammte von Wyn Hopkins von Access Analytic. Anstelle eines benannten Bereichs konvertierte Wyn die Daten mit Strg + T in eine Tabelle. Zu diesem Zeitpunkt wurde der Schaden an den Überschriften verursacht, da Excel die Überschriften in Folgendes konvertierte:
Nachdem Wyn die Daten in Power Query aufgenommen hatte, öffnete er das Dropdown-Menü Erste Zeile als Überschriften verwenden und wählte Überschriften als erste Zeile verwenden. Ich habe nie bemerkt, dass das da ist. Es wird ein Schritt namens Table.DemoteHeaders erstellt.
Aber selbst mit Wyns Verbesserung müsste er später noch die ersten beiden Zeichen aus diesen Überschriften extrahieren.
Die zweite Neuerung ist die Technik von MF Wong. Als er die Tabelle erstellt hat, hat er das Kontrollkästchen Meine Tabelle hat Überschriften deaktiviert!
Dadurch wird sichergestellt, dass Excel die mehreren Q1-Header in Ruhe lässt und das zusätzliche Suffix später nicht extrahiert werden muss.
Ich verstehe, dass es Leute im Lager „Ich liebe Tische“ gibt. Das Video von MF Wong zeigte, wie er rechts von den Daten neue Mitarbeiter hinzufügen kann, und die Tabelle wird automatisch erweitert. Es gibt viele gute Gründe, Tabellen zu verwenden.
Da ich Zwischensummen, benutzerdefinierte Ansichten und Filter nach Auswahl mag, verwende ich normalerweise keine Tabellen. Daher schätze ich die Lösung von Jason M. Er hat die Daten als benannten Bereich von UglyData beibehalten. Sobald er die Daten in Power Query importiert hatte, löschte er diese beiden Schritte:
Mit den Daten in Zeile 1 ist es kein Problem, viele Spalten mit der Bezeichnung Q1 zu haben.
Hier ist Wyn Hopkins Code, der DemotedHeaders zeigt:
let Source = Excel.CurrentWorkbook()((Name="Table1"))(Content), #"Demoted Headers1" = Table.DemoteHeaders(Source), #"Transposed Table1" = Table.Transpose(#"Demoted Headers1"), #"Added Custom" = Table.AddColumn(#"Transposed Table1", "Custom", each if Text.Start((Column1),1) = "Q" then null else (Column1)), #"Filled Down" = Table.FillDown(#"Added Custom",("Custom")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Custom) "Dept. Total")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.StartsWith((Column1), "Employee")), #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", (PromoteAllScalars=true)), #"Extracted First Characters" = Table.TransformColumns(#"Promoted Headers", (("Category Description", each Text.Start(_, 2), type text))), #"Reordered Columns" = Table.ReorderColumns(#"Extracted First Characters",("Category Description_1", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category Description_1", "Category Description"), "Attribute", "Value"), #"Reordered Columns1" = Table.ReorderColumns(#"Unpivoted Other Columns",("Category Description_1", "Attribute", "Category Description", "Value")), #"Pivoted Column" = Table.Pivot(#"Reordered Columns1", List.Distinct(#"Reordered Columns1"(#"Category Description")), "Category Description", "Value", List.Sum), #"Reordered Columns2" = Table.ReorderColumns(#"Pivoted Column",("Attribute", "Category Description_1", "Q1", "Q2", "Q3", "Q4")), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns2",(("Attribute", "Cat Deasc"), ("Category Description_1", "Emp Name"))), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",(("Emp Name", type text), ("Q1", Int64.Type), ("Q2", Int64.Type), ("Q3", Int64.Type), ("Q4", Int64.Type))), #"Inserted Sum" = Table.AddColumn(#"Changed Type", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), Int64.Type) in #"Inserted Sum"Kehren Sie zur Hauptseite für die Podcast 2316-Herausforderung zurück.
Lesen Sie den nächsten Artikel in dieser Reihe: Power Query: Löschen Sie diese, löschen Sie diese oder löschen Sie nichts?.
