Xtract: Auch eine große Anzahl von Überschriften (Spaltennamen) in einer Power Query-Abfrage auf komfortable Weise veränern/anpassen.
Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ
Herr Kleinbrot (seines Zeichens Bäcker und Inhaber der gleichnamigen Bäckerei) hat einen Bekannten gebeten, die Daten dieser Datei (Blätter Januar bis Dezember) in eine csv-Datei zu exportieren. Dabei ist offensichtlich etwas anders gelaufen, als gedacht bzw. ist das Ergebnis anders, als es sich für eine „ordentliche” csv-Datei gehört. Die Daten an sich wurden zwar prinzipiell alle korrekt übernommen und auch lückenlos zusammengefügt aber in der ersten Zeile sind nicht die Spaltenüberschriften sondern es beginnt gleich mit den Daten vom 1. Januar 2014; das widerspricht den Vorgaben für ein *.csv-File, dass die erste Zeile die Überschriften enthält. Hier können Sie diese (fehlerhafte) Datei von unserem Server herunterladen, um damit zu arbeiten. Hinweis: Wenn Sie später Ihr Werk mit meiner Lösung vergleichen wollen, dann legen Sie bitte die *.csv in das Verzeichnis C:\Data\Kleinbrot\ oder passen Sie den Pfad der Quelle in meiner Version entsprechend Ihren Gegebenheiten an.
Ein Import über Daten | Aus Text/csv wird den bekannten und erwarteten Dialog öffnen und sie können die Daten in Power Query einfügen:
Der Import-Dialog für die csv-Daten
Das klappt wie erwartet und gewohnt und Sie können die Daten nach Wunsch Laden oder Sie Klicken auf Daten transformieren. Ich gehe typischerweise den Weg über Laden ▼ | Laden in… und wähle dann die Möglichkeit Nur Verbindung erstellen. Einen wichtigen Schritt sollten Sie bereits an dieser Stelle gehen: Der Spalte Column1 bereits an dieser Stelle das Datenformat Datum und den restlichen Spalten das Format Währung oder Dezimalzahl zuweisen. – Ach ja, ändern Sie bitte nicht den Namen der Abfrage, denn am Schluß dieser Übung werden Sie genau diese Abfrage an die generierten Überschriften anfügen. Alternativ verwenden Sie gerne am Ende den von Ihnen verwendeten Namen.
Damit ist der größte Teil der Vorarbeit erledigt. Es wäre gut, wenn Sie nun die eigentlichen, noch zu ergänzenden bzw. als Ersatz einzufügenden Überschriften erkunden; das geht recht gut, wenn Sie (beispielsweise) in dieser Datei nachsehen und sich die Überschriften merken oder notieren wie auch die entsprechende Reihenfolge. Für die erste Spalte gibt es dort zwar keine Überschrift aber die sollte klar sein… 😉
Wenn Sie sich es ganz bequem machen wollen, dann erstellen Sie sich in Plain Excel eine Tabelle (waagerechte Anordnung) oder Liste (senkrecht untereinander) mit den Überschriften. Ihre Arbeit importieren Sie dann als weitere Abfrage in Power Query. Wenn Sie sich für die Tabelle entschieden hatten, sind ja seitens PQ automatisch die Überschriften Spalte1 bis Spalte8 vergeben worden. Nun einfach im Menüband auf Erste Zeile als Überschriften verwenden Klicken und Sie haben eine leere Tabelle mit den gewünschten Überschriften. Passen Sie den Namen der Abfrage an die Gegebenheiten an: Überschriften. Nun noch Schließen & laden in… und im Dialog Nur Verbindung erstellen wählen.
Falls Sie eine Liste mit den 8 Überschriften in Excel erstellt hatten, werden Sie diese natürlich auch in PQ importieren. Im ersten Schritt wechseln Sie zum Menü Transformieren und wählen dort im Menüband den Punkt Vertauschen. Und mit dem kleinen Unterschied, dass die Überschriften nun in Englisch sind (Column1 .. Column8), ist die weitere Vorgehensweise genauso wie direkt hierüber beschrieben. Hinweis: Im Grunde genommen ist das alles absolut korrekt; da es aber in jedem Falle besser ist, Berechnungen so weit wie möglich per Power Query durchführen zu lassen, sollten Sie diese Spalte löschen. Sie wird in der Auswertung sowieso nicht gebraucht.
Die Vorgehensweise mit den Überschrift-Daten in Excel ist zwar recht bequem und hat den Vorteil, dass Sie Änderungen an den Überschriften rasch vornehmen können. Das kann aber auch rasch zum Nachteil werden, wenn andere User den Zweck dieser Daten nicht erkennen und diese (aus welchen Gründen auch immer) verändern oder sogar löschen. 🙁 Hier bietet sich die Möglichkeit an, die erforderliche (leere, nur aus der Überschrift bestehnde) Abfrage direkt und ausschließlich in Power Query zu erstellen. Wechseln Sie dazu erforderlichenfalls zum Menüpunkt Start und ganz rechts in der Gruppe Neue Abfrage den Eintrag Daten eingeben:
Hier erstellen Sie eine neue, individuelle Tabelle
Rasch öffnet sich ein Dialog, der auf den allerersten Blick nicht gerade aussagekräftig ist:
Der Dialog zum Erstellen einer Tabelle
Auf den zweiten Blick werden sie aber gewiss erkennen, dass es sich um eine Tabelle mit einer Spalte und einer Zeile handelt. Tragen Sie in das grün hinterlegte Feld die erste Überschrift Datum ein. Anschließend Tab und in die zweiten Spalte kommt dann der Text Brot. Vervollständigen Sie erst einmal die Werte bis zu Sonstiges. Und die achte Spalte mit der berechneten Summe werden sie erst gar nicht mit verwenden. Im unteren Viertel des Dialogfensters geben Sie im Textfeld Name: den Wert Überschriften ein. Das ganze sieht nun so aus:
Die fertige selbst erstellte Tabelle
Nach einem OK hat Power Query für Sie automatisch eine neue Abfrage mit dem Namen Überschriften erstellt. Diese Query ist die Basis für jene Abfrage, die das Ziel sein wird. Prinzipiell könnten Sie nun direkt die Daten der Abfrage Kleinbrot_2014_365_Tage anhängen, aber mir ist Transparenz wichtiger als eine schnelle Lösung. Also gehe ich folgenden Weg: Immer noch in der Abfrage Überschriften: Start | Kombinieren | Abfragen anfügen ▼ | Abfragen als neu anfügen. Im Anfügen-Dialog wählen Sie als Zweite Tabelle: Kleinbrot_2014_365_Tage und bestätigen mit OK. Die letzte Spalte mit den berechneten Werten wurde zwar automatisch mit angefügt, sollte aber sofort entfernt werden. Jetzt nur noch Erste Zeile als Überschrift verwenden im Menüband anklicken und das Ziel ist erreicht.
Und da Herr Kleinbrot sich recht gut mit dem klassischen Excel auskennt, möchte er diese Daten per PivotTable auswerten. Er merkt recht schnell, dass das mit dieser Datenbasis praktisch nicht möglich ist; Kreuztabellen sind für das Aussehen gut, für alle denkbaren Auswertungen sollten die Daten als geordnete Liste vorliegen. Aber er schaut einfach einmal hier in unserem Blog nach und kommt nach kurzer Überlegung auch rasch zum Ziel.
Und noch ein Hinweis: Der hier aufgezeigte Weg ist aus meiner Sicht sehr benutzerfreundlich. Wenn Sie sehr tief in der PQ-Materie stecken, werden Sie vielleicht einen anderen Weg wählen, der verschiedene Funktionen der M‑Sprache verwendet. Ich finde das übertrieben und möchte Ihnen das hier nicht „antun”. Ich selber würde auch diesen hier aufgezeigten Weg gehen.
Rückmeldungen / Feedback gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 3,50 € freuen … (← Klick mich!)