Wie der Inhalt einer Spaltenüberschrift ausgelesen und als neue Spalte bereitgestellt wird, habe ich in meinem Power-Query-Rezept vom 8.3.2023 beschrieben.
Was aber, wenn Inhalte nicht in der Liste selbst sind, sondern als sogenannte Kopfdaten zu Beginn der CSV-Dateien vorliegen? Die folgende Abbildung zeigt dafür ein Beispiel: Lieferant, Warengruppe und Datum stehen hier oberhalb der Datenliste. Doch genau diese drei Angaben werden als weitere Spalten gebraucht, um die Daten später nach Lieferant, Warengruppe oder Datum auswerten zu können.
Wie Lieferantenname, Warengruppe und Lieferdatum aus den Kopfdaten in Spalten überführt werden, zeige ich im folgenden Power-Query-Rezept, bei dem ich u.a. den Befehl »Benutzerdefinierte Spalte« verwende.
Bild 1: Nicht nur die Listen-, sondern auch die Kopfdaten werden später zum Auswerten gebraucht
Beim Einlesen der CSV-Dateien automatisch drei neue Spalten erzeugen
Ziel ist es, dass aus den in Bild 1 gezeigten Kopfdaten die in der folgenden Abbildung gezeigten drei neuen Spalten Lieferant, Warengruppe und Lieferdatum gebildet werden, und zwar automatisch.
Bild 2: Vorschau auf das fertige Ergebnis: Die Daten aus allen Dateien sind zusammengeführt inklusive der drei Kopfdaten in jeweils eigener Spalte
1) Daten aus Ordner mit Power Query einlesen
Zunächst rufe ich wie folgt alle Dateien aus dem Ordner mit Power Query ab:
- In Excel wechsle ich zur Registerkarte Daten und wähle die Befehlsfolge Daten abrufen > Aus Datei > Aus Ordner und den gewünschten Ordner.
- Per Klick auf die Schaltfläche Daten transformieren gelange ich in den Power Query-Editor.
- Um sicherzustellen, dass nur relevante Dateien eingelesen werden, filtere ich in der Spalte Name alle Dateien, die mit Lieferung beginnen und die Endung .csv besitzen.
Bild 3: Um nicht versehentlich falsche Dateien einzulesen, werden sie zunächst gefiltert
Hinweis: Wie alle Dateien aus einem Ordner nicht manuell, sondern automatisiert und fehlerfrei importiert werden, habe ich im Beitrag vom 3.5.2023 »Dateien aus Ordner importieren und anfügen, aber OHNE Fehler« gezeigt.
- Per Rechtsklick auf die Spalte Content wähle ich Andere Spalten entfernen.
- Nun können alle Dateien zusammengeführt werden, indem ich auf den Doppelpfeil in der Spalte Content klicke, um die Dateien zu kombinieren.
- Ich bestätige das folgende Dialogfeld Dateien kombinieren mit OK.
Bild 4: Per Klick auf den Doppelpfeil werden alle Dateien automatisch kombiniert
Kurzes Zwischenfazit
Das Ergebnis sieht noch nicht zufriedenstellend aus. Zwar sind alle Daten in der Abfrage Lieferungen aneinandergefügt, allerdings mit den kompletten Kopfzeilen.
Bild 5: Die Daten wurden zwar zusammengeführt, aber noch ohne Übernahme der Kopfdaten in die Liste
Demzufolge muss ich dafür sorgen, dass die Listendaten untereinander stehen mit den Angaben aus den Kopfdaten als neue Spalten. Dies erledige ich in der Abfrage Beispieldatei transformieren, denn sie dient als Muster für alle zu verarbeitenden Dateien.
Musterdatei nachbearbeiten und bereinigen
Bevor ich die Musterdatei Beispieldatei transformieren bearbeite, schaue ich mir an, an welcher Stelle die relevanten Informationen aus den Kopfdaten zu finden sind:
- Lieferant steht in Zeile 1 der Spalte Column2 des Schritts Quelle,
- Warengruppe in Zeile 2 und
- Lieferdatum in Zeile 3.
Bild 6: Später wird auf diesen Schritt zurückgegriffen, um die relevanten Kopfdaten zu sichern
Um die Listendaten verarbeiten zu können, muss ich die Kopfdaten entfernen. So geht’s:
- Auf der Registerkarte Start wähle ich Zeilen entfernen > Erste Zeilen entfernen > 5.
- Anschließend verschiebe ich die Überschriften nach oben mit Start > Erste Zeile als Überschriften verwenden.
Im Ergebnis dessen sind in der Liste nur noch die Spalten ArtNr, Stückpreis und Menge verfügbar. Die Angaben aus den Kopfdaten fehlen noch.
Bild 7: Die Liste ist jetzt zwar bereinigt, aber es fehlen noch die Kopfdaten
2) Die Musterdatei um die Angaben aus den Kopfdaten als neue Spalten erweitern
Jetzt werden die drei Angaben aus den Kopfdaten als jeweils eigene Spalten benötigt.
Dazu greife ich auf den Schritt Quelle zurück. Im ihm finde ich die Kopfdaten in Spalte Column2 in den Zeilen 1, 2, und 3.
Da Power Query die Zeilenzählung bei 0 beginnt, muss ich alle Zeilennummern um 1 reduzieren. So gehe ich vor:
- Ich wähle Spalte hinzufügen > Benutzerdefinierte Spalte und Neuer Spaltenname: Lieferant.
- Als benutzerdefinierte Spaltenformel gebe ich ein: =Quelle[Column2]{0}.
Bild 8: Mit einer benutzerdefinierten Spalte den Lieferantennamen aus dem Schritt Quelle auslesen
Bild 9: Der Lieferantenname wurde mit Hilfe einer benutzerdefinierte Spalte als Spalteninformation bereitgestellt
- Für das Auslesen der Kopfdaten-Angaben zu Warengruppe und Datum gehe ich analog vor, nur ändere ich jeweils die Zeilennummer.
Bild 10: Warengruppe und Lieferdatum werden auf die gleiche Weise ausgelesen und nur die Zeilennummer ändert sich
3) Kontrolle der Abfrage Lieferungen mit allen Daten aus dem Ordner
Ich kontrolliere die Abfrage Lieferungen. Hier sind alle Lieferdaten samt relevanter Kopfdaten aus dem Ordner ordnungsgemäß aneinandergefügt worden (siehe ganz oben Bild 2).
Nun fehlen nur noch die Datentypanpassung und das Laden der Abfrage nach Excel. Das erledige ich gebündelt, indem ich
- alle Spalten mit Hilfe der Umschalt-Taste markiere,
- unter Transformieren > Datentyp erkennen wähle und
- den Datentyp für die Spalte Lieferdatum nachkorrigiere zu Datum.
- Die Abfrage lade ich über Start > Schließen & laden > Schließen & laden in … > Tabelle.
Fazit
- Neue Lieferdaten können nun einfach in dem Ordner abgelegt werden und es reicht ein einfaches Aktualisieren per Rechtsklick auf die geladene Tabelle.
- Künftig entfällt Copy & Paste und damit lästige und fehleranfällige Arbeiten.
Weiterführende Informationen
Wie nur bestimmte Dateien aus mehreren Ordnern eingelesen werden, erkläre ich in diesem Blogbeitrag vom 10. August 2023 Power Query: Aus mehreren Ordnern nur ausgewählte Dateien zusammenführen.
Wer in Power Query Wissen aufbauen oder ausbauen möchte, findet hier passende Kurse:
- Daten automatisiert importieren, aufbereiten und abgleichen
- Mit eigenen Abfrage-Tools die Datenaufbereitung optimieren
TIPP: Wer hier im Blog ALLE Rezepte zu Power Query finden möchte, gibt einfach oben rechts in das Suchfeld Power-Query-Rezept ein.
