Excel Power Query: CSV importieren und Daten bereinigen

Power Query steckt in Excel für Microsoft 365, Excel 2021 und 2019 sowie 2016 unter Windows. Der Menüpunkt heißt „Daten abrufen“ auf der Registerkarte „Daten“. Funktionen und Bezeichnungen unterscheiden sich in der Mac-Version.

Wofür Power Query gedacht ist

Wer jeden Monat eine neue Exportdatei bekommt und sie gleich aufbereitet, kennt die Routine: Spalten löschen, Texte teilen, Formate ändern, Duplikate entfernen. Mit Power Query machst du das einmal, und Excel merkt sich jeden Schritt. Beim nächsten Export tauschst du nur die Datei aus und aktualisierst. Die Originaldaten bleiben unverändert, denn Power Query liest nur. Das Ergebnis landet als neue Tabelle. Damit ist es sicherer als Bereinigen von Hand und weniger aufwendig als ein Makro, siehe Excel Makro aufzeichnen.

Typische Anwendungen sind Kontoauszüge als CSV, Exporte aus Shops und Kassensystemen, mehrere Monatsdateien in einem Ordner, Tabellen von Webseiten und Daten aus mehreren Tabellenblättern, die du zusammenführen willst.

CSV-Datei importieren

1. Öffne eine leere Arbeitsmappe und wähle „Daten“, „Daten abrufen“, „Aus Datei“, „Aus Text/CSV“.
2. Wähle die Datei aus. Excel zeigt eine Vorschau.
3. Prüfe oben „Dateiursprung“ und „Trennzeichen“. Für deutsche CSV-Dateien ist das Semikolon üblich, bei Umlaut-Problemen wähle „65001: Unicode (UTF-8)“ oder „1252: Westeuropäisch“.
4. Klicke auf „Daten transformieren“, um in den Editor zu wechseln, oder auf „Laden“, wenn die Daten schon sauber sind.

Praktisch ist, dass Excel den Datentyp der Spalten automatisch erkennt. Prüfe das Ergebnis, denn Postleitzahlen und Telefonnummern erkennt das Programm manchmal als Zahl. Im Editor änderst du den Typ auf Text. Hinweise zu führenden Nullen und Zahlen als Text stehen in Excel Text trennen und verbinden.

Der Power-Query-Editor

Der Editor ist ein eigenes Fenster. In der Mitte siehst du die Daten, links die Liste der Abfragen, rechts die „Abfrageeinstellungen“ mit den „Angewendeten Schritten“. Jeder Klick erzeugt einen Schritt, den du ansehen, umbenennen, löschen oder ändern kannst. Klickst du auf einen früheren Schritt, siehst du den Stand der Daten zu diesem Zeitpunkt. Änderst du dort etwas, wirkt sich das auf alle folgenden Schritte aus. Die Schritte laufen immer von oben nach unten.

• Menüband: Die Registerkarten „Start“, „Transformieren“ und „Spalte hinzufügen“ enthalten die Befehle.
• Spaltenkopf: Ein Rechtsklick öffnet ein Menü mit den wichtigsten Aktionen.
• Spaltenprofil: Unter „Ansicht“ kannst du „Spaltenqualität“ und „Spaltenverteilung“ einblenden. Sie zeigen Fehler und leere Werte.
• Formelleiste: Zeigt den Befehl des aktuellen Schritts in der Sprache M.

Typische Bereinigungsschritte

Eine benutzerdefinierte Spalte ist wie eine Formel in M. Du klickst auf die Spaltennamen, die du verwenden willst, und kombinierst sie. Für Vorgänge wie „wenn Betrag größer 1000, dann hoch“ nutzt du die Bedingte Spalte, die ohne Programmierung auskommt. Solche Bedingungen entsprechen der WENN-Funktion, siehe Excel WENN-Funktion.

Datentypen und Gebietsschema

Zahlen im deutschen Format haben ein Komma als Dezimaltrenner, englische einen Punkt. Importiert Power Query eine Datei mit dem falschen Format, entstehen Fehler oder falsche Werte, etwa wenn 1,5 als Datum gelesen wird. Lösung: Rechtsklick auf die Spalte, „Typ ändern“, „Mit Gebietsschema“, dann Typ und Gebietsschema wählen, zum Beispiel „Dezimalzahl“ und „Englisch (USA)“ für Daten mit Punkt. Dasselbe gilt für Datumsangaben: Ein amerikanisches Datum im Format Monat/Tag/Jahr braucht das passende Gebietsschema, sonst vertauschen sich Tag und Monat.

Setze den Datentyp bewusst jeder Spalte, am besten gegen Ende der Schritte. Ein falscher Typ ist die häufigste Ursache für Fehler wie „Fehler in Zelle“. Zeigt die Spaltenqualität rote Werte, ist der Typ unpassend oder die Daten sind fehlerhaft.

Entpivotieren: Breite in lange Tabellen

Viele Tabellen sind für Menschen gebaut: Monate stehen als Spalten nebeneinander, jede Zeile ist ein Produkt. Für Auswertungen mit Pivot-Tabellen ist das ungünstig. Besser sind lange Tabellen mit den Spalten Produkt, Monat und Wert. Power Query wandelt sie um. Markiere die Spalten, die erhalten bleiben sollen, also Produkt, wähle „Transformieren“, „Spalten entpivotieren“ und „Andere Spalten entpivotieren“. Aus den Monatsspalten werden zwei Spalten: „Attribut“ mit dem Monatsnamen und „Wert“ mit der Zahl. Benenne sie um. Die neue Tabelle lässt sich direkt in einer Pivot-Tabelle auswerten, siehe Excel Pivot-Tabelle erstellen.

Der umgekehrte Weg heißt „Pivotieren“ und wandelt Werte einer Spalte in Spaltenköpfe um. Er wird seltener gebraucht.

Tabellen anfügen und Ordner kombinieren

Hast du zwölf Monatsdateien mit identischer Struktur, kannst du sie in einem Schritt zusammenfügen. Lege alle Dateien in einen Ordner. Wähle „Daten abrufen“, „Aus Datei“, „Aus Ordner“ und den Ordnerpfad, dann „Kombinieren“ und „Daten kombinieren und transformieren“. Power Query bereinigt die erste Datei und wendet dieselben Schritte auf alle an. Neue Dateien im Ordner werden bei der nächsten Aktualisierung automatisch berücksichtigt. Die Dateien müssen gleiche Spalten haben, sonst gibt es Fehler.

Zum Anfügen von Abfragen im Arbeitsblatt nutzt du „Start“, „Abfragen anfügen“. Zwei Abfragen mit denselben Spalten werden untereinander gestellt. Das ist nützlich, wenn du Daten aus verschiedenen Datenquellen sammelst, etwa Bestellungen aus zwei Shops. Behalte eine Spalte „Quelle“, damit du später weißt, woher eine Zeile stammt. Das kannst du über eine benutzerdefinierte Spalte mit einem festen Text vor dem Anfügen setzen.

Tabellen zusammenführen

Zusammenführen entspricht einer Suche über zwei Tabellen, ähnlich einem Verweis, aber ohne Formeln. Wähle „Start“, „Abfragen zusammenführen“. Markiere in beiden Tabellen die Spalte, die zusammenpasst, etwa die Kundennummer. Unter „Join-Art“ entscheidest du:

• Links außen: Alle Zeilen der ersten Tabelle, dazu passende der zweiten. Das ist die häufigste Wahl, um Informationen anzureichern.
• Inner: Nur Zeilen, die in beiden Tabellen vorkommen.
• Voll außen: Alle Zeilen beider Tabellen.
• Links anti: Zeilen der ersten Tabelle ohne Treffer in der zweiten. Gut, um fehlende Zuordnungen zu finden.

Danach klappst du die neue Spalte mit dem Doppelpfeil auf und wählst die gewünschten Felder. Für einzelne Nachschlagevorgänge im Blatt bleibt die Funktion XVERWEIS bequemer, siehe XVERWEIS statt SVERWEIS. Für größere Datenmengen und wiederkehrende Aufgaben ist das Zusammenführen robuster und schneller.

Laden und Aktualisieren

Mit „Schließen und laden“ legt Excel das Ergebnis als Tabelle in ein neues Blatt. Über den Pfeil „Schließen und laden in“ wählst du ein bestehendes Blatt, nur eine Verbindung ohne Tabelle oder das Datenmodell für große Auswertungen. Das Ergebnis ist eine Excel-Tabelle mit allen Vorteilen, siehe Excel als Tabelle formatieren.

Ändert sich die Quelle, klickst du in Excel auf „Daten“, „Alle aktualisieren“ oder drückst Strg+Alt+F5. Die Abfrage läuft erneut. Du kannst unter den Verbindungseigenschaften festlegen, dass die Daten beim Öffnen der Datei oder in Intervallen aktualisiert werden. Manuell entscheiden ist oft sicherer, besonders wenn die Datenquelle auf dem Netzlaufwerk liegt. Sie sollte stets am selben Ort bleiben, sonst findet Excel sie nicht. Ändert sich der Pfad, passt du ihn in den Datenquelleneinstellungen an.

Abfragen verwalten und teilen

• Öffne das Fenster „Abfragen und Verbindungen“ über „Daten“. Dort siehst du alle Abfragen, kannst sie umbenennen, duplizieren oder löschen.
• Gib den Abfragen sprechende Namen. „Abfrage1“ hilft in einem halben Jahr niemandem.
• Dupliziere eine Abfrage, bevor du sie stark änderst. Eine Kopie kostet nichts.
• Wenn du die Arbeitsmappe weitergibst, hängen Abfragen an den Pfaden der Ausgangsdateien. Der Empfänger sieht Fehler, wenn er die Dateien nicht hat. Speichere die Daten und die Abfrage zusammen oder ändere die Quelle.
• Prüfe die Datenschutzstufen: Excel fragt, wenn Daten aus verschiedenen Datenquellen kombiniert werden. Behandle vertrauliche Daten vorsichtig.

Teile keine Dateien mit Zugangsdaten für Datenbanken oder Webdienste. Sie werden in den Verbindungen gespeichert. Prüfe, was beim Weitergeben in der Datei steckt, siehe auch Metadaten entfernen.

Ein Blick auf die Sprache M

Im Hintergrund schreibt Power Query jeden Schritt in der Sprache M. Du siehst den Code im „Erweiterten Editor“ auf der Registerkarte „Ansicht“. Er besteht aus einer Folge von benannten Schritten, die aufeinander aufbauen. Du musst ihn nicht lernen, um loszulegen. Aber ein Blick lohnt, denn du erkennst, was jeder Klick bewirkt, und kannst kleine Änderungen von Hand vornehmen, etwa den Dateipfad. Kopiere den Code in eine Textdatei als Sicherung, bevor du ihn bearbeitest.

Grenzen und Alternativen

• Power Query arbeitet nicht in Echtzeit. Es braucht eine Aktualisierung.
• Sehr große Datenmengen belasten den Arbeitsspeicher. Prüfe die Auslastung, siehe Arbeitsspeicher voll.
• Die Funktion fehlt in Excel für das Web in vollem Umfang und in der Mac-Version.
• Für einmalige kleine Bereinigungen reichen Textfunktionen oder die Blitzvorschau.
• LibreOffice hat kein Gegenstück, Dateien mit Abfragen bleiben dort nur als Ergebnis nutzbar.

Typische Probleme

• Umlaute sind kaputt: Falscher Dateiursprung. Wähle UTF-8 oder 1252.
• Zahlen werden als Datum gelesen: Gebietsschema stimmt nicht. Typ „Mit Gebietsschema“ ändern.
• Fehler nach neuer Datei: Spaltennamen haben sich geändert, und ein Schritt verweist auf eine nicht mehr vorhandene Spalte. Passe den Schritt an.
• Datei nicht gefunden: Datei wurde verschoben oder umbenannt. Ändere den Pfad in den Datenquelleneinstellungen.
• Aktualisieren dauert lange: Zu viele Schritte oder große Dateien. Entferne früh unnötige Spalten und Zeilen.
• Abfrage lädt doppelt: Du hast sie in ein Blatt und das Datenmodell geladen. Entferne eine der beiden Verbindungen.

Häufige Fragen

Muss ich programmieren können?

Nein. Die meisten Schritte erledigst du per Klick. Die Sprache M kannst du später lernen.

Verändert Power Query meine Originaldatei?

Nein. Es liest nur. Das Ergebnis steht in einer neuen Tabelle der Arbeitsmappe.

Was ist besser, Power Query oder ein Makro?

Für Importe und Bereinigungen meist Power Query, weil die Schritte lesbar sind und einfacher geändert werden können. Ein Makro hilft bei Bedienaufgaben in Excel selbst.

Kann ich mehrere Blätter einer Arbeitsmappe zusammenführen?

Ja. Lade die Arbeitsmappe über „Aus Arbeitsmappe“, wähle die Blätter und füge die Abfragen an.