Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung
In den ersten beiden Teilen dieses Projekts haben Sie ausschließlich mit Daten eines einzigen Files und dort auch einer einzigen Tabelle bzw. eines einzigen Arbeitsblatts gearbeitet; und die kalendarischen Daten waren alle aus dem gleichen Jahr. Dadurch war es auch kein großer Aufwand, die Jahreszahl für die Umwandlung des Monatsnamens in ein korrektes Datum per Hand einzugeben. In diesem und dem folgendem Beitrag werden Sie per Power Query mehrere Jahrgang-Files einlesen um diese dann in eine einzige Query zusammenzufassen und auf zwei unterschiedliche Weisen die Jahreszahl aus den importierten Daten extrahieren, um daraus anschließend ein kalendarisches Datum zu erstellen.
Hinweis: Warum schriebe ich immer „kalendarisches Datum” und nicht einfach (nur) „Datum”? Nun ja, in beispielsweise einer Datenbank und natürlich auch in Excel befinden sich typischerweise mehrere Daten. „Daten” ist also das Plural (Mehrzahl) von…? Richtig von „Datum”! Und da aus dem Kontext nicht immer klar hervorgeht, um welchen Typ von Datum oder Daten es geht, verwende ich diese Schreibweise. Konstrukte wie „Datums”, „Datümer”, etc. für die kalendarischen Daten finde ich keineswegs als kreativ sondern einfach nur unschön.
In dieser zip-gepackten Datei habe ich Ihnen 5 einzelne Arbeitsmappen zur Verfügung gestellt. Es sind Files der Jahre 2018 (die gleiche Datei, die sie bislang schon verwendet haben) bis zum Jahr 2022. Der Aufbau der Arbeitsblätter ist notwendigerweise stets identisch, die Umsatzzahlen habe ich mit einer Zufallsmultiplikation in den einzelnen Jahren angepasst. Falls noch nicht geschehen, laden Sie die zip-Datei herunter und entpacken diese beispielsweise im Ordner C:\Data\Kleinbrot_18-22.
Mit PQ ist es problemlos möglich, alle in Excel importier-fähige Dateien mit wenigen Mausklicks in den Power Query-Editor zu importieren. Und es wird Sie nicht verwundern, dass es auch hier mehrere Wege bzw. Möglichkeiten gibt. Unter dem Blickwinkel, dass die primäre Zielsetzung eine Extraktion der Jahreszahl des entsprechenden Files ist, zeige ich Ihnen insgesamt 2 Wege auf. In diesem Abschnitt des Projekts gehen Sie den etwas einfacheren Weg und nutzen die Tatsache, dass in jeder Datei in der ersten Zeile der Daten nach dem Wort Umsatz die Jahreszahl vermerkt ist.
Gehen Sie zur Erreichung dieses Ziels in Excel über Daten | Daten abrufen | Aus Datei | Aus Ordner und navigieren Sie im File-Dialog zu jenem Ordner, wo Sie die zip-Datei entpackt haben. In meinem Fall wäre das (wie gerade vorher beschrieben) C:\Data\Kleinbrot_18-22. Nach einem Klick auf Öffnen sehen Sie diesen Dialog:
Der Inhalt des Ordners direkt nach dem Start des Imports
Sie erkennen, dass alle Dateien des Ordners in der Liste aufgeführt sind. Der Einfachheit halber erweitern Sie die Schaltfläche Kombinieren 
und wählen anschließend im Dropdown den untersten Punkt Kombinieren und laden in… Im folgenden Dialog markieren Sie den Eintrag Tabelle1, was sich dann so darstellt:
Alle Files wurden in1 Abfrage zusammenfeührt
Nach einem Klick auf OK öffnet sich der Dialog wo Sie idealerweise den Punkt Nur Verbindung erstellen markieren. Im rechten Seitenfenster erkennen Sie nun eine größere Menge an Schritten, welche Power Query ohne ihr direktes Zutun bereits erledigt hat:
Vielleicht etwas unübersichtlich, aber komprimieren hilft… ☺
Klicken Sie hier zwecks besserer Übersicht erst einmal auf das von mir durch einen Pfeil markierte Dreieck und öffnen anschließend die Abfrage Kleinbrot_18-22 (Nur Verbindung) auf beliebigem Wege.
Hinweis: Bis zu diesem Punkt ist die Vorgehensweise im nächsten Kapitel PQ-Projekt/Workshop „Kleinbrot” (4) absolut identisch. Dort werde ich Ihnen auch aufzeigen, wie Sie dann das Vorgehen bis zu diesem Punkt von hier an übernehmen können.
Weiter gehts. Auch wenn hier nicht allzu viele Datenzeilen (Datensätze) gegeben sind folge ich dem Grundsatz, die Menge der zu verarbeitenden Daten so früh wie möglich auf ein zu der Zeit akzeptables Minimum zu reduzieren. Ab der dritten Spalte kann ich ja in Zeile 3 die künftige Überschrift(en) erkennen. Unter diesem Aspekt markiere ich die Spalte Bäckerei Kleinbrot, Shift und Column7; es sind nun alle Spalten markiert, welche ich weiterhin nutzen möchte. Rechtsklick in eine der markierten Überschriften und im Kontextmenü Andere Spalten entfernen auswählen.
In der nunmehr ersten Spalte (Bäckerei Kleinbrot) steht zu Beginn jeden Jahres der Text Umsätze gefolgt von einem Leerzeichen und der entsprechenden Jahreszahl. Und genau das nutze ich, um die Jahreszahl zu separieren. Und Sie schaffen das auch. 😉 Markieren Sie die Spalte Bäckerei Kleinbrot durch einen Rechtsklick in die Überschrift und wählen Sie Spalte teilen | Nach Trennzeichen. Übernehmen Sie die Vorgabe Leerzeichen und bei Aufteilen Klicken Sie zur Sicherheit auf Beim äußersten rechten Trennzeichen. Die erste Spalte wurde umbenannt zu Bäckerei Kleinbrot.1 und die neu erstellte Spalte heißt Bäckerei Kleinbrot.2 und zu Beginn jeden Jahres steht in der Spalte Kleinbrot.2 die Jahreszahl. Darum benennen Sie diese Spalte jetzt auch zu Jahr um.
Um die entsprechende Jahreszahl in jeder Zeile zuzuordnen, Rechtsklick in die Überschrift Jahr | Ausfüllen | Nach unten. Im nächsten Schritt werden Sie auf die bekannte Weise die ersten beiden Zeilen löschen. Und wie auch schon früher werden Sie nun auf Erste Zeile als Überschrift verwenden Klicken. Auch wenn es zum Ablauf nicht wirklich erforderlich ist, ändern Sie die Spaltenüberschriften Column1 und (nun) 2018 auf Monat und (wieder) Jahr.
So weit, so gut. Nun geht es darum, die Zeilen auf das absolute Minimum zu reduzieren. Mit etwas Nachdenken werden Sie mir zustimmen, dass ausschließlich jene Datensätze erhalten bleiben sollen, welche einen Monatsnamen enthalten. Also Januar, Februar, …, Dezember. Das erreichen Sie recht komfortabel, indem sie die Spalte Monat nach genau diesem Kriterium filtern. Also erweitern Sie die Überschrift Monat durch einen Klick auf ▼ und Textfilter | Ist gleich. Im Dialog wählen Sie Weitere statt Standard. In der direkt hierunter gezeigten Abbildung erkennen Sie, nach welchem Muster Sie alle zwölf Monate in den Filter-Dialog eintragen werden:
Mehrere ODER-Verknüpfungen führen zum Ziel
Zugegeben, Sie hätten auch im normalen Filtermenü alle Einträge abwählen und dann die einzelnen Monate wieder mit einem Häkchen versehen oder die nicht erwünschten Zellinhalte abwählen können. Das wäre der gleiche Effekt. Der etwas aufwendigere vorher aufgezeigte Weg bietet sich aber immer dann an, wenn deutlich mehr unterschiedlicher Zeilen mit nicht erwünschten Inhalten gegeben sind. Und natürlich können Sie diese Positiv-Auswahl auch bei anderen Gelegenheiten nutzen.
In den folgenden Schritten werden Sie aus den beiden ersten Spalten ein „echtes” Datum des Monatsletzten des entsprechenden Jahres generieren. Beginnen Sie damit, (in dieser Reihenfolge) Monat und Jahr zu markieren. Ein Rechtsklick in eine der beiden markierten Überschriften und Spalten zusammenführen. Im Dialog legen Sie als Trennzeichen das Leerzeichen fest und als Neuer Spaltenname verwenden Sie Monat & Jahr. Markieren Sie nun nur Monat & Jahr, wechseln Sie zum Menü Transformieren und Datum | Analysieren. Gleich danach Datum | Monat | Monatsende auswählen und alle 60 Zeilen haben in dieser Spalte nun den Wert des jeweiligen Monatsletzten, dem Ultimo.
Das Ziel ist fast erreicht. Rechtsklick in Monat & Jahr | Andere Spalten entpivotieren und die Abfrage ist bereit, ein letztes Mal per Datei | Schließen & laden geschlossen zu werden. Wenn Sie nun (ähnlich wie auch schon vorher) im rechten Seitenfenster per Rechtsklick auf Kleinbrot_18-22 (Nur Verbindung) Klicken und im Dialog Laden in… anschließend PivotTable-Bericht wählen, haben Sie eine fast perfekte Basis für eine Pivot-Auswertung über mehrere Jahre. Wirklich perfekt wäre aus meiner Sicht, wenn die Spaltenüberschriften bereits in PQ den Gegebenheiten angepasst werden würden und die Umsätze auch als Datentyp Währung formatiert wäre.
Ja, natürlich auch hier mein Ergebnis zum Download. 😎 Die kleinen Anpasungen in der PivotTable habe ich auch dort per Hand vorgenommen, nicht in Power Query.
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,00 € freuen … (← Klick mich!)