Wie man Power Query in Microsoft Excel verwendet

Viele Menschen vermeiden Power Query, weil sie denken, es sei zu technisch, aber es ist tatsächlich eine der sichersten und einfachsten Methoden, um Ihre Daten zu organisieren. Es erledigt die schmutzige Arbeit mit einer intuitiven Benutzeroberfläche, zeichnet Ihre Klicks während des Vorgehens auf und automatisiert Ihren Arbeitsablauf, bevor Ihr Kaffee kalt wird. Wenn Sie also noch nie über grundlegende Excel-Formeln hinausgegangen sind, ist heute der Tag, um damit zu beginnen.

Erste Schritte: Zugriff auf die Power Query-Oberfläche

Verbinden Sie Ihre Daten, ohne etwas zu beschädigen

Power Query ist eine Datenumwandlungs-Engine, die in Excel integriert ist, mit der Sie Ihre Daten importieren, bereinigen und umgestalten können. Sie arbeitet auf einer separaten Abfrageversion Ihrer Daten, sodass Sie frei mit Ihren Zahlen spielen können, ohne sich Sorgen machen zu müssen, die Quelle zu erhalten.

Wenn Sie Power Query noch nie verwendet haben, ist der beste Weg, sich damit vertraut zu machen, eine Tabelle zu laden, die bereits in Ihrer aktuellen Arbeitsmappe enthalten ist. So beginnen Sie:

1. Wählen Sie eine beliebige Zelle innerhalb des Datensatzes aus und drücken Sie Strg+T (oder klicken Sie auf Tabelle im Einfügen-Tab), um Ihre Daten in eine Tabelle umzuwandeln.
2. Stellen Sie im Tabellenerstellen-Dialog sicher, dass Meine Tabelle hat Überschriften aktiviert ist, und klicken Sie dann auf OK.
3. Wählen Sie eine beliebige Zelle in der Tabelle aus, und benennen Sie sie im Tab Tabellendesign in T_Staff um und drücken Sie Enter.
4. Klicken Sie auf der Registerkarte Daten auf Aus Tabelle/Bereich, um den Power Query-Editor zu starten.

Eine Tour durch den Power Query-Editor

Machen Sie sich mit Ihrem neuen Datenlabor vertraut

Wenn der Power Query-Editor erscheint, lassen Sie sich nicht einschüchtern. Ja, es ist eine spezialisierte Umgebung, aber sie ist auf Effizienz ausgelegt. Und obwohl es Dutzende von Schaltflächen gibt, werden Sie wahrscheinlich die gleichen wenigen für 90 % Ihrer Arbeit verwenden.

Achten Sie auf diese Schnittstellenelemente:

1. Das Abfragefenster: Diese Seitenleiste listet alle Abfragen in Ihrer aktuellen Arbeitsmappe auf. Sie können schnell zwischen den verschiedenen Datensätzen wechseln, die Sie bereinigen.
2. Die Startseite: Dies ist Ihr primäres Dashboard. Es enthält die wesentliche Schaltfläche Schließen & Laden zum Beenden sowie einige andere gängige Werkzeuge.
3. Die Registerkarte "Transformieren": Verwenden Sie dies, wenn Sie eine vorhandene Datenspalte ändern müssen.
4. Die Registerkarte Spalte hinzufügen: Dies ist für den Fall, dass Sie Ihre ursprünglichen Daten behalten möchten, während Sie eine neue Spalte basierend darauf erstellen.
5. Die Registerkarte Ansicht: Sie können ändern, was Sie im Power Query-Editor sehen, indem Sie hier die Optionen umschalten.
6. Die Formelleiste: Dies zeigt die M-Formel, die durch Ihre Klicks erstellt wurde. Im Moment können Sie dies ignorieren - es sind nur die Notizen von Power Query in seiner eigenen Sprache.
7. Die Datenvorschau: Dies ist Ihr Live-Arbeitsbereich. Er zeigt eine Echtzeitdarstellung Ihrer Daten, während Sie sie bereinigen.
8. Die Abfrageeinstellungen: Hier geben Sie Ihrer Abfrage einen Namen und sehen die Schritte, die Sie bisher unternommen haben.

Während die Registerkarten alle Werkzeuge enthalten, die Sie benötigen, ist der schnellste Weg, in Power Query zu arbeiten, oft das Kontextmenü. Wenn Sie mit der rechten Maustaste auf eine Spaltenüberschrift in der Datenvorschau klicken, können Sie auf viele der am häufigsten verwendeten Werkzeuge zugreifen, ohne die Registerkarten oben durchsuchen zu müssen.

Hör auf, Formeln zu schreiben, und fang an, Tasten zu drücken

Lassen Sie das Menüband die komplexe Logik übernehmen

Power Query verwandelt alltägliche Befehle in einfache Klicks. Im Beispieldatensatz haben Namen inkonsistente Groß- und Kleinschreibung und zufällige Punkte, Abteilungsnamen haben lästige führende Leerzeichen, und Stadt und Bundesland sind in einer einzigen Spalte zusammengefasst. Wo man normalerweise komplexe Formeln verwenden würde, um diese Probleme zu beheben, kann Power Query sie viel einfacher handhaben.

Großschreibung korrigieren

1. Klicken Sie mit der rechten Maustaste auf die Spaltenüberschrift Vollständiger Name.
2. Klicken Sie auf Transformieren > Jedes Wort großschreiben.
3. Die Namen sind jetzt einheitlich großgeschrieben.

Ersetze die Punkte durch Leerzeichen

1. Klicken Sie auf die Überschrift der Spalte Vollständiger Name, um sie auszuwählen.
2. Klicken Sie im Transformieren-Tab auf Werte ersetzen.
3. Geben Sie im Feld Zu findender Wert einen Punkt ein und geben Sie im Feld Ersetzen durch ein einfaches Leerzeichen ein.
4. Wenn Sie auf OK klicken, werden alle Punkte durch Leerzeichen ersetzt.

Führende Leerzeichen entfernen

1. Klicken Sie mit der rechten Maustaste auf die Spalte Abteilung.
2. Klicken Sie auf Transformieren > Zuschneiden.
3. Alle unsichtbaren führenden (und nachfolgenden) Leerzeichen werden jetzt entfernt.

Stadt und Staat trennen

1. Wählen Sie die Spalte Stadt/Bundesland aus.
2. Klicken Sie auf der Registerkarte Transformieren auf Spalte teilen > Nach Trennzeichen.
3. Wählen Sie im Dropdown-Menü Benutzerdefiniert, geben Sie ein Komma gefolgt von einem Leerzeichen (, ) in das Textfeld ein und klicken Sie auf OK.
4. Doppelklicken Sie auf die Spaltenüberschriften, um sie in Stadt und Bundesland umzubenennen.

Laden Sie die bereinigten Daten wieder in Ihr Arbeitsblatt

Jetzt, da Ihre Daten aufbereitet sind, ist es an der Zeit, sie zurück in Ihre Excel-Arbeitsmappe zu verschieben. Dies ist der Moment, in dem die "Sandbox"-Arbeit von Power Query in eine nutzbare Excel-Tabelle übergeht.

Klicken Sie im Start-Tab auf die obere Hälfte der geteilten Schaltfläche Schließen & Laden. Power Query schließt den Editor und erstellt standardmäßig ein neues Tabellenblatt in Ihrer Arbeitsmappe, das die bereinigten Daten enthält.

Wenn Sie Ihrer Quelltabelle neue Zeilen hinzufügen, müssen Sie Power Query nicht erneut öffnen - klicken Sie einfach auf Alle aktualisieren im Daten-Tab, und Excel führt sofort jeden einzelnen Schritt mit den neuen Daten erneut aus.

Wenn Sie feststellen, dass Sie einen Schritt verpasst haben oder eine neue Transformation hinzufügen möchten, nachdem Sie die Daten bereits geladen haben, können Sie das Dataset im Power Query-Editor immer noch erneut öffnen. Klicken Sie auf Abfragen & Verbindungen im Daten-Tab, und doppelklicken Sie dann auf die Abfrage in der Seitenleiste.

Verwenden Sie das Menü 'Angewendete Schritte', um Ihre Änderungen zu prüfen und zu bearbeiten

Verabschieden Sie sich von der Rückgängig-Schaltfläche für eine bessere Verlaufshistorie

In standardmäßigem Excel ist Strg+Z ein lineares Rückgängig, das Aktionen eine nach der anderen in genau umgekehrter Reihenfolge rückgängig macht, in der sie ausgeführt wurden. Wenn Sie feststellen, dass Ihr drittletzter Schritt ein Fehler war, müssen Sie alles, was danach kam, rückgängig machen, um es zu korrigieren. Power Query ersetzt dies durch den Abschnitt "Angewendete Schritte" im Bereich "Abfrageeinstellungen" - eine Rezeptkarte, die jeden von Ihnen ausgeführten Schritt aufzeichnet.

1. Wählen Sie einen beliebigen Schritt aus, um zu sehen, wie die Daten zu diesem genauen Zeitpunkt aussahen, und klicken Sie auf das X, um diese bestimmte Aktion zu löschen.
2. Klicken Sie mit der rechten Maustaste auf einen Schritt und wählen Sie Umbenennen, um ihm eine klare Bezeichnung zu geben, oder Eigenschaften, um eine detailliertere Beschreibung hinzuzufügen.

Aufleveln: Power Query auf die nächste Stufe bringen

Jetzt, da Sie die Grundlagen des Bereinigens einer einzelnen Tabelle gemeistert haben, haben Sie eine ganz neue Welt von Möglichkeiten zur Datenbereinigung eröffnet. Sie müssen keine Angst mehr haben, Ihre Daten zu beschädigen, und Sie haben gesehen, wie Power Query in nur wenigen Klicks eine bessere Arbeit leisten kann als eine Formel in gewöhnlichem Excel.

Aber Sie müssen hier nicht aufhören. Wenn Sie bereit sind, einen Schritt weiterzugehen, verwenden Sie die gleichen Prinzipien, um noch größere Aufgaben zu bewältigen. Zum Beispiel erlaubt Ihnen Power Query, Daten aus mehreren Blättern zu kombinieren, um Hauptberichte zu erstellen, oder Datensätze aus mehreren Arbeitsmappen zu verbinden, um Ihre Datensammlung vollständig zu automatisieren. Je mehr Sie Power Query nutzen, desto mehr werden Sie sich fragen, wie Sie Excel je ohne es bewältigt haben.