Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung
Naturgemäß beginnen Sie wieder mit einer neuen, leeren Excel-Arbeitsmappe. Die ersten Schritte für den Import der Daten in den Power Query-Editor kennen Sie aus dem vorhergehenden Kapitel (siehe auch hier). Wenn Sie etwas Training brauchen oder haben möchten, führen Sie das ganze noch einmal durch. Ich habe Ihnen aber auch versprochen, dass Sie den Weg des eigentlichen Imports auch ohne diverse Mausklicks übernehmen können.
Der einfachste Weg ist aus meiner Sicht, die von Ihnen im letzten Kapitel erstellte Excel-Datei zu kopieren und gleich unter einem anderen Namen (beispielsweise per F12) zu speichern. Ich kann mir aber auch vorstellen, dass Sie das von mir erstellte Ergebnis für die Kopie nutzen, damit die von mir angesprochene Schritte die gleichen Bezeichnungen auf die gleiche Reihenfolge haben. Um nicht irgendwelche Überreste im Speicher oder der Zwischenablage zu behalten, schließen Sie Excel komplett und öffnen dann die gerade erstellte Kopie noch einmal. Sicherheit geht vor… 😉
Achten Sie darauf, dass Sie im rechten Seitenfenster direkten Zugriff auf Abfragen und Verbindungen haben. Öffnen Sie dort erforderlichenfalls die Abfrage Kleinbrot_18-22. Sie haben nun im Power Query-Editor die gleiche Datei mit dem identischen Ablauf wie im vorherigen Kapitel auf dem Schirm. Im rechten Seitenfenster im Kasten Angewendete Schritte hat die siebte Zeile den Text Geänderter Typ; markieren Sie die Zeile darunter mit dem Text Andere entfernte Spalten1. Rechtsklick auf diesen Text und wählen Sie im Kontextmenü Bis zum Ende löschen. Den Dialog mit der Sicherheitsabfrage bestätigen Sie mit OK. Hinweis: In Plain Excel hätten Sie nach solch einer Aktion eine Chance, das ganze mit StrgZ wieder rückgängig zu machen. Das ist in Power Query nicht machbar, die Löschung ist nach der Bestätigung endgültig und unwiderruflich!
Gehen wir gemeinsam einfach einmal davon aus oder tun wir so, dass in der ersten Zeile jedes Arbeitsblatts nur das Wort Umsätze ohne angefügte Jahreszahl steht. Dann werden Sie sich vielleicht fragen, wie die zu den Daten passende Jahreszahl generiert werden kann. Ein kleiner Denkanstoß: In der Spalte Source.Name steht der komplette Dateiname. Und in den jeweiligen Feldern dieser Spalte ist ja auch die Jahreszahl direkt vor dem Punkt, welcher den Dateinamen von der Erweiterung trennt, vermerkt. Und vor der Jahreszahl ist in jedem Fall ein Leerzeichen. Diese beiden Merkmale können Ihnen helfen, die Jahreszahl in jeder Zeile zu separieren. Da Sie vom Filenamen ausschließlich die Jahresbezeichnung brauchen, kann der restliche Text gelöscht werden. Ich zeige ihn hier zwei Möglichkeiten auf, wie Sie das Ziel erreichen können.
In allen Excel-Versionen (mit PQ)
Wechseln Sie zum Menü Transformieren. In der Gruppe Textspalte wählen Sie Extrahieren | Text zwischen Trennzeichen und geben als Startkennzeichen das Leerzeichen und als Endtrennzeichen den Punkt ein. Wenn Sie das gedanklich nachvollziehen (Schreibtischtest) werden Sie feststellen, dass das Ergebnis in diesem speziellen Fall korrekt sein wird aber mit Sicherheit nicht dem Wunsch entsprechen würde, wenn bei Kleinbrot_Kreuztabelle statt des Unterstrichs _ ein Leerzeichen wäre. Darum ein Klick auf Erweiterte Optionen und an erster Position des neu erschienenen Bereichs können Sie bei Auf Startkennzeichen überprüfen per Dropdown einstellen, dass Vom Ende der Eingabe aus geprüft werden soll. Und damit wird das Ergebnis auch wunschgemäß sein. 🙂
Okay, nun werden Sie die beiden letzten Spalten Column8 und Column9 löschen. Markieren Sie nun in dieser Reihenfolge die zweite Spalte (Bäckerei Kleinbrot) und dann per Shift oder Strg die erste Spalte (Source.Name). Ein Rechtsklick in eine der markierten Überschriften und im Kontextmenü ein Klick auf Spalten zusammenführen. Als Trennzeichen wählen Sie das Leerzeichen. Den vorgegebenen Spaltennamen können Sie erst einmal so lassen. OK – Löschen Sie nun über das Menü Start auf die bekannte Weise die ersten beiden Zeilen und anschließend Erste Zeile als Überschrift verwenden. Die Überschrift der ersten Spalte werden Sie an dieser Stelle auf Monat & Jahr ändern.
Um die Daten erst einmal zu „bereinigen”, also dergestalt zu filtern, dass nur die eigentliche Monate erhalten bleiben gehe ich einen Weg, der „dreimal um die Ecke gedacht” wahrscheinlich nicht so vorgesehen ist; aber es klappt, ist zielführend, und nur das ist aus meiner Sicht wichtig.
In den ersten 12 Zeilen steht ja tatsächlich ein kalendarischen Wert. Januar 2018, Februar 2018, … bis Dezember 2018. Und die nächsten 8 Zeilen enthalten zwar eine Jahreszahl, sind aber keineswegs einem kalendarischen Datum ähnlich. Und genau das nutze ich, um die „echten” Monate klar zu definieren. Ändern Sie den Datentyp der Spalte Monat & Jahr auf Datum. Prompt haben exakt jene Zeilen, die nicht zu einem Datum umgewandelt werden konnten die Meldung / den Wert Error, also ein Fehler. Start | Zeilen verringern | Zeilen entfernen | Fehler entfernen. Bingo, das hat‘s gebracht. Die Abfrage besteht jetzt nur noch als 60 Zeilen und jeder Monat der fünf Jahre ist hier aufgeführt.
In aktuellen Excel-Versionen
Im Grunde genommen ist fast alles wie gerade beschrieben für alle Version des Excel. Nur das herauslösen der Jahreszahl geht hier auch etwas anders und diese Systematik können Sie natürlich auch an vielen anderen Stellen in ihrem PQ-Leben verwenden. 😉 Sie befinden sich im rechten Seitenfenster bei Angewendeten Schritte dort, wo Sie mit Punkt und Leerzeichen das Jahr ausgelesen haben; also dort, wo Sie den Text zwschen den Trennzeichen extrahiert hatten.
Die erste Spalte ist markiert. Transformieren | Spalte teilen | Nach Wechsel von Nicht-Ziffer zu Ziffer, anschließend markieren Sie die zweite Spalte (Source.Name.2) und noch einmal Spalte teilen | Nach Wechsel von Ziffer zu Nicht-Ziffer. Löschen Sie nun die erste und die dritte Spalte und Sie können dort wieder einsteigen, wo Sie weiter oben die Jahreszahl extrahiert hatten.
Welche der beiden aufgeführten Möglichkeiten für Sie angenehmer, praktikabler, besser, … ist, werden Sie von Fall zu Fall gewiss selber entscheiden. Die zweitgenannte Variante ist gewiss in sehr vielen (auch anderen) Fällen Ihres PQ-Lebens ein exzellenter Helfer.
Die weiteren Schritte wie Entpivotieren, Datentyp auf Währung anpassen, etc. kennen Sie ja und bedüfen hier keiner Erwähnung. Und ausnahmsweisehabe ich hier mein Ergebnis nicht zum Download angeboten, Sie packen das gewiss auch so!
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. 2,50 € freuen … (← Klick mich!)