Xtract: Am Beispiel der Berechnung von Arbeitstagen für ein Projekt auf der Basis der gesamten Laufzeit wird hauptsächlich über Kombinieren und per … Join Zusammenführen ein Extrakt erstellt. und berechnet.
Wissensstand: Level 3 ⇒ Excel GUT!, mindestens Basis-Kenntnisse in Power Query.
Arbeitstage (ohne Feiertage und ohne Betriebsurlaub) mit Power Query berechnen, Linker Anti-Join Join
Laden Sie diese Arbeitsmappe, um das Beispiel besser nachvollziehen zu können. Im Blatt Projekte erkennen Sie die Daten für 3 Projekte, deren Start- und Ende-Datum und ab Spalte E die Überschriften für die Monate Oktober 2019 bis Dezember 2020 (welche aber umformatierte kalendarische Daten des jeweiligen Monatsersten sind). In diese Spalten sollen die Werte der entsprechenden Arbeitstage je Projekt eingetragen werden. Im WorkSheet Arbeitsfrei sind die Feiertage (einschließlich Heiligabend und Silvester) sowie die Zeit des Betriebsurlaubs eingetragen.
Im ersten Schritt werden Sie alle drei tabellarischen Darstellungen in eine Intelligente Tabelle formatieren und diesen Tabellen dann auch einen „sprechenden” Namen geben. Ich verwende Projekte, Feiertage und Betriebsurlaub. Anschließend importiere ich diese 3 Tabellen nacheinander in Power Query und speichere jede einzelne direkt nach dem Import erst einmal als Nur Verbindung ¿. An dieser Stelle aber bereits ein Hinweis: Sie fördern die Performance, wenn Sie den Bereich der Intelligenten Tabelle Projekte auf den Bereich der Spalten A:D (und natürlich letzte Datenzeile) einschränken, denn prinzipiell ist der Bereich ab Spalte E in PQ selbst erst einmal überflüssig. Außerdem ist dann auch der während des Imports wahrscheinliche Hinweis, dass die Formeln in statischen Text umgewandelt werden, hinfällig. 😉 Hinweis: In E1 steht noch ein „normales” Datum, in F1:S1 habe ich jedoch die Funktion EDATUM() verwendet, welche automatisch den Monatsbeginn des Folgemonats berechnet und als Datum in die Zelle schreibt.
Öffnen Sie nach dem Import wieder die Abfrage Projekte, beispielsweise durch einen Doppelklick auf den entsprechenden Eintrag im rechten Seitenfenster. Sollten Sie die Daten bis Spalte S importiert haben, dann löschen Sie alle Spalten mit den Monatsnamen. Und da PQ sehr subtil zwischen den Datentypen unterscheidet, formatieren Sie die Spalten Von und Bis als (nur) Datum. Das stellt sich im Anschluss so dar:
Der angepasste Datentyp des Datums ist ausgesprochen hilfreich
Jeder Tag im Zeitbereich Von … Bis
Im ersten Schritt werden Sie für jeden Tag des einzelnen Projekts eine einzelne Datenzeile generieren. Dabei werden erst einmal auch Wochenenden und Feiertage mit einbezogen. Das geschieht idealerweise so:
- Wechseln Sie zum Menü Spalte hinzufügen, Benutzerdefinierte Spalte.
- Im Dialog bei Neuer Spaltenname beispielsweise Kal. Daten eintragen.
- Bei Benutzerdefinierte Spaltenformel tragen Sie diese Formel ein:
= List.Dates([Von], Duration.Days([Bis]-[Von])+1,
#duration(1,0,0,0))
wobei Sie die Überschriften einschließlich der eckigen Klammern am einfachsten per Doppelklick auf den Eintrag im Bereich Verfügbare Spalten übernehmen können (obwohl es natürlich bequemer ist, die Funktion hier zu kopieren und im Dialog einzufügen). 😎
Achten Sie auch darauf, dass die Sprache M die exakte Groß- Kleinschreibung wie hier dargestellt erfordert. Weiter geht’s …
- Nach einem OK ist eine neue Spalte Kal. Daten erstellt worden, wo in jeder Zelle dieser Spalte der Begriff, das Schlüsselwort List steht.
- Erweitern Sie die Spalte durch einen Klick auf den Doppelpfeil
in der Überschrift und im Kontextmenü wählen Sie Auf neue Zeilen ausweiten. - Kontrollieren Sie in der jetzt um reichlich Zeilen erweiterte Abfrage, ob der Beginn beim ersten Tag des Zeitraums (Spalte Von) ist, die folgenden paar Zeilen tatsächlich jeweils 1 Tag später sind und ob die letzte Zeile der jeweiligen Projektnummer auch das Datum aus der Spalte Bis enthält.
- Löschen Sie die Spalten Von und Bis, diese werden nicht mehr benötigt. Zugegeben, das ist zu diesem Zeitpunkt noch nicht unbedingt erforderlich, sorgt aber für mehr Klarheit bzw. Übersicht und auch für etwas mehr Performance des Projekts. 💡
Arbeitstage filtern
Zu diesem Zeitpunkt steht jeder einzelne Kalendertag des Zeitbereichs vom ersten bis zum letzten Tag des jeweiligen Projekts in der Abfrage. In den kommenden Schritten werden Sie erst einmal die Wochenenden herausfiltern (löschen):
-
- Achten Sie darauf, das die Spalte Kal. Daten markiert ist.
- Immer noch im Menü Spalten hinzufügen wählen Sie Datum | Tag | Name des Tags, um eine Spalte mit dem Namen des Wochentages einzufügen.
- Filtern Sie diese neu eingefügte Spalte so, dass Sie bei Samstag und Sonntag das Häkchen entfernen.
Der Erfolg dieser Aktion ist, dass die Abfrage statt 847 nun nur noch 604 Zeilen enthält. Auch diese Spalte mit den Namen der Wochentage wird jetzt nicht mehr benötigt, sie kann (und sollte) also ebenfalls gelöscht werden.
Zum derzeitigen Stand sind ja in der Abfrage immer noch die Feiertage und auch die arbeitsfreie Zeit der Betriebsferien aufgeführt. Und in diesem Fall wird es dann auch etwas anspruchsvoller, was die Vorgehensweise betrifft. – Falls das linke Seitenfenster mit der Auflistung der Abfragen nicht sichtbar ist, blenden Sie es ein. ¿ Klicken Sie hier auf den Eintrag Feiertage, um rasch zu dieser Abfrage zu wechseln. Im ersten Schritt ändern Sie auch hier den Datentyp der Spalte FT Datum auf Datum, damit die Datentypen zu denen in der Abfrage Projekte identisch sind.
Sie haben jetzt noch die Gelegenheit, in der Abfrage die Zeilen 15;18 (24. und 31. Dezember) zu löschen, falls an diesen „unechten” Feiertagen doch gearbeitet wird. ¿ Natürlich können Sie auch die Quelldaten entsprechend anpassen; nach dem erneuten Öffnen und einem eventuellen Aktualisieren sind die beiden Tage dann auch nicht mehr in der Query. Hinweis in Sachen „Anzahl der Datensätze: Ich belasse es bei den beiden „unechten” Feiertagen, denn meistens wird dann in der Verwaltung nicht gearbeitet.
Wechseln Sie per Klick im linken Seitenfenster wieder zur Query Projekte. Home (bzw. Start) | Kombinieren | Abfragen zusammenführen und es öffnet sich ein Dialogfenster. Wählen Sie im kleinen Textfeld unterhalb Projekte die Abfrage Feiertage. Der untere große Kasten wird automatisch ausgefüllt und das stellt sich nun so dar:
Direkt nach Auswahl der zu verknüpfenden Abfrage ist OK noch deaktiviert
Im folgenden Schritt muss festgelegt werden, welche Felder für einen Vergleich verknüpft werden sollen. Und dafür eignet sich natürlich nur das Datum. Markieren Sie also bei Projekte das Feld Kal. Daten und bei Feiertage FT Datum. Klicken Sie nun in die jeweilige Spalte, die anschließend komplett markiert sein wird.
Danach müssen Sie noch festlegen, wie die Verknüpfung gestaltet werden muss. Die Vorgabe Linker äußerer Join ist nicht zielführend, da dann alle Datensätze aus der ersten (oberen) Abfrage verwendet werden würden und (zusätzlich, falls nicht vorhanden) die übereinstimmenden Werte aus der zweiten (unteren) Query. Da die Feiertage ja (teilweise) bereits eine Untermenge der Projekt-Daten sind würden sich keine Änderungen in der Anzahl ergeben; schließlich wollen Sie ja nicht eine zusätzliche Spalte mit den Bezeichnungen der Feiertage erstellen … 😎
Sie wollen im Endeffekt ja nur die kalendarischen Daten in der ersten Abfrage behalten, die nicht in der unteren Abfrage (mit den Feiertagen) enthalten, also dort aufgeführt sind. Und wenn Sie die Vorgabe der Zeile Join-Art erweitern, dann werden Sie bei der Auswahl Linker Anti-Join … fündig. Es bleiben jene Zeilen erhalten, die beim verLinkten Feld nur in der ersten Abfrage vorhanden sind:
22 von den 604 Zeilen der ersten Query werden ausgeschlossen (nicht übernommen, gelöscht)
In der Statuszeile des Power Query-Editors erkennen Sie nach dem Schließen des Dialogs, dass nur noch 582 Zeilen vorhanden sind. Das stimmt also schon einmal. Die neu erstellte Spalte Feiertage enthält ausschließlich den Wert Table. Erweitern Sie diese Spalte durch einen Klick auf das Doppelpfeil-Symbol rechts in der Überschrift. Sorgen Sie dafür, dass ausschließlich bei FT Datum das Häkchen gesetzt ist. Diese Spalte enthält naturgemäß ausschließlich den Wert null, sie ist darum nicht mehr erforderlich; also einfach löschen. 😉
Hinweis: Wenn Sie etwas mehr Erfahrung mit Power Query gesammelt haben, werden sie die Spalte erst gar nicht erweitern sondern nach dem Abschluss des Kombinierens und der Kontrolle auf die verminderten Zeilen gleich löschen. Für den Einstieg ist aber der etwas längerer Weg des Erkenntnisgewinns wegen durchaus hilfreich.
Und nach exakt dem gleichen Muster werden Sie die die Betriebsferien ausblenden wollen. Aber ein Blick in die Abfrage wird Ihnen schnell klar machen, dass das irgendwie nicht hinhauen kann und auch nicht wird. Im besten Fall (wenn Sie die Formel deutlich angepasst hätten) werden 2 kalendarische Daten in der Query enthalten sein, es sollen aber natürlich alle Tage des Betriebsurlaubs sein; wahrscheinlich aber nur 1 Zeile mit Error als Wert generiert. 😕
Betriebsurlaub, alle Tage als Tabelle generieren
Wechseln Sie zur Abfrage Betriebsurlaub. ¿ Ändern Sie auch hier den Datentyp beider Spalten auf Datum. Nun Spalte hinzufügen, Benutzerdefinierte Spalte und geben Sie im Dialog beispielsweise BU-Datum bei Neuer Spaltenname ein. Als Benutzerdefinierte Spaltenformel schreiben Sie exakt diese Funktion nach dem Gleichheitszeichen:
= {[Betriebsurlaub von]..[Betriebsurlaub bis]}
… also mit den einfassenden geschwungenen Klammern und den 2 Punkten zwischen den Spaltennamen. Falls Sie bereits etwas mehr Erfahrung mit M-Funktionen gesammelt haben werden Sie sagen: „Das sollte korrekt sein”. Sollte… 😥 Das Ergebnis ist ernüchternd. Wie oben schon „angedroht”: Error… Und wenn Sie in die Zelle mit der Fehlermeldung Klicken, wird auch eine Fehleranalyse angezeigt:
Der Operator „..” kann nur auf Zahlen angewendet werden
Speziell für diese Fehlermeldung wurde im rechten Seitenfenster ein neuer Schritt BU-Datum erzeugt. Löschen Sie diese Zeile, bevor sie weiter in der Abfrage arbeiten. – Auch wenn Sie nun vielleicht denken werden, dass in Excel ein Datum doch nur eine anders dargestellte Zahl ist, das trifft zwar auf Excel, nicht jedoch auf Power Query zu. Denn hier sind es zwei durchaus unterschiedliche Datentypen, die nicht kompatibel (miteinander vereinbar) sind. Vielleicht ahnen Sie nun, warum ich die Spalten mit der Datum/Uhrzeit-Formatierung in „nur” Datum umformatiert habe. 💡
Und wie lässt sich das Dilemma beseitigen? Der eine Weg wäre natürlich, die praktisch gleiche Vorgehensweise wie bereits gehabt anwenden. Also im rechten Seitenfenster ein Doppelklick auf den zuletzt protokollierten Schritt und ersetzen Sie die Formel hiermit:
= List.Dates([Betriebsurlaub von],
Duration.Days([Betriebsurlaub bis]-[Betriebsurlaub von])+1,
#duration(1,0,0,0))
Und das klappt dann auch, die erweiterte Spalte BU-Datum ist korrekt. Wenn Sie dennoch die vorher gelernte Form(el) mit den 2 Punkten anwenden wollen müssen Sie grundsätzlich dafür sorgen, dass die beiden Datumswerte in eine Ganzzahl umgewandelt werden. Das geht mit einer Folge von (verschachtelten) Funktionen, welche die Werte vom Typ Datum in eine Ganzzahl umwandeln. Siehe etwas weiter unten …
Für Einsteiger in Sachen PQ ist es gewiss der einfachste Weg, erst den Datentyp der beiden Spalten auf Ganze Zahl anzupassen, die erstgenannte Formel anzuwenden und anschließend entweder alle drei oder nur die neu erstellte Spalte wieder als Datum deklarieren. Und jetzt werden Sie das gleiche Procedere durchführen wie beim Kombinieren der ersten Abfrage mit den Daten der Abfragen Projekte und Feiertage. Auch hier werden Sie die neu generierte Spalte mit dem Inhalt Table löschen.
Wenn Sie eine fortgeschrittene Funktionalität nutzen wollen bietet sich an, erst einmal die (noch) fehlerhafte Formel mit den 2 Punkten in den Editor zu schreiben. Den ersten Teil werden Sie dann in eine einzige kombinierte Funktion „packen”. Erstellen Sie eine neue Benutzerdefinierte Spalte und tragen bei der Spaltenüberschrift einen beliebigen Wert ein oder belassen Sie es bei Benutzerdefiniert. Ergänzen bzw. ändern Sie die fehlerhafte Formel so:
= {Number.From([Betriebsurlaub von])..Number.From([Betriebsurlaub bis])}
… dann wird eine neue Spalte mit den numerischen Werten der kalendarischen Daten erstellt. OK und Sie erweitern die neue Spalte BU-Datum wie gehabt durch einen Klick auf den Doppelpfeil in der Überschrift und Auf neue Zeilen ausweiten. Nach einer Umwandlung in den Datentyp Datum ist der gewünschte Erfolg auch hier gegeben.
Wie auch immer ihre Abfrage in Sachen Betriebsurlaub derzeit aussieht, es sollte im Endeffekt nur die Spalte BU-Datum existieren. Löschen Sie also erforderlichenfalls die beiden Spalten Betriebsurlaub von und Betriebsurlaub bis. Prinzipiell ist dieser Schritt wiederum wegen der besseren Performance sinnvoll.
Und natürlich werden Sie auch in dieser Situation diese Tabelle mit der Abfrage Projekte derart Kombinieren, dass auch die Tage des Betriebsurlaubs aus der Abfrage Projekte herausgefiltert werden. Die Vorgehensweise ist mit der bei den Feiertagen identisch, nur dass Sie hier als zweite Abfrage Betriebsurlaub und die Spalte BU-Datum nutzen.
Eine erste Auswertung
Zu diesem Zeitpunkt befinden sich in 542 Zeilen alle erforderlichen Daten in der Abfrage Projekte. Die Wochenenden, der Betriebsurlaub und die Feiertage sind aus den kalendarischen Daten entfernt. Wenn es Ihnen ausschließlich darum geht, die Gesamt-Anzahl der Arbeitstage für jedes der Projekte zu erfassen, reicht in vielen Fällen eine einfache tabellarische Auswertung. Gehen Sie dazu so vor:
- Um die Basis-Daten der Abfrage Projekte zu erhalten, erstellen Sie von dieser Abfrage ein Duplikat. – Idealerweise werden sie dieser Kopie beispielsweise den Namen Projekte (kumuliert) statt Projekte (2) geben, der Deutlichkeit wegen. 😉
- Markieren Sie nacheinander die Spalten Projekt Nr. und Projekt Bezeichnung.
- Rechtsklick in eine der beiden Überschriften und wählen Sie dann im Kontextmenü Gruppieren nach…
- Übernehmen Sie die Vorgaben und schließen Sie dieses Fenster durch einen Klick auf OK.
Das Ergebnis stellt sich so dar:
Das Ergebnis: Die Gesamt-Anzahl aller einzelnen Projekte
Übersicht der Anzahl (Projekttage)
Sollte dieses soeben erarbeitete Ergebnis ausreichend sein oder Sie brauchen auch nur dieses Format der Auswertung (also in diese Darstellung), dann können Sie dieses Ergebnis per Schließen & laden oder Schließen & laden in… in ein Excel – Tabellenblatt speichern. Alternativ einfach als Nur Verbindung speichern, ohne die Query in ein WorkSheet zu schreiben. Die Aufgabe wäre damit gelöst.
Monatliche Auswertung (grob)
Die eigentliche Forderung des Fragestellers für die Auswertung war aber, dass die monatlichen, nicht die gesamten Arbeitstage berechnet werden. Und um das zu erreichen, wechseln Sie noch einmal zur Abfrage Projekte oder öffnen diese durch einen Klick im linken Seitenfenster. Da die meisten Schritte zu einer neuen Auswertung auf der beschriebenen Basis bereits erfolgt sind, Duplizieren Sie diese Abfrage ¿ auf beliebigem Wege. Ich gebe dieser neu erstellten Query aus Gründen der Übersichtlichkeit einen anderen Namen. Statt der angehängten (2) schreibe ich nach dem Namen der Query beispielsweise (monatliche Liste). Sie könen direkt an dieser Position weitermachen.
Sie werden nun je eine neue Spalte einrichten, wo der Monatsname und das Jahr berechnet werden. Auch wenn es ein kleiner Umweg ist werde ich mit Ihnen zu Beginn zwei Spalten generieren und diese dann zum Wunsch-Ergebnis verbinden. Ich denke, dass diese Vorgehensweise leichter nachvollziehbar ist, als alles „in einem Rutsch” zu machen.
- Markieren Sie die Spalte Kal. Daten.
- Wechseln Sie zum Menü Spalte hinzufügen und wählen Datum | Monat | Name des Monats.
- Lassen Sie die Spalte Kal. Daten markiert, Datum | Jahr | Jahr.
- Markieren Sie nun zuerst die Spalte Monatsname und anschließend mit Strg oder mit Shift die Spalte Jahr.
- Transformieren | (Gruppe) Textspalte | Spalten zusammenführen und wählen Sie im Dialog als Trennzeichen das Leerzeichen.
- Bei Neuer Spaltenname gebe ich entweder Monat & Jahr oder MMMM JJJJ ein. Die zwei markierten Spalten werden nun zu einer einzigen zusammengefasst und dort steht zu jedem Tag die ausgeschriebene Monatsbezeichnung und durch ein Leerzeichen getrennt das Jahr.
Löschen Sie nun die Spalte Kal. Daten. Markieren Sie (von links nach rechts !) alle drei Spalten ¿ und führen Sie in einer der markierten Überschriften einen Rechtsklick durch. Im Kontextmenü wählen Sie dann Gruppieren nach… Übernehmen sie die Vorgaben des Dialogs und Sie werden erkennen, dass für jeden Monat die Anzahl der entsprechenden Arbeitstage berechnet wurde. Für die kompakte Übersicht als 30-zeilige Liste sollte das absolut ausreichen. Schließen & laden oder Schließen & laden in… und auch diese Abfrage hat ihren Platz im Excel-Workbook oder auch nur im Arbeitsspeicher gefunden. 😉
Wunschergebnis Kreuztabelle
Ein Blick auf das Excel-Arbeitsblatt Projekte zeigt ganz klar auf, dass die eigentliche Wunschvorstellung eine Kreuztabelle und keine Liste ist. Die Monate sollen also in Spalten dargestellt werden und die Projekte sowie die dazugehörigen berechneten Arbeitstage in den zum Monat passenden Zeilen. Die Basis ist bereits gelegt und die zuletzt erstellte Abfrage kann und sollte dafür vorbehaltlos verwendet werden.
Wenn Sie das Ergebnis und vor allen Dingen auch den zielführenden Weg der zuletzt erstellten Abfrage erhalten (also nicht verwerfen) wollen, dann werden sie von dieser Abfrage ein Duplikat erstellen (obwohl Sie diese Abfrage nur minimal ergänzen werden). Den Namen dieser Query passen sie entsprechend an, ich schreibe dort zwischen die Klammern das Wort (Kreuztabelle) und lösche natürlich die vorherigen Werte zwischen den Klammern. Der Weg zum Ziel ist nun gewiss deutlich unkomplizierter, als sie es sich vielleicht vorstellen.
- Markieren Sie die Spalte mit den Monatsbezeichnungen (z.B. MMMM JJJJ) und wechseln Sie zum Menü Transformieren.
- Suchen Sie dort in der Gruppe Beliebige Spalte das Symbol für Pivotieren
; ein Klick darauf und im Dialogfenster wählen Sie bei Wertespalte die Zeile Anzahl. - Kontrollieren Sie, ob bei Erweiterte Optionen der Wert Summe vorgegeben ist, erforderlichenfalls wählen Sie diesen Eintrag.
- Jetzt nur noch OK und das war’s auch schon. Das erwünsche Ergebnis erkennen Sie sofort in der Abfrage. Wenn Sie nun diese Abfrage in ein Arbeitsblatt übertragen, haben Sie die Aufgabe erfüllt. Es sei denn … ➡
Die Lösung für Puristen
… es sei denn, Sie möchten eine quasi perfekte Lösung. 😛 Ich finde, dass die bis hierher erarbeitete Lösung gut ist. Aber wenn Sie genau hinsehen werden Sie erkennen, dass es doch einen kleinen Unterschied zwischen Ihrem bisherigen Ergebnis und der Wunschvorstellung gibt. In den Quelldaten ist der (kalendarische) Datenbereich von Oktober 2019 bis Dezember 2020 angegeben, Ihr (eigentlich unser) Ergebnis beginnt aber im November 2019 und endet auch im Dezember 2020.
Zugegeben, eine einzige Spalte hinzuzufügen ist auch in PQ prinzipiell kein wirklicher Aufwand. Aber stellen Sie sich einfach einmal vor, dass in dieser Aufgabe das Geschäftsjahr am 1. Oktober beginnt und am 30. November endet. Dann müssten ja neben dem Oktober 2019 noch die Monate Januar bis November 2021 in die Liste bzw. auch Kreuztabelle eingefügt werden. Und des Lerneffekts wegen sollten Sie auch diesen Schritt vollziehen.
Nein, den „Schleichweg” werde ich Ihnen zwar sagen aber das wäre dann nun wirklich nicht der gewollte Lerneffekt. Sie könnten ja in Excel eine Liste nach dem Muster der Ursprungsdaten mit der Funktion EDATUM() erstellen, die den eben angesprochenen Zeitraum umfasst. Diese Liste könnten Sie importieren und dann an dieser Stelle weiter arbeiten.
Eine Abfrage per Power Query erstellen
Sie können auch in Power Query autonom eine Abfrage erstellen, also ohne den Import externer Daten. Wechseln Sie dazu erst einmal zum Menü Home. Ganz rechts in der Gruppe Neue Abfrage erweitern Sie Neue Quelle | Andere Quellen | Leere Abfrage. PQ legt eine neue, leere Abfrage an, welche sich ausgesprochen unscheinbar darstellt:
Das leere Gerüst einer neuen, internen Abfrage
Interessant … Im linken Seitenfenster ist die neu erstellte Abfrage noch durch grüne Hinterlegung markiert und der eigentliche Abfrage-Bereich „glänzt” durch Leere. In der Eingabezeile steht (natürlich) auch nichts, denn es gibt ja noch keine Daten. Das lässt sich aber recht schnell und auch unkompliziert ändern. Geben Sie in die Eingabezeile die folgende Formel mit dem führenden Gleichheitszeichen in exakt der dargestellten Groß- Kleinschreibung ein:
= {Number.From(#date(2019,10,1))..Number.From(#date(2021,09,30))}
Prinzipiell kennen Sie den Aufbau solch einer Formel, die Funktion #date() sollte aufgrund der hier dargestellten Daten selbsterklärend sein. Ansonsten bietet hier Microsoft eine gute Hilfe für diese Funktion im Internet an. Nach einem Klick in den leeren Bereich unterhalb der Eingabezeile oder einfach auch nur Zeilenschaltung / Enter erstellt Power Query eine Liste aller Tage des angegebenen Bereichs, hier allerdings als „serielle Zahl”. Da eine Typ-Umwandlung bei einer PQ-Liste nicht direkt möglich ist und die Daten sowieso später als Abfrage vorliegen müssen, Klicken Sie im Menüband auf die Schaltfläche Zu Tabelle und übernehmen Sie die Vorgaben des Dialogs ohne Änderung. Jetzt ändern Sie den Datentyp der einzigen Spalte auf Datum.
Es liegt in der Natur der Sache, dass hier entschieden zu viele Daten generiert worden sind; mehr als 30 mal zu viel, denn Sie brauchen ja nur den jeweils Monatsersten. Auch hier führen viele Wege zum Ziel. Ich denke, dass folgende Vorgehensweise ein hohes Maß an Transparenz bietet:
- Markieren Sie erforderlichenfalls die einzige Spalte Column1.
- Menü Transformieren, Datum | Monat | Monatsbeginn sorgt dafür, dass für jedes bisherige Datum nur der Monatserste in der Zelle steht.
- Rechtsklick in die Überschrift und Duplikate entfernen. Die Abfrage enthält jetzt noch 24 Zeilen, also sind die Monatsersten exakt beider Geschäftsjahre erfasst.
Sie haben jetzt entweder die importierte Tabelle aus Excel oder die eben erstellte Abfrage auf dem Bildschirm. Das weitere Vorgehen kennen Sie schon. Sie erstellen über Spalte hinzufügen, Datum | Monat | Name des Monats eine neue Spalte und auf die gleiche Weise eine Spalte für das Jahr. Anschließend die beiden neu erstellten Spalten Zusammenführen und das Leereichen als Trenner verwenden. Die erste Spalte mit dem Datum hat ihren Zweck erfüllt und sollte gelöscht werden. Wenn Sie möchten, können Sie nun noch die Überschrift Zusammengeführt in beispielsweise Monate ändern. Und um konsequent die Namen der Abfragen „sprechend” zu gestalten, gebe ich dieser Query den Namen qry_Monate. Der Präfix „qry_” ist zwar nicht erforderlich, dient aber aus meiner Sicht der besseren Unterscheidbarkeit zum Spaltennamen.
Ein kleiner Schönheitsfehler …
Bleiben Sie in dieser Abfrage qry_Monate. Home | Kombinieren | Abfragen zusammenführen und wählen Sie als zweite Abfrage Projekte (monatliche Liste). Selbstredend werden die beiden Spalten mit den Monatsnamen verknüpft. Nur noch ein kleiner Kontroll-Blick:
In der ersten (oberen) Abfrage ist Oktober der erste Monat
Diese Einstellungen sind korrekt. Einschließlich des Join-Art: Linker äußerer Join (alle aus erster, übereinstimmende aus zweiter. OK und die zweite, neu generierte Spalte durch Klick auf den Doppelpfeil in der Überschrift erweitern. Da die Spalte MMMM JJJJ mit Monate identisch ist, entfernen Sie das Häkchen bei MMMM JJJJ und erforderlichenfalls auch jenes bei Ursprünglichen Spaltennamen als Präfix verwenden.
Das sieht doch ganz passabel aus, oder?
Ist das Ergebnis wirklich in Ordnung? Wo ist der Oktober 2019? Okay, wenn Sie probeweise die Spalte Monate nach Oktober filtern werden Sie erkennen, dass es diese Zeile doch noch gibt. Wenn auch an unerwarteter Stelle. Ungeachtet dieser kleinen Ungereimtheit erstellen Sie doch einfach auch hier eine Kreuztabelle aus den vorhandenen Daten. Markieren Sie also die Spalte Monate, Wechsel zum Menü Transformieren und wählen Sie dort Pivotieren. Als Wertespalte werden sie natürlich wieder Anzahl wählen. Und es wird Sie nicht überraschen, dass der Oktober 2019 auch hier an falscher Stelle steht. Soweit der Schönheitsfehler.
Ursache und Korrektur
Die Ursache für dieses unerwartete Phänomen ist auch mir nicht vollkommen eingängig. Und die Systematik habe ich auch noch nicht verstanden. Es ist offensichtlich, dass Power Query diesen Datensatz an den Beginn der Gruppe von Datensätzen verschoben bzw. sortiert hat, die (auch) keine Daten in der verknüpfen Projekte-Abfrage enthalten haben. Würden in den Quelldaten der Ursprungstabelle (Excel) auch Lücken in der Reihenfolge existieren, wären diese auch in den unteren Bereich der Abfrage qry_Monate verschoben worden.
Die einfachste und offensichtlichste Möglichkeit wäre natürlich, die fehlerhaft angeordnete Spalte(n) einfach von Hand an die korrekte Position zu verschieben. Aber das kann ja nicht der Sinn der Sache sein, denn eine der großen Stärken von Power Query ist ja, dass veränderte Quelldaten automatisch erkannt und korrekt ausgewertet (sprich formatiert) werden. Dieser Weg wäre also nur für eine einmalige Aktion denkbar.
Natürlich gibt es einen Ausweg, der zwar recht ungewöhnlich scheint aber doch ausgesprochen effektiv wirkt. Sie befinden sich immer noch in der Abfrage qry_Monate. Markieren Sie im rechten Seitenfenster die Zeile Entfernte Spalten oder falls Sie der Spalte einen anderen Namen gegeben haben die Zeile Umbenannte Spalten direkt darunter. Spalte hinzufügen, Indexspalte und bestätigen Sie, dass Sie einen Schritt einfügen wollen. Markieren Sie anschließend bei Angewendete Schritte die Zeile Erweiterte Projekte (monatliche Liste) und überprüfen Sie, ob sich etwas in der Reihenfolge geändert hat.
Nein, der Oktober 2019 ist immer noch nicht an erster Stelle. Und die Reihenfolge der Spalte Index scheint auch in Ordnung zu sein. Oder doch nicht? Natürlich nicht, denn da sie ja nur auf die Schaltfläche Indexspalte geklickt haben, ohne sie zu erweitern und ohne eine Änderung vorzunehmen, beginnt die Indexspalte von Haus aus mit 0. Sortieren Sie die Spalte Index aufsteigend und siehe da, die Abfrage beginnt mit dem Monat Oktober 2019. 😀 Löschen Sie nun die Spalte Index, sie hat ihren Dienst getan. Und wenn Sie nun im rechten Seitenfenster zum letzten aufgezeichneten Schritt wechseln werden Sie erkennen, dass auch die Kreuztabelle in der gewünschten Reihenfolge existiert. Mit einer kleinen auch für mich derzeit nicht so ganz nachvollziehbaren Ausnahme: Der erste Datensatz ist leer; komplett leer. Home, Zeilen verringern | Zeilen entfernen | Leere Zeilen entfernen und auch dieses Problem ist dauerhaft beseitigt.
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag Ihrerseits z.B. 5,00 € freuen …
(Und wenn Sie mir Ihre E‑Mail mitteilen, sende ich Ihnen dann auch die komplette erarbeitete Muster-Datei)