Power Query: Daten importieren und transformieren wie ein Profi

Wenn Sie jede Woche Stunden damit verbringen, CSV-Dateien herunterzuladen, leere Zeilen zu löschen, Daten zu formatieren und komplexe verschachtelte Formeln zu schreiben, nur um Ihre Daten für die Analyse vorzubereiten, arbeiten Sie härter als nötig. Willkommen bei Power Query – dem mit Abstand leistungsstärksten Tool zur Datenautomatisierung, das direkt in Microsoft Excel integriert ist.

Oft als "Abrufen und transformieren" bezeichnet, ermöglicht Ihnen Power Query, sich mit fast jeder Datenquelle zu verbinden, die Informationen zu bereinigen, umzuwandeln und in Ihre Tabelle zu laden. Das Beste daran? Es zeichnet Ihre Schritte auf. Wenn Sie das nächste Mal neue Daten erhalten, müssen Sie die manuelle Arbeit nicht wiederholen; Sie klicken einfach auf Aktualisieren.

In diesem umfassenden Leitfaden werden wir untersuchen, was Power Query ist, wie Sie auf der Benutzeroberfläche navigieren, und wir gehen ein praktisches Beispiel durch, bei dem ein unordentlicher Datensatz in saubere, analysebereite Informationen transformiert wird.

Was ist Power Query?

Power Query ist eine Engine für Datenverbindungen und -aufbereitung. In der Welt der Datenbankverwaltung ist dieser Prozess als ETL bekannt: Extract, Transform, and Load (Extrahieren, Transformieren, Laden).

• Extrahieren: Daten aus verschiedenen Quellen abrufen (Excel-Arbeitsmappen, CSV- und Textdateien, Webseiten, SQL-Datenbanken oder sogar Cloud-Diensten).
• Transformieren: Die Daten bereinigen und umwandeln. Dies umfasst das Filtern von Zeilen, Ändern von Datentypen, Teilen von Text, Zusammenführen von Tabellen und Erstellen berechneter Spalten.
• Laden: Die aufbereiteten Daten direkt in eine Excel-Tabelle, eine PivotTable oder das Power Pivot-Datenmodell ausgeben.

Traditionell verließen sich Excel-Benutzer auf eine Kombination aus Funktionen wie TRIM, PROPER, SUBSTITUTE und VLOOKUP, verbunden mit manuellem Kopieren und Einfügen, um diese Aufgaben zu bewältigen. Power Query ersetzt diesen mühsamen Workflow durch eine visuelle, benutzerfreundliche Oberfläche.

Warum Sie Power Query lernen sollten

Wenn Sie noch zögern, ein neues Excel-Tool zu erlernen, sind hier die Gründe, warum die Beherrschung von Power Query ein Meilenstein für Ihre Produktivität sein wird:

• Keine Programmierung erforderlich: Obwohl Power Query auf einer Formelsprache namens "M" basiert, können Sie 95 % der Datentransformationen nur mit den Schaltflächen im Menüband und den Rechtsklick-Menüs durchführen.
• Automatisierung ohne VBA: Sie müssen keine Makros schreiben können, um sich wiederholende Aufgaben zu automatisieren. Power Query merkt sich Ihre "Angewendeten Schritte" und wendet sie automatisch auf neue Daten an.
• Bewältigt große Datenmengen (Big Data): Excel hat ein physisches Limit von 1.048.576 Zeilen. Power Query kann sich mit Datensätzen verbinden, die Millionen von Zeilen enthalten, die Daten im Hintergrund aggregieren und nur die zusammengefassten Ergebnisse in Ihre Tabelle laden.
• Zerstörungsfreie Bearbeitung: Power Query verändert niemals Ihre ursprüngliche Datenquelle. Es erstellt eine Verbindung, liest die Daten und gibt eine frisch bereinigte Kopie aus.

Erste Schritte: Der Power Query-Editor

Um auf Power Query zuzugreifen, öffnen Sie eine leere Excel-Arbeitsmappe und navigieren Sie zum Tab Daten im Menüband. Suchen Sie ganz links nach der Gruppe Daten abrufen und transformieren.

Von hier aus können Sie auf Daten abrufen klicken, um ein Dropdown-Menü der verfügbaren Datenquellen zu sehen. Sobald Sie eine Datei auswählen und auf "Daten transformieren" klicken, öffnet Excel den Power Query-Editor in einem neuen Fenster. Diese Benutzeroberfläche besteht aus vier Hauptbereichen:

1. Das Menüband: Befindet sich oben und enthält Tabs wie Start, Transformieren, Spalte hinzufügen und Ansicht. Hier befinden sich all Ihre Werkzeuge.
2. Der Abfragenbereich: Befindet sich auf der linken Seite und listet alle aktiven Datenverbindungen in Ihrer Arbeitsmappe auf.
3. Die Datenvorschau: Das Raster in der Mitte zeigt eine Live-Vorschau dessen, wie Ihre Daten beim aktuell ausgewählten Schritt aussehen.
4. Der Bereich Abfrageeinstellungen (Angewendete Schritte): Befindet sich auf der rechten Seite. Dies ist der wichtigste Teil des Editors. Jedes Mal, wenn Sie etwas ändern, zeichnet Power Query dies hier als fortlaufenden Schritt auf. Sie können diese Schritte jederzeit löschen, neu anordnen oder bearbeiten.

Schritt-für-Schritt-Anleitung: Unordentliche Verkaufsdaten bereinigen

Schauen wir uns ein praktisches Beispiel aus der realen Welt an. Stellen Sie sich vor, Sie exportieren einen wöchentlichen Verkaufsbericht aus dem CRM Ihres Unternehmens. Der Roh-Export ist unordentlich und enthält unnötige Kopfzeilen, kombinierte Textzeichenfolgen und inkonsistente Formatierungen.

Hier ist ein Muster unserer unordentlichen Rohdaten:

Systemexport: Q3 Verkaufsbericht	Column2	Column3
Generated on: 10/01/2023
Rep_ID_Name	Order_Date	Revenue
101-John Doe	2023-08-15	1500.5
102-Jane Smith	09/01/2023	$2,340.00
103-Bob_Jones	23-Sep-2023	850.75

Wenn wir traditionelle Formeln verwenden würden, müssten wir LEFT, RIGHT, FIND und VALUE verwenden, um die Namen der Vertreter zu extrahieren und die Zahlen zu korrigieren. Lassen Sie uns stattdessen Power Query verwenden.

Schritt 1: Daten importieren

Speichern Sie die unordentlichen Daten als CSV- oder Excel-Datei. Öffnen Sie eine neue Excel-Arbeitsmappe, gehen Sie zu Daten > Daten abrufen > Aus Datei und wählen Sie Ihre Datei aus. Wenn das Vorschaufenster erscheint, klicken Sie auf Daten transformieren. Der Power Query-Editor wird geöffnet.

Schritt 2: Oberste Zeilen entfernen und Kopfzeilen hochstufen

Die ersten beiden Zeilen unserer Daten sind Metadaten des Systemexports und keine tatsächlichen Datensätze. Wir müssen sie loswerden.

• Klicken Sie auf dem Tab Start auf Zeilen entfernen > Erste Zeilen entfernen. Geben Sie "2" ein und klicken Sie auf OK.
• Nun befinden sich unsere eigentlichen Spaltenüberschriften in Zeile 1. Klicken Sie auf die Schaltfläche Erste Zeile als Kopfzeilen verwenden auf dem Tab Start. Sofort werden "Rep_ID_Name", "Order_Date" und "Revenue" zu Ihren offiziellen Spaltentiteln.

Schritt 3: Spalte mit Vertretern aufteilen

Die Spalte "Rep_ID_Name" enthält sowohl die ID-Nummer als auch den Namen des Mitarbeiters, getrennt durch einen Bindestrich.

• Klicken Sie mit der rechten Maustaste auf die Spaltenüberschrift "Rep_ID_Name".
• Wählen Sie Spalte teilen > Nach Trennzeichen.
• Wählen Sie "Benutzerdefiniert" und tippen Sie einen Bindestrich ( - ) ein. Klicken Sie auf OK.
• Power Query teilt die Daten in zwei Spalten auf. Sie können nun auf die neuen Spaltenüberschriften doppelklicken, um sie in "Rep_ID" und "Rep_Name" umzubenennen.

Um die Unterstriche in Bobs Namen (Bob_Jones) zu bereinigen, klicken Sie mit der rechten Maustaste auf die Spalte Rep_Name, wählen Sie Werte ersetzen, tippen Sie einen Unterstrich (_) in das Feld "Zu suchender Wert" ein und lassen Sie "Ersetzen durch" leer oder fügen Sie ein Leerzeichen hinzu. Klicken Sie auf OK.

Schritt 4: Datentypen standardisieren

Ist Ihnen aufgefallen, dass unsere Daten und Umsätze in völlig unterschiedlichen Formaten vorliegen? Power Query macht es einfach, dies zu standardisieren.

• Klicken Sie auf das kleine Symbol auf der linken Seite der Spaltenüberschrift "Order_Date" (es steht dort wahrscheinlich "ABC" oder "Beliebig"). Ändern Sie es in Datum. Power Query übersetzt die verschiedenen Datumsformate automatisch in ein einheitliches Standarddatum.
• Klicken Sie auf das Symbol neben "Revenue" und ändern Sie es in Währung (oder Dezimalzahl). Es entfernt die Dollarzeichen und formatiert alles sauber.

Schritt 5: Eine bedingte Spalte hinzufügen

Angenommen, wir möchten Verkäufe über 1.000 $ als "Hoher Wert" kategorisieren. Anstatt eine komplexe IF-Funktion wie =IF(C2>=1000, "High Value", "Standard") in Excel zu schreiben, können wir die Power Query-Benutzeroberfläche verwenden.

Gehen Sie zum Tab Spalte hinzufügen und klicken Sie auf Bedingte Spalte. Legen Sie die Regeln fest: Wenn [Revenue] größer oder gleich 1000 ist, dann Ausgabe "Hoher Wert" ("High Value"), andernfalls "Standard". Hinter den Kulissen generiert Power Query den folgenden M-Code für diesen Schritt:

= Table.AddColumn(#"Changed Type", "Sales Category", each if [Revenue] >= 1000 then "High Value" else "Standard")

Abfragen zusammenführen: Die moderne Alternative zu VLOOKUP

Eine der häufigsten Aufgaben in der Datenanalyse ist das Kombinieren von Tabellen. Wenn Sie eine separate Tabelle haben, die die Region für jeden Vertriebsmitarbeiter enthält, würden Sie normalerweise auf unseren vollständigen Leitfaden zu VLOOKUP zurückgreifen, um diese Daten hereinzuholen.

Die Ausführung von Tausenden von VLOOKUP- oder INDEX- und MATCH-Formeln kann Ihre Arbeitsmappe jedoch drastisch verlangsamen. In Power Query verwenden Sie die Funktion Abfragen zusammenführen.

Importieren Sie einfach beide Tabellen in Power Query, wählen Sie Ihre Hauptverkaufstabelle aus und klicken Sie auf dem Tab Start auf Abfragen zusammenführen. Wählen Sie die zweite Tabelle (die Regionen-Tabelle), klicken Sie in beiden Tabellen auf die übereinstimmende Spalte (z. B. "Rep_ID") und klicken Sie auf OK. Power Query führt das Äquivalent eines ultraschnellen VLOOKUPs in Sekundenbruchteilen durch, unabhängig davon, ob Sie zehn oder zehn Millionen Zeilen haben.

Daten entpivotieren: Die Geheimwaffe von Power Query

Oft erhalten Sie Daten, die bereits in einer Pivot-ähnlichen Struktur gruppiert sind (z. B. Monate als Spalten: Jan, Feb, Mär, Apr). Während dies für Menschen leicht zu lesen ist, ist es für die Erstellung von Diagrammen oder PivotTables furchtbar.

Wählen Sie Ihre Identifikatorspalten (wie Rep_Name) aus, klicken Sie mit der rechten Maustaste auf die Überschrift und wählen Sie Andere Spalten entpivotieren. Power Query verwandelt Ihre breiten Kreuztabellendaten sofort in ein flaches, tabellarisches Layout mit einer neuen Spalte für das "Attribut" (Monat) und den "Wert" (Umsatz). Dies mit Standard-Excel-Formeln zu bewerkstelligen, ist nahezu unmöglich, weshalb das Entpivotieren eine der gefeiertsten Funktionen von Power Query ist.

Laden der transformierten Daten

Sobald Ihre Daten perfekt bereinigt sind, ist es an der Zeit, sie zurück an Excel zu senden.

Klicken Sie auf dem Tab Start auf Schließen & laden. Standardmäßig lädt dies Ihre transformierten Daten in eine brandneue, grüne Excel-Tabelle auf einem neuen Arbeitsblatt. Wenn Sie die Daten lieber direkt an Ihre Analysephase senden möchten, können Sie auf den Dropdown-Pfeil klicken, Schließen & laden in... wählen und stattdessen einen PivotTable-Bericht auswählen. Wenn Sie eine Auffrischung beim Erstellen dieser Zusammenfassungen benötigen, lesen Sie unser Tutorial zum Erstellen von PivotTables für Anfänger.

Die Magie der Schaltfläche "Aktualisieren"

Die wahre Stärke von Power Query zeigt sich nächste Woche, wenn Sie einen neuen Roh-Verkaufsexport erhalten. Wiederholen Sie nicht die oben genannten Schritte!

Speichern Sie einfach die neue CSV-Datei über die alte (behalten Sie genau denselben Dateinamen und Speicherort bei). Öffnen Sie dann Ihre Excel-Arbeitsmappe, klicken Sie mit der rechten Maustaste auf eine beliebige Stelle in Ihrer sauberen Datentabelle und klicken Sie auf Aktualisieren.

Power Query greift auf die Datei zu, wendet jeden einzelnen Schritt erneut an – Zeilen entfernen, Kopfzeilen hochstufen, Spalten teilen, Text ersetzen, Bedingungen prüfen und Tabellen zusammenführen – und aktualisiert Ihre endgültige Ausgabe in Sekundenbruchteilen. Dies ist eine entscheidende Komponente von Excel-Automatisierungs-Workflows.

Ihre Datenreise mit KI vereinfachen

Während Power Query strukturelle Transformationen hervorragend meistert, benötigen Sie manchmal eine spezifische bedingte Logik oder ein komplexes Text-Parsing, das fortgeschrittene Excel-Formeln oder benutzerdefinierten M-Code erfordert. Anstatt Foren nach Antworten zu durchsuchen, können Sie künstliche Intelligenz nutzen.

Wenn Sie sich schwer tun, die perfekte Berechnung für eine benutzerdefinierte Spalte zu schreiben, ist ExcelGPT der perfekte Begleiter. Beschreiben Sie einfach in einfachem Deutsch, was Sie erreichen möchten – zum Beispiel: "Ich brauche eine Formel, um nur die Zahlen aus einer gemischten Textzeichenfolge zu extrahieren" –, und ExcelGPT generiert sofort die richtige Formel oder den M-Code. Die Kombination von Power Query mit KI zur Datenbereinigung bietet Ihnen ein unschlagbares Toolkit für die Datenanalyse.

Häufig gestellte Fragen (FAQ)

Verändert Power Query meine ursprüngliche Datendatei?

Nein. Power Query erstellt eine Einwegverbindung zu Ihren Quelldaten. Es liest die Daten, wendet die Transformationen im Arbeitsspeicher an und gibt ein neues Ergebnis in Excel aus. Ihre ursprüngliche CSV-Datei, Datenbank oder Arbeitsmappe bleibt völlig unberührt und sicher.

Kann ich Power Query auf einem Mac verwenden?

Ja, Microsoft hat die Unterstützung für Power Query in Excel für Mac erheblich verbessert. Während der Mac-Version früher einige der auf Windows verfügbaren erweiterten Konnektoren und UI-Funktionen fehlten, können Sie sich in modernen Versionen von Microsoft 365 nun reibungslos mit lokalen Dateien und Datenbanken verbinden sowie bestehende Abfragen aktualisieren.

Was ist der Unterschied zwischen Zusammenführen und Anfügen in Power Query?

Zusammenführen (Merge) ist das Äquivalent zu einem VLOOKUP oder INDEX/MATCH. Sie verwenden es, um neue Daten-Spalten hinzuzufügen, indem Sie eine gemeinsame ID zwischen zwei Tabellen abgleichen. Anfügen (Append) ist wie das Kopieren und Einfügen von Daten am unteren Rand eines Blattes. Sie verwenden es, um Tabellen übereinander zu stapeln und neue Zeilen hinzuzufügen (z. B. das Kombinieren der Januar-Verkäufe mit den Februar-Verkäufen).

Warum ist meine Power Query-Aktualisierung fehlgeschlagen?

Der häufigste Grund für das Fehlschlagen einer Abfrageaktualisierung ist, dass die Quelldatei verschoben, umbenannt oder gelöscht wurde. Ein weiteres häufiges Problem ist, dass sich eine Spaltenüberschrift in den Rohdaten geändert hat (z. B. wurde "Revenue" vom System in "Total Revenue" geändert). Sie können dies beheben, indem Sie den Power Query-Editor öffnen, zum Bereich "Angewendete Schritte" gehen und den Schritt "Quelle" aktualisieren oder die Spalte in Ihrer Schrittlogik umbenennen.