Spalten in Power Query automatisch trennen: Schritt-für-Schritt-Anleitung für Excel und Power BI, inklusive Trennzeichen-Erkennung und häufiger Fehlerquellen.
Wenn beim Import aus CSV-Dateien, Textdateien oder Datenbanken mehrere Werte in einer einzigen Spalte landen, lässt sich das in Power Query zuverlässig lösen.
Mit der Funktion „Spalte teilen“ trennen Sie Inhalte nach Trennzeichen, nach Zeichenanzahl oder nach Position – und zwar so, dass die Struktur bei jedem Aktualisieren automatisch erhalten bleibt.
Warum Spalten beim Import oft zusammenhängen
Beim Import von Daten aus CSV-Dateien, Textdateien oder externen Systemen entsteht häufig das gleiche Problem: Mehrere Informationen stecken in nur einer Spalte. Ein typisches Beispiel ist eine Spalte mit vollständigem Namen, obwohl eigentlich Vor- und Nachname getrennt benötigt werden. Ebenso kommt es vor, dass Adressen, Artikelnummern oder Zeitstempel als ein zusammenhängender Text importiert werden.
Der Grund dafür liegt meistens im Ursprungssystem. Viele Exporte aus ERP-Systemen, Webshops oder Datenbanken liefern Rohdaten, ohne Rücksicht darauf zu nehmen, wie diese später ausgewertet werden sollen. Zudem verwenden unterschiedliche Quellen unterschiedliche Trennzeichen wie Komma, Semikolon, Tabulator oder Leerzeichen. Deshalb ist es sinnvoll, die Trennung nicht manuell in der Tabelle vorzunehmen, sondern direkt im Importprozess mit Power Query zu regeln.
Ein weiterer Vorteil: Sobald die Trennung als Abfrageschritt gespeichert ist, wiederholt sie sich bei jedem Refresh automatisch. Nachträgliche Korrekturen in Excel oder Power BI entfallen dadurch vollständig, was besonders bei wiederkehrenden Berichten viel Zeit spart.
Trennzeichen erkennen, bevor Sie starten
Bevor Sie eine Spalte aufteilen, lohnt sich ein genauer Blick auf die vorhandenen Daten. Denn nicht jede Spalte lässt sich mit derselben Methode trennen. Zunächst sollten Sie prüfen, ob ein einheitliches Trennzeichen vorliegt, etwa ein Komma, ein Semikolon oder ein Leerzeichen. Ist das der Fall, reicht in der Regel die Standardfunktion „Nach Trennzeichen teilen“ vollkommen aus.
Anders sieht es aus, wenn die Werte immer an derselben Position stehen, beispielsweise eine Postleitzahl mit fester Länge oder ein Code aus genau acht Zeichen. In diesem Fall bietet sich die Trennung nach Zeichenanzahl an, da hierbei keine Trennzeichen benötigt werden.
Etwas komplexer wird es bei gemischten Daten, bei denen unterschiedliche Trennzeichen innerhalb derselben Spalte vorkommen. Ein Beispiel dafür sind exportierte Listen, in denen manche Zeilen mit Semikolon und andere mit Komma getrennt sind. Hierfür ist eine zusätzliche Vorbereitung notwendig, die weiter unten im Abschnitt zu mehreren Trennzeichen erklärt wird.
Prüfen Sie außerdem mehrere Zeilen der importierten Spalte, nicht nur die erste. Andernfalls kann es passieren, dass eine Methode zwar für die ersten Datensätze funktioniert, bei abweichenden Zeilen jedoch fehlschlägt und die Struktur der Tabelle durcheinanderbringt.
Spalte nach Trennzeichen teilen – Schritt für Schritt
Die häufigste Methode, um Spalten beim Import zu trennen, ist die Aufteilung anhand eines Trennzeichens. Diese Funktion steht sowohl im Power Query-Editor von Excel als auch in Power BI Desktop und Power Query Online zur Verfügung. Der Ablauf ist in allen Umgebungen nahezu identisch.
So gehen Sie konkret vor:
- Daten importieren: Laden Sie die Quelldatei über „Daten abrufen“ in den Power Query-Editor, etwa aus einer CSV-Datei, einer Textdatei oder einer Tabelle.
- Spalte auswählen: Klicken Sie auf die Spaltenüberschrift der Spalte, die getrennt werden soll.
- Funktion öffnen: Wählen Sie im Menüband unter „Start“ oder „Transformieren“ die Option „Spalte teilen“ und anschließend „Nach Trennzeichen“. Alternativ erreichen Sie dieselbe Funktion per Rechtsklick auf die Spaltenüberschrift.
- Trennzeichen festlegen: Im sich öffnenden Dialogfeld geben Sie das passende Trennzeichen an, etwa Komma, Semikolon, Doppelpunkt, Tab oder ein benutzerdefiniertes Zeichen.
- Aufteilungsart bestimmen: Legen Sie fest, ob bei jedem Vorkommen des Trennzeichens getrennt werden soll oder nur beim ersten beziehungsweise letzten Vorkommen. Das ist besonders wichtig, wenn ein Trennzeichen mehrfach in der Spalte auftaucht.
- Bestätigen: Mit „OK“ wird die Spalte sofort in mehrere neue Spalten aufgeteilt, deren Anzahl sich automatisch aus dem Ergebnis ergibt.
Da Power Query jeden Schritt protokolliert, erscheint die Aufteilung anschließend im Bereich „Angewendete Schritte“. Dadurch lässt sich der Vorgang jederzeit nachvollziehen oder nachträglich anpassen, ohne die gesamte Abfrage neu aufbauen zu müssen.
Ein praktisches Beispiel verdeutlicht das Vorgehen: Enthält eine Spalte den Wert „Max Mustermann“, lässt sich diese über „Spalte teilen“ und das Trennzeichen Leerzeichen sauber in „Vorname“ und „Nachname“ aufteilen. Umgekehrt lassen sich getrennte Spalten später über „Spalten zusammenführen“ wieder verbinden, falls das erforderlich sein sollte.
Spalte nach Anzahl der Zeichen teilen
Nicht jede Spalte lässt sich anhand eines Trennzeichens aufteilen. Häufig stehen Informationen an einer festen Position, etwa bei Artikelnummern, Kontonummern oder Codes mit einheitlicher Länge. In diesem Fall bietet Power Query die Option „Nach Anzahl von Zeichen“.
Diese Funktion finden Sie an derselben Stelle wie die Trennung nach Trennzeichen: über „Spalte teilen“ im Menüband oder per Rechtsklick auf die Spaltenüberschrift. Anschließend wählen Sie „Nach Anzahl von Zeichen“ aus und geben ein, nach wie vielen Zeichen die Trennung erfolgen soll.
Zusätzlich lässt sich festlegen, ob die Aufteilung nur einmal erfolgen oder wiederholt in gleichbleibenden Abständen angewendet werden soll. Letzteres ist beispielsweise nützlich, wenn ein langer Textblock in mehrere gleich lange Abschnitte zerlegt werden muss.
Ein Beispiel: Besteht ein Code aus zehn Zeichen, wobei die ersten drei Zeichen für eine Kategorie und die restlichen sieben für eine laufende Nummer stehen, lässt sich die Spalte mit der Angabe „3 Zeichen“ exakt an dieser Stelle aufteilen. Dadurch entstehen zwei neue Spalten, die sich unabhängig voneinander weiterverarbeiten lassen, etwa für Filterungen oder Berechnungen.
Wichtig ist dabei, dass diese Methode nur zuverlässig funktioniert, wenn alle Werte tatsächlich dieselbe Struktur besitzen. Weichen einzelne Zeilen von der erwarteten Länge ab, verschieben sich die Ergebnisse, und die Auswertung wird fehlerhaft. Deshalb empfiehlt es sich, vor der Anwendung stichprobenartig mehrere Zeilen zu kontrollieren.
Mehrere unterschiedliche Trennzeichen gleichzeitig verarbeiten
Besonders anspruchsvoll wird die Aufteilung, wenn in einer Spalte mehrere unterschiedliche Trennzeichen gleichzeitig vorkommen, etwa ein Semikolon in einer Zeile und ein Komma in der nächsten. Die Standardfunktion „Nach Trennzeichen“ berücksichtigt zwar mehrere Zeichen gleichzeitig, jedoch nur, wenn diese im selben Dialogfeld angegeben werden.
Um dieses Problem zu lösen, hat sich folgender Ablauf bewährt:
- Bestehende Trennzeichen identifizieren: Sichten Sie mehrere Zeilen der Spalte und notieren Sie sämtliche vorkommenden Trennzeichen, beispielsweise Semikolon, Komma, Doppelpunkt und Bindestrich.
- Ein eindeutiges Ersatzzeichen wählen: Wählen Sie ein Zeichen, das in den Ausgangsdaten garantiert nicht vorkommt, zum Beispiel das Prozentzeichen. Prüfen Sie das vorab mit der Suchfunktion, um Überschneidungen auszuschließen.
- Werte ersetzen: Nutzen Sie in Power Query die Funktion „Werte ersetzen“, um jedes der ursprünglichen Trennzeichen einzeln durch das gewählte Ersatzzeichen zu ersetzen.
- Einheitlich trennen: Sobald alle Trennzeichen vereinheitlicht sind, wenden Sie „Spalte teilen“ mit dem neuen Zeichen an. Dadurch entsteht eine saubere Aufteilung, unabhängig davon, welches Trennzeichen ursprünglich verwendet wurde.
Diese Methode eignet sich zudem für Fälle, in denen zusätzlich zur Uneinheitlichkeit auch überflüssige Leerzeichen vorkommen. Hier empfiehlt sich vor dem Trennen der Einsatz der Funktion „Kürzen“, welche führende und abschließende Leerzeichen entfernt, sowie „Bereinigen“, um nicht druckbare Zeichen zu eliminieren. Beide Funktionen finden Sie im Menüband unter „Transformieren“ in der Gruppe für Textspalten.
Auf diese Weise lässt sich selbst eine stark uneinheitliche Spalte in eine klar strukturierte Tabelle überführen, ohne jede Zeile manuell zu kontrollieren.
Trennung dauerhaft automatisieren, damit sie bei jedem Refresh funktioniert
Der eigentliche Vorteil von Power Query liegt darin, dass eine einmal eingerichtete Aufteilung nicht wiederholt werden muss. Sobald die Schritte im Abfrage-Editor gespeichert sind, wendet Power Query sie bei jedem Laden der Daten automatisch erneut an, etwa nach einem manuellen Refresh oder bei einer geplanten Aktualisierung.
Damit das zuverlässig funktioniert, sollten Sie einige Punkte beachten:
- Feste Quellstruktur voraussetzen: Die Automatisierung funktioniert nur, wenn die Spaltenstruktur der Quelldatei bei jedem Import gleich bleibt. Ändert sich beispielsweise die Reihenfolge der Spalten, kann die gespeicherte Trennung ins Leere greifen.
- Schritte im Editor überprüfen: Öffnen Sie regelmäßig den Bereich „Angewendete Schritte“, um zu kontrollieren, ob alle Transformationen weiterhin korrekt ausgeführt werden.
- Fehlerbehandlung einplanen: Weichen einzelne Datensätze vom erwarteten Format ab, kann es zu Fehlern kommen. Power Query zeigt betroffene Zellen in solchen Fällen deutlich an, sodass sich Ursachen schnell finden lassen.
- Parameter nutzen: Bei wechselnden Dateinamen oder Pfaden empfiehlt sich der Einsatz von Parametern, damit die Abfrage flexibel bleibt, ohne bei jedem Import neu konfiguriert werden zu müssen.
Gerade bei regelmäßig aktualisierten Berichten, etwa monatlichen Auswertungen aus einem CSV-Export, zahlt sich dieser einmalige Aufwand deutlich aus. Anstatt die Trennung jedes Mal erneut vorzunehmen, genügt anschließend ein einfacher Klick auf „Aktualisieren“, und die Struktur bleibt automatisch erhalten.
Datentypen nach dem Trennen korrekt zuweisen
Nach dem Aufteilen einer Spalte weist Power Query den neu entstandenen Spalten häufig zunächst den Datentyp „Text“ zu. Das ist jedoch nicht immer sinnvoll, insbesondere wenn die getrennten Werte Zahlen, Datumsangaben oder Uhrzeiten enthalten.
Deshalb sollte im Anschluss an jede Trennung der passende Datentyp kontrolliert und bei Bedarf angepasst werden. Dazu klicken Sie auf das kleine Symbol links neben der Spaltenüberschrift und wählen den gewünschten Typ aus, etwa „Ganze Zahl“, „Dezimalzahl“, „Datum“ oder „Datum/Zeit“.
Diese Anpassung ist aus mehreren Gründen wichtig. Zum einen lassen sich Berechnungen wie Summen oder Mittelwerte nur auf numerischen Spalten korrekt durchführen. Zum anderen erkennt Power BI nur bei korrektem Datentyp automatisch geeignete Diagrammtypen und Sortierreihenfolgen. Zudem verhindert eine saubere Typisierung spätere Fehler bei Verknüpfungen mit anderen Tabellen, etwa in einem Datenmodell mit mehreren Abfragen.
Ein praktischer Hinweis: Ändern Sie den Datentyp erst, nachdem die Spalte vollständig getrennt und bereinigt wurde. Andernfalls kann es vorkommen, dass Power Query bei nachträglichen Anpassungen frühere Schritte überschreibt oder Konflikte zwischen den „Angewendeten Schritten“ entstehen.
Häufige Fehler beim Spalten trennen und wie Sie sie vermeiden
Auch wenn die Funktion „Spalte teilen“ recht intuitiv bedienbar ist, treten in der Praxis immer wieder ähnliche Fehler auf. Die folgende Übersicht zeigt die häufigsten Stolperfallen sowie passende Lösungen:
- Falsches Trennzeichen gewählt: Wird beispielsweise ein Komma als Trennzeichen angegeben, obwohl tatsächlich ein Semikolon verwendet wird, bleibt die Spalte unverändert. Kontrollieren Sie deshalb vorab genau, welches Zeichen tatsächlich vorliegt.
- Mehrfaches Vorkommen des Trennzeichens ignoriert: Wenn ein Trennzeichen öfter vorkommt als erwartet, entstehen mehr Spalten als geplant. Legen Sie deshalb bewusst fest, ob bei jedem Vorkommen oder nur am ersten beziehungsweise letzten Vorkommen getrennt werden soll.
- Uneinheitliche Zeilenlängen bei fester Zeichenanzahl: Weicht auch nur eine Zeile von der erwarteten Länge ab, verschiebt sich die gesamte Aufteilung. Prüfen Sie deshalb stichprobenartig mehrere Datensätze, bevor Sie diese Methode anwenden.
- Fehlende Bereinigung vor dem Trennen: Überflüssige Leerzeichen oder nicht druckbare Zeichen führen häufig zu unerwarteten Ergebnissen. Setzen Sie deshalb vorab die Funktionen „Kürzen“ und „Bereinigen“ ein.
- Datentyp nicht angepasst: Bleiben getrennte Zahlen- oder Datumsspalten als Text formatiert, funktionieren spätere Berechnungen oder Sortierungen nicht korrekt.
- Änderungen an der Quelldatei ohne Anpassung der Abfrage: Ändert sich die Struktur der Ursprungsdatei, etwa durch eine zusätzliche Spalte, kann die gespeicherte Aufteilung ins Leere laufen. Kontrollieren Sie in solchen Fällen die „Angewendeten Schritte“ erneut.
Wer diese Punkte im Blick behält, vermeidet die überwiegende Mehrheit der typischen Probleme und erhält eine stabile, dauerhaft funktionierende Abfrage.
Unterschiede zwischen Excel Power Query, Power BI und Power Query Online
Die Grundfunktion „Spalte teilen“ steht in allen gängigen Power-Query-Umgebungen zur Verfügung, jedoch mit kleinen Unterschieden in der Bedienung und im Funktionsumfang.
In Excel öffnen Sie den Power Query-Editor über „Daten“ und anschließend „Daten abrufen“. Die Funktion „Spalte teilen“ findet sich sowohl im Menüband unter „Start“ als auch unter „Transformieren“. Excel eignet sich besonders für einmalige oder gelegentliche Importe sowie für kleinere bis mittlere Datenmengen.
In Power BI Desktop ist der Power Query-Editor über „Daten transformieren“ erreichbar. Die Bedienung ähnelt Excel stark, jedoch stehen zusätzlich erweiterte Optionen zur Verfügung, etwa im Bereich „Erweitert“ innerhalb des Dialogfelds „Spalte teilen“. Dort lässt sich unter anderem festlegen, ob die Aufteilung in Zeilen statt in Spalten erfolgen soll, was bei mehrwertigen Feldern nützlich sein kann. Power BI eignet sich zudem besser für größere Datenmengen und wiederkehrende Aktualisierungen in Berichten.
In Power Query Online, etwa im Kontext von Dataflows in Power BI Service oder Microsoft Fabric, unterscheidet sich das Dialogfeld optisch leicht von der Desktop-Version. Die grundlegenden Optionen wie Trennzeichenauswahl und Aufteilungsart sind jedoch weitgehend identisch. Diese Umgebung eignet sich besonders für zentral verwaltete Datenflüsse, auf die mehrere Berichte gleichzeitig zugreifen.
Unabhängig von der gewählten Umgebung bleibt der zugrunde liegende Mechanismus derselbe: Power Query erzeugt im Hintergrund einen Schritt in der sogenannten M-Sprache, der bei jedem Laden erneut ausgeführt wird. Deshalb lässt sich eine einmal erstellte Logik grundsätzlich auch zwischen Excel, Power BI und Power Query Online übertragen, sofern die Datenquelle entsprechend eingebunden wird.
Fortgeschrittene Techniken: M-Code für komplexe Trennungen
Für Anwender, die über die grafische Oberfläche hinausgehen möchten, bietet Power Query zusätzlich die Möglichkeit, Trennungen direkt über die interne Formelsprache M zu steuern. Das ist vor allem dann sinnvoll, wenn Standardfunktionen an ihre Grenzen stoßen, etwa bei sehr unregelmäßigen Textstrukturen.
Über den „Erweiterten Editor“, den Sie im Menüband unter „Start“ oder „Ansicht“ finden, lässt sich der generierte Code jeder Aufteilung einsehen und bei Bedarf anpassen. Die Funktion Table.SplitColumn etwa steuert die Aufteilung nach Trennzeichen und lässt sich manuell erweitern, um zusätzliche Bedingungen einzubauen.
Ein Beispiel: Sollen nur Zeilen ab einer bestimmten Länge geteilt werden, während kürzere Werte unverändert bleiben sollen, lässt sich dies mit einer zusätzlichen if-Bedingung innerhalb einer benutzerdefinierten Spalte umsetzen. Ebenso lassen sich mit der Funktion Text.Split einzelne Werte in Listen umwandeln, die anschließend flexibel weiterverarbeitet werden können, etwa um sie in Zeilen statt in Spalten zu expandieren.
Auch wenn für die meisten alltäglichen Aufgaben kein M-Wissen erforderlich ist, lohnt sich ein Blick in den erweiterten Editor spätestens dann, wenn sich Anforderungen wiederholt ändern oder mehrere Sonderfälle gleichzeitig abgedeckt werden müssen. Auf diese Weise lässt sich die Trennlogik präzise an individuelle Datenstrukturen anpassen, ohne auf zusätzliche externe Werkzeuge zurückgreifen zu müssen.
Häufige Fragen zum Trennen von Spalten in Power Query
Welches Trennzeichen wird am häufigsten für den Spaltenimport benötigt?
Am häufigsten kommen Komma, Semikolon, Tabulator und Leerzeichen vor, da diese in CSV-Exporten und Textdateien standardmäßig verwendet werden. Zusätzlich lässt sich in Power Query jederzeit ein benutzerdefiniertes Zeichen angeben, falls die Quelldatei ein untypisches Trennzeichen verwendet.
Was passiert, wenn ein Trennzeichen mehrfach in einer Zelle vorkommt?
In diesem Fall entscheidet die gewählte Aufteilungsart über das Ergebnis. Wird „Bei jedem Vorkommen“ ausgewählt, entstehen mehrere Spalten. Alternativ lässt sich festlegen, dass nur beim ersten oder letzten Vorkommen getrennt wird, wodurch die restliche Zeichenkette unverändert bleibt.
Kann Power Query mehrere unterschiedliche Trennzeichen gleichzeitig berücksichtigen?
Ja, allerdings müssen alle relevanten Zeichen im Dialogfeld angegeben oder vorab durch ein einheitliches Ersatzzeichen ersetzt werden. Andernfalls erkennt Power Query nur das explizit ausgewählte Trennzeichen und ignoriert abweichende Zeichen in anderen Zeilen.
Warum entstehen nach dem Trennen zu viele oder zu wenige Spalten?
Dieses Problem tritt meist auf, wenn die Anzahl der Trennzeichen zwischen den Zeilen variiert. Da Power Query die Spaltenanzahl anhand der maximal vorkommenden Trennungen festlegt, entstehen bei kürzeren Zeilen leere Zellen, während bei längeren Zeilen zusätzliche Spalten erzeugt werden.
Bleibt die Trennung erhalten, wenn die Datenquelle aktualisiert wird?
Ja, sofern die grundlegende Struktur der Quelldatei gleich bleibt. Power Query speichert die Trennung als festen Abfrageschritt, der bei jedem Refresh automatisch erneut ausgeführt wird, ohne dass eine manuelle Wiederholung notwendig ist.
Wie lässt sich eine Spalte nach fester Zeichenanzahl korrekt aufteilen?
Wählen Sie hierfür „Spalte teilen“ und anschließend „Nach Anzahl von Zeichen“. Geben Sie die gewünschte Zeichenanzahl an und legen Sie fest, ob die Aufteilung einmalig oder wiederholt erfolgen soll. Diese Methode funktioniert zuverlässig nur bei einheitlicher Zeilenlänge.
Unterscheidet sich die Funktion zwischen Excel und Power BI?
Grundsätzlich nicht. Excel und Power BI verwenden denselben Power-Query-Mechanismus, wobei Power BI zusätzlich erweiterte Optionen bietet, etwa die Aufteilung in Zeilen statt in Spalten. Power Query Online unterscheidet sich optisch leicht, bietet jedoch dieselben grundlegenden Funktionen.
Was tun, wenn nach dem Trennen falsche Datentypen angezeigt werden?
Kontrollieren Sie nach jeder Trennung die zugewiesenen Datentypen und passen Sie diese über das Symbol neben der Spaltenüberschrift manuell an, etwa auf „Ganze Zahl“ oder „Datum“. Ohne diesen Schritt können spätere Berechnungen oder Sortierungen fehlerhaft ausfallen.
Lässt sich eine bereits getrennte Spalte wieder zusammenführen?
Ja, dafür markieren Sie die betroffenen Spalten und wählen „Spalten zusammenführen“. Anschließend legen Sie ein Trennzeichen fest, mit dem die Werte beim Zusammenführen wieder verbunden werden sollen.
Fazit
Power Query löst das Problem zusammenhängender Spalten zuverlässig, egal ob per Trennzeichen oder Zeichenanzahl. Sobald die Trennung einmal eingerichtet ist, wiederholt sie sich automatisch bei jedem Refresh, unabhängig davon, ob in Excel, Power BI oder Power Query Online gearbeitet wird.
Wichtig bleibt eine saubere Vorbereitung: einheitliche Trennzeichen, korrekte Datentypen und ein Blick auf mehrere Zeilen verhindern spätere Fehler in Berichten und Auswertungen.
