Power Query ist in Windows-Versionen von Office 365, Excel 2016, Excel 2019 integriert und steht in Windows-Versionen von Excel 2010 und Excel 2013 als kostenloser Download zur Verfügung. Das Tool dient zum Extrahieren, Transformieren und Laden von Daten in Excel aus a Vielzahl von Quellen. Das Beste daran: Power Query merkt sich Ihre Schritte und gibt sie wieder, wenn Sie die Daten aktualisieren möchten. Dies bedeutet, dass Sie Daten an Tag 1 in 80% der normalen Zeit bereinigen können, und Sie können Daten an Tag 2 bis 400 bereinigen, indem Sie einfach auf Aktualisieren klicken.
Ich sage dies über viele neue Excel-Funktionen, aber dies ist wirklich die beste Funktion, um Excel seit 20 Jahren zu erreichen.
In meinen Live-Seminaren erzähle ich eine Geschichte darüber, wie Power Query als Krücke für Kunden von SQL Server Analysis Services erfunden wurde, die gezwungen waren, Excel zu verwenden, um auf Power Pivot zuzugreifen. Aber Power Query wurde immer besser und jede Person, die Excel verwendet, sollte sich die Zeit nehmen, um Power Query zu lernen.
Holen Sie sich Power Query
Möglicherweise haben Sie bereits Power Query. Es befindet sich in der Gruppe Get & Transform auf der Registerkarte Data.
Wenn Sie sich jedoch in Excel 2010 oder Excel 2013 befinden, gehen Sie ins Internet und suchen Sie nach Download Power Query. Ihre Power Query-Befehle werden auf einer speziellen Power Query-Registerkarte in der Multifunktionsleiste angezeigt.
Bereinigen Sie Daten zum ersten Mal in Power Query
Um Ihnen ein Beispiel für die Großartigkeit von Power Query zu geben, geben Sie an, dass Sie jeden Tag die unten gezeigte Datei erhalten. Spalte A wird nicht ausgefüllt. Die Quartale werden nicht auf der Seite, sondern auf der anderen Seite angezeigt.
Speichern Sie diese Arbeitsmappe zunächst auf Ihrer Festplatte. Platzieren Sie es an einem vorhersehbaren Ort mit einem Namen, den Sie jeden Tag für diese Datei verwenden werden.
Wählen Sie in Excel Daten abrufen, Aus Datei, Aus Arbeitsmappe.
Navigieren Sie zur Arbeitsmappe. Klicken Sie im Vorschaufenster auf Sheet1. Anstatt auf Laden zu klicken, klicken Sie auf Bearbeiten. Sie sehen die Arbeitsmappe jetzt in einem etwas anderen Raster - dem Power Query-Raster.
Jetzt müssen Sie alle leeren Zellen in Spalte A reparieren. Wenn Sie dies in der Excel-Benutzeroberfläche tun möchten, lautet die unhandliche Befehlssequenz Start, Suchen & Auswählen, Gehe zu Spezial, Leerzeichen, Gleich, Aufwärtspfeil, Strg + Eingabetaste .
Wählen Sie in Power Query Transform, Fill, Down aus.
Alle Nullwerte werden durch den Wert von oben ersetzt. Bei Power Query sind drei statt sieben Klicks erforderlich.
Nächstes Problem: Die Quartiere gehen quer statt runter. In Excel können Sie dies mit einer Pivot-Tabelle für mehrere Konsolidierungsbereiche beheben. Dies erfordert 12 Schritte und mehr als 23 Klicks.
Wählen Sie in Power Query die beiden Spalten aus, die keine Viertel sind. Öffnen Sie die Dropdown-Liste "Spalten aufheben" auf der Registerkarte "Transformieren" und wählen Sie "Andere Spalten aufheben" (siehe unten).
Klicken Sie mit der rechten Maustaste auf die neu erstellte Attributspalte und benennen Sie sie in Viertel anstelle von Attribut um. Mehr als zwanzig Klicks in Excel werden zu fünf Klicks in Power Query.
Um fair zu sein, ist in Power Query nicht jeder Reinigungsschritt kürzer als in Excel. Das Entfernen einer Spalte bedeutet weiterhin, mit der rechten Maustaste auf eine Spalte zu klicken und Spalte entfernen zu wählen. Aber um ehrlich zu sein, handelt die Geschichte hier nicht von der Zeitersparnis an Tag 1.
Aber warten Sie: Power Query merkt sich alle Ihre Schritte
Schauen Sie auf der rechten Seite des Power Query-Fensters nach. Es gibt eine Liste mit dem Namen Angewandte Schritte. Es ist ein sofortiger Prüfpfad für alle Ihre Schritte. Klicken Sie auf ein Zahnradsymbol, um Ihre Auswahl in diesem Schritt zu ändern und die Änderungen durch die zukünftigen Schritte zu leiten. Klicken Sie auf einen Schritt, um zu sehen, wie die Daten vor diesem Schritt ausgesehen haben.
Wenn Sie mit dem Bereinigen der Daten fertig sind, klicken Sie wie unten gezeigt auf Schließen und Laden.
Trinkgeld
Wenn Ihre Daten mehr als 1.048.576 Zeilen umfassen, können Sie die Daten mithilfe der Dropdown-Liste Schließen und Laden direkt in das Power Pivot-Datenmodell laden, das 995 Millionen Zeilen aufnehmen kann, wenn auf dem Computer genügend Speicher installiert ist.
In wenigen Sekunden werden Ihre transformierten Daten in Excel angezeigt. Genial.
Die Auszahlung: Bereinigen Sie Daten morgen mit einem Klick
In der Power Query-Story geht es jedoch nicht um die Zeitersparnis an Tag 1. Wenn Sie die von Power Query zurückgegebenen Daten auswählen, wird auf der rechten Seite von Excel ein Bereich für Abfragen und Verbindungen angezeigt, auf dem sich eine Schaltfläche zum Aktualisieren befindet. (Wir benötigen hier eine Schaltfläche Bearbeiten. Da jedoch keine vorhanden ist, müssen Sie mit der rechten Maustaste auf die ursprüngliche Abfrage klicken, um die ursprüngliche Abfrage anzuzeigen oder zu ändern.)
Es macht Spaß, Daten an Tag 1 zu bereinigen. Ich liebe es, etwas Neues zu tun. Aber wenn mein Manager den resultierenden Bericht sieht und sagt: „Schön. Schaffst du das jeden Tag? " Ich hasse es schnell, jeden Tag den gleichen Datensatz zu bereinigen.
Um den 400. Tag der Datenbereinigung zu demonstrieren, habe ich die Originaldatei vollständig geändert. Neue Produkte, neue Kunden, kleinere Stückzahlen, mehr Zeilen, wie unten gezeigt. Ich speichere diese neue Version der Datei im selben Pfad und mit demselben Dateinamen wie die Originaldatei.
Wenn ich die Abfrage-Arbeitsmappe öffne und in wenigen Sekunden auf Aktualisieren klicke, meldet Power Query 92 statt 68 Zeilen.
Bereinigen der Daten an Tag 2, Tag 3, Tag 4,… Tag 400,… Tag Infinity dauert jetzt zwei Klicks.
In diesem Beispiel wird nur die Oberfläche von Power Query zerkratzt. Wenn Sie zwei Stunden mit dem Buch M is for (Data) Monkey von Ken Puls und Miguel Escobar verbringen, erfahren Sie mehr über andere Funktionen, wie z.
- Kombinieren aller Excel- oder CSV-Dateien aus einem Ordner in einem einzigen Excel-Raster
- Konvertieren einer Zelle mit Apfel, Banane, Kirsche, Dill und Aubergine in fünf Zeilen in Excel
- Durchführen eines VLOOKUP für eine Sucharbeitsmappe, während Sie Daten in Power Query bringen
- Aus einer einzelnen Abfrage eine Funktion machen, die auf jede Zeile in Excel angewendet werden kann
Eine vollständige Beschreibung von Power Query finden Sie unter M Is for (Data) Monkey von Ken Puls und Miguel Escobar. Bis Ende 2019 wird die zweite Ausgabe mit dem Titel Master Your Data verfügbar sein.
Vielen Dank an Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser und Colin Michael für die Nominierung von Power Query.
