Excel-MVPs greifen das Problem der Datenbereinigung in Power Query an - Excel-Tipps

Inhaltsverzeichnis

Hinweis

Dies ist einer aus einer Reihe von Artikeln, in denen Lösungen für die Podcast 2316-Herausforderung aufgeführt sind.

Excel MVP Oz Du Soleil vom Excel on Fire-Kanal auf YouTube erwähnte den brasilianischen Bullenreiter Kaique Pachecho. Oz war der erste, der bemerkte, dass ich den langsamen Weg ging, um die vier Viertel hinzuzufügen.

Das Video von Oz lautet:
https://www.youtube.com/watch?v=OluZlF44PNI

Sein Code lautet:

let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Removed Columns" = Table.RemoveColumns(Source,("Column2", "Column3", "Column4", "Column5", "Column6")), #"Transposed Table" = Table.Transpose(#"Removed Columns"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Administrative", type number), ("Holiday", Int64.Type), ("PTO/LOA/Jury Duty", Int64.Type), ("Project A", type number), ("Project B", type number), ("Project C", type number))), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if (Category Description) = "Q1" then null else if (Category Description) = "Q2" then null else if (Category Description) = "Q3" then null else if (Category Description) = "Q4" then null else (Category Description)), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Custom")), #"Renamed Columns" = Table.RenameColumns(#"Filled Down",(("Custom", "Names"))), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each (Category Description) = "Q1" or (Category Description) = "Q2" or (Category Description) = "Q3" or (Category Description) = "Q4"), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",("Names", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Names", "Category Description"), "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"(#"Category Description")), "Category Description", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Addition", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Renamed Columns1" = Table.RenameColumns(#"Inserted Sum",(("Addition", "TOTAL"))) in #"Renamed Columns1"

Eine andere Lösung, diese von Excel MVP John MacDougall.

  • John war der erste, der sagte, dass Sie durch Löschen der beiden zusätzlichen Schritte, die Power Query hinzugefügt hat, die ungeraden Suffixe in den doppelten Überschriften Q1 Q2 Q3 Q4 entfernen.
  • John verwendete früh eine Indexspalte, die am Ende zum Sortieren verwendet wurde. Aber - John hat seine Indexspalte nach der Kategoriebeschreibung verkettet. Er benutzte ein vertikales Rohrzeichen | damit er die Daten später herausbrechen konnte.
  • John gab seine bedingte Spalte als benutzerdefinierte Spalte ein, anstatt die Schnittstelle für bedingte Spalten zu verwenden.
Bedingte Spalte als benutzerdefinierte Spalte

Sehen Sie sich Johns Video hier an:
https://www.youtube.com/watch?v=Dqmb6SEJDXI

Excel MVP Ken Puls, Co-Autor des M is for (Data) Monkey-Buches, wird in drei Lösungen gesendet. Seine bedingte Kolumne ist wahrscheinlich die kürzeste.

Aber Kens bevorzugte Lösung ignoriert die ursprüngliche Frage. Anstatt die Tabelle in Power Query zu erstellen, erstellt er einen schwenkbaren Datensatz in Power Query und schließt mit einer Pivot-Tabelle ab.

Kens endgültige Vorschau in Power Query sieht folgendermaßen aus:

Schwenkbarer Datensatz

Hier ist Kens 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))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",("Dept. Total", "Q1", "Q2", "Q3", "Q4")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", ("Category Description"), "Attribute", "Value"), #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Employee", each if Text.Contains((Attribute), "_") then null else (Attribute)), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee")), #"Split Column by Delimiter" = Table.SplitColumn(#"Filled Down", "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))), #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ((Attribute.2) null)), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",(("Attribute.1", "Quarter"), ("Value", "Amount"))), #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",(("Category Description", type text), ("Quarter", type text), ("Amount", type number), ("Employee", type text))) in #"Changed Type2"

Nachdem er diese Abfrage nur als Verbindung erstellt hat, verwendet er eine Pivot-Tabelle, um den Abschlussbericht zu erstellen.

Abschlussbericht mit Pivot Table

Lösungen von anderen MVPs:

  • Der Wyn Hopkins-Code ist hier: Power Query: Umgang mit mehreren identischen Headern.
  • Der Code von Mike Girvin ist hier: Power Query: Extrahieren von 2 linken Zeichen aus einer Spalte.
  • Roger Goviers Formellösung ist hier: Formellösungen.

Kehren Sie zur Hauptseite für die Podcast 2316-Herausforderung zurück.

Lesen Sie den nächsten Artikel in dieser Reihe: Power Query: Jenseits der Benutzeroberfläche: Table.Split and More.

Interessante Beiträge...