Power Query in Excel: Daten einmal aufbereiten, immer wieder nutzen

Was ist Power Query und wann lohnt es sich?

Power Query ist kein Add-in mehr, sondern seit Excel 2016 fest eingebaut. Im Menüband heißt die Gruppe Daten abrufen und transformieren. Das Prinzip: Du zeigst Power Query einmal, wie aus einem unordentlichen Export eine saubere Liste wird. Jeder Klick im Editor wird als Schritt gespeichert. Beim nächsten Export musst du nichts mehr von Hand machen, du klickst nur auf Aktualisieren.

Das lohnt sich immer dann, wenn dieselbe Aufräumarbeit regelmäßig anfällt: der wöchentliche Export aus der Warenwirtschaft, die monatlichen Kassendateien mehrerer Filialen, die Kontoauszüge aus dem Onlinebanking oder die Stundenliste aus dem Zeiterfassungssystem. Wer heute jeden Montag eine halbe Stunde Spalten löscht, Leerzeichen entfernt und Daten zusammenkopiert, spart mit Power Query genau diese halbe Stunde.

Wichtig für das Verständnis: Power Query verändert nie die Quelldatei. Es liest sie, wendet die Schritte an und schreibt das Ergebnis in eine neue Tabelle. Die CSV vom Shop bleibt unangetastet. Für einen einmaligen, einfachen Import reicht oft der Weg aus unserer Anleitung CSV in Excel importieren. Power Query ist das Werkzeug dahinter, wenn es regelmäßig, mehrere Dateien oder kompliziert wird.

Die wichtigsten Befehle im Power-Query-Editor

Der Editor hat eigene Reiter: Start, Transformieren, Spalte hinzufügen und Ansicht. Für den Büroalltag brauchst du vor allem diese Befehle. Erste Zeile als Überschriften verwenden macht aus Column1, Column2 echte Spaltennamen. Datentyp legt fest, ob eine Spalte Text, Zahl oder Datum ist. Das ist der wichtigste Schritt überhaupt, denn ein falscher Typ führt später zu Fehlern oder zu Summen, die nicht rechnen.

Spalte teilen zerlegt Inhalte nach Trennzeichen, Anzahl Zeichen oder Position. Das ist das Gegenstück zu Text in Spalten, nur dass der Schritt beim Aktualisieren wieder mitläuft. Mehr zu den Varianten ohne Power Query steht unter Zelle teilen. Werte ersetzen arbeitet wie Suchen und Ersetzen, aber nur in der markierten Spalte. Transformieren › Format › Kürzen entfernt Leerzeichen am Rand. Zeilen entfernen löscht oberste Zeilen, leere Zeilen oder Duplikate.

Unter Spalte hinzufügen baust du neue Spalten, etwa Benutzerdefinierte Spalte für eine Rechnung wie Menge mal Preis. Die Formelsprache dort heißt M und ist nicht dieselbe wie Excel-Formeln: Es gibt kein =SUMME, und Spaltennamen stehen in eckigen Klammern, zum Beispiel [Menge] * [Preis]. Für den Einstieg brauchst du M nicht, die Menübefehle reichen.

Hast du dich vertan, klickst du rechts unter Angewendete Schritte auf das X neben dem Schritt. Ein Klick auf einen älteren Schritt zeigt, wie die Daten an dieser Stelle aussahen. So findest du Fehler schnell. Neue Schritte landen immer hinter dem markierten Schritt, deshalb vor dem Weiterarbeiten wieder den letzten Schritt anklicken.

Viele CSV-Dateien aus einem Ordner zusammenführen

Das ist die Funktion, für die viele Power Query überhaupt entdecken. Leg alle Dateien mit gleichem Aufbau in einen Ordner, zum Beispiel jeden Monat eine CSV pro Filiale. Dann Daten › Daten abrufen › Aus Datei › Aus Ordner, Ordner wählen, und in der Dateiliste auf Kombinieren klicken. Am sichersten ist Daten kombinieren und transformieren, dann landest du im Editor und kannst prüfen.

Power Query nimmt die erste Datei als Beispiel, du bestätigst Trennzeichen und Codierung, und alle Dateien werden untereinander gehängt. Im linken Bereich entstehen dabei automatisch Hilfsabfragen wie Beispieldatei und Datei transformieren. Die lässt du stehen, sie gehören dazu. Praktisch: Die Spalte Source.Name zeigt bei jeder Zeile, aus welcher Datei sie stammt. Über Spalte teilen machst du daraus Filiale und Monat.

Damit das dauerhaft klappt, gilt eine Regel: Im Ordner liegen nur Dateien mit gleichem Aufbau. Eine Notiz.txt oder eine geöffnete Excel-Datei mit dem Namen ~$Bericht.xlsx bringt die Abfrage durcheinander. Filtere im ersten Schritt die Spalte Extension auf .csv, dann ignoriert Power Query alles andere.

Abfragen zusammenführen: SVERWEIS ohne Formel

Stehen Preise, Kundennamen oder Kostenstellen in einer zweiten Tabelle, holst du sie mit Start › Kombinieren › Abfragen zusammenführen dazu. Du wählst beide Abfragen, klickst in jeder die gemeinsame Spalte an, etwa die Artikelnummer, und als Join-Art meist Linker äußerer Join (alle aus der ersten, übereinstimmende aus der zweiten). Danach erscheint eine neue Spalte mit dem Wort Table. Ein Klick auf das Doppelpfeil-Symbol im Spaltenkopf klappt sie auf, und du wählst die Spalten, die du brauchst.

Das Ergebnis entspricht einem SVERWEIS oder XVERWEIS, mit zwei Vorteilen: Es rechnet nicht bei jeder Änderung neu, was große Mappen schneller macht, und es findet auch mehrere Treffer pro Schlüssel. Achtung: Gibt es eine Artikelnummer in der zweiten Tabelle doppelt, verdoppelt sich die Zeile im Ergebnis. Vorher also Duplikate in der Nachschlagetabelle prüfen.

Nicht verwechseln: Abfragen anfügen hängt Tabellen untereinander, zum Beispiel die Januar- und Februarliste. Zusammenführen stellt Spalten nebeneinander. Die Faustregel: gleiche Spalten, mehr Zeilen heißt anfügen. Gleiche Zeilen, mehr Spalten heißt zusammenführen.

Das kann im deutschen Excel schiefgehen

Power Query wandelt Datentypen gern automatisch um, direkt nach dem Import steht ein Schritt Geänderter Typ. Bei deutschen Daten rät es manchmal falsch: Postleitzahlen werden zu Zahlen und verlieren die Null, Dezimalzahlen mit Punkt aus englischen Systemen werden zu Datum oder Fehler. Lösung: den automatischen Schritt löschen und den Typ selbst setzen, bei fremden Formaten über Rechtsklick › Typ ändern › Mit Gebietsschema.

Zweite Falle: Spaltennamen. Jeder Schritt merkt sich die Namen der Spalten. Benennt der Shop im Export „Umsatz“ in „Umsatz brutto“ um, bricht die Abfrage mit einer Fehlermeldung ab. Dann klickst du dich durch die Angewendeten Schritte bis zum ersten mit Warnsymbol und passt den Namen an.

Dritte Falle: Handarbeit in der Ergebnistabelle. Tippst du Notizen direkt in die geladene Tabelle, können sie beim Aktualisieren verrutschen oder verschwinden, weil Power Query die Zeilen neu schreibt. Änderungen gehören deshalb in die Abfrage. Eigene Formelspalten rechts neben den geladenen Spalten bleiben dagegen erhalten und rechnen für neue Zeilen mit.

Aktualisieren: manuell, beim Öffnen oder regelmäßig

Standard ist das manuelle Aktualisieren über Daten › Alle aktualisieren oder per Rechtsklick in die Ergebnistabelle › Aktualisieren. Unter Daten › Abfragen und Verbindungen siehst du alle Abfragen der Mappe. Rechtsklick › Eigenschaften öffnet die Einstellungen: Dort setzt du Daten beim Öffnen der Datei aktualisieren oder eine Aktualisierung alle paar Minuten.

Zieht die Quelldatei um, etwa auf ein neues Laufwerk, findet Power Query sie nicht mehr. Über Daten › Daten abrufen › Datenquelleneinstellungen › Quelle ändern trägst du den neuen Pfad ein, alle Schritte bleiben erhalten. Das ist deutlich schneller als neu bauen.

Ist die Liste geladen, wertest du sie am besten mit einer Pivot-Tabelle aus. Die aktualisiert sich bei Alle aktualisieren gleich mit. Wie Abfragen, Tabellen und Pivot zusammenspielen, übst du Schritt für Schritt im Excel-Kurs online.