Geburtstag innerhalb der nächsten x Tage + Verteiler

Geburtstagsliste, innerhalb der nächsten x Tage, nach Niederlassungen aufgeteilt

In der Zentrale eines größeren Betriebes mit 4 Standorten soll wöchentlich einer Liste erstellt werden, wo getrennt für jede Niederlassung aufgeführt wird, welche Mitarbeiterin bzw. welcher Mitarbeiter heute bzw. in den kommenden 14 Tagen Geburtstag hat. Nutzen Sie die einzige Tabelle dieser Datei, um die 4 getrennten Listen/Tabelle per Power Query zu erstellen und als PDF zu speichern.

Zugegeben, für eine einmalige Aktion wäre der Aufwand gewiss etwas hoch. Da würde ich wahrscheinlich selber auch nur mit Formeln und Filtern in plain Excel arbeiten. Aber da  wöchentlich eine solche Liste für jede Niederlassung erstellt werden soll und naturgemäß auch die Liste der Mitarbeiter andauernden Veränderungen unterliegt, kann Power Query durchaus eine echte Hilfe sein. 💡 

Ausnahmsweise werden Sie die Daten mit den Namen der Mitarbeiter nicht zu Beginn importieren und anschließend als Tabelle formatieren. Sie greifen von einem noch leeren Workbook auf diese externe Datei zu. Dazu erstellen Sie in Excel eine neue, leere Arbeitsmappe. Über das Excel 2016-Menü Daten | Neue Abfrage | Aus Datei | Aus Arbeitsmappe greifen sie auf die Datei Geburtstag_innerhalb_x_Tagen.xlsx zu, indem Sie das File im entsprechenden Ordner auswählen und dann auf Importieren klicken. Da nur ein Arbeitsblatt existiert, klicken Sie auf den Eintrag Tabelle1. Rechts wird sofort ein Preview eingeblendet und da es die richtigen Daten sind ein Klick auf Bearbeiten. Und Sie werden auch rasch erkennen, dass das Datum im ANSI-Format ohne ihr Zutun in das DE-Format umgewandelt wurde (sofern ihr Rechner auf Gebietsschema Deutschland eingestellt ist).

Mir ist klar, dass Sie jetzt verschiedene Einzelschritte unternehmen, die nicht in jedem Fall unbedingt erforderlich sind. Allerdings haben Sie so besser die Möglichkeit, die Schritte zum Ziel auch nachvollziehen zu können. Es werden innerhalb des Abfrage-Editors Hilfsspalten generiert, die direkt vor der Erstellung der Listen wieder entfernt werden. Gehen Sie über das Menü Spalte hinzufügen und dort ein Klick auf Benutzerdefinierte Spalte. Verwenden Sie einen „sprechenden“ Spaltennamen, beispielsweise Geburtstag aktuelles Jahr. Bei Benutzerdefinierte Spaltenformel geben Sie folgendes ein:
=#date(Date.Year(DateTime.LocalNow()),
Date.Month([Geburtstag]), Date.Day([Geburtstag]))

wobei sie darauf achten werden, dass die Groß- Kleinschreibung exakt so ist, wie hier dargestellt. Ein- oder mehrzeilig ist nicht relevant. Das gilt übrigens für alle Formeln und Funktionen, die sie in der Sprache M verwenden. – Das Ergebnis ist erwartungsgemäß der Geburtstag jedes Mitarbeiters im aktuellen Jahr. Darauf basierend wird nun festgestellt, ob dieses Datum heute ist bzw. innerhalb der nächsten 14 Tage liegt.

Um die restlichen Formeln für die Berechnung so transparent wie möglich zu halten, generiere ich einfach eine Spalte mit dem aktuellen, dem heutigen Datum. Dazu wiederum Benutzerdefinierte Spalte und geben Sie als Überschrift Heute ein danach verwenden Sie diese Funktion:
=DateTime.LocalNow()
und weisen Sie anschließend diese Spalte den Datentyp (nur) Datum zu.

In einem weiteren Zwischenschritt geht es darum festzustellen, ob der Geburtstag des aktuellen Jahres innerhalb des definierten Zeitraums liegt oder nicht. Mit folgender Formel innerhalb einer Benutzerdefinierten Spalte erreichen Sie, dass ein TRUE oder FALSE berechnet bzw. ausgegeben wird:
[Geburtstag aktuelles Jahr] >= [Heute]
and [Geburtstag aktuelles Jahr] <= Date.AddDays([Heute], 14)

Sie können die Formel in 1 oder -wie hier gezeigt- in mehrere Zeilen schreiben. Die Zeilen werden nicht verknüpft. – Damit ist der größte Schritt geschafft. Filtern Sie die Spalte Geburtstagsliste nach TRUE und es bleiben in der Abfrage nur noch jene Mitarbeiter sichtbar, die heute einschließlich der kommenden 14 Tage Geburtstag haben. – Die 3 Hilfsspalten haben ihre Schuldigkeit getan, Geburtstag aktuelles Jahr, Heute, Geburtstagsliste können also gelöscht werden.

Nun soll ja für jede Niederlassung eine Liste mit den aktuellen „Geburtstagskindern“ erstellt werden. Dazu duplizieren sie diese Abfrage genau 4 mal. Wenn im linken Seitenbereich nur der schmale Streifen mit dem Text Abfragen und dem ein Größer-Symbol darüber sichtbar ist, dann klicke ich auf den Text oder >, um in einem Seitenfenster die Auflistung der Abfragen zu sehen. Derzeit ist das ja nur Tabelle1. Ein Rechtsklick auf den Namen der Abfrage und sie wählen nicht Duplizieren sondern die Auswahl Verweis. Dadurch werden die Kopien dynamisch. Achten Sie aber darauf, dass Sie die Kopie jeweils von Tabelle1 erstellen.

Geben Sie nun jeder der vier erstellten Abfragen den Namen einer Niederlassung also Berlin, Frankfurt, Hamburg und München. Und last but not least werden Sie jede der zuletzt erstellten Abfragen gemäß dem Namen in der Spalte Filiale filtern. Anschließend ein Klick auf das Symbol Schließen & laden und es werden durch Power Query 4 neue Tabellenblätter mit den Listen/Tabellen der jeweiligen Abfrage erstellt. Sinnvollerweise werden Sie die Sheets nun mit den entsprechenden Städtenamen benennen, das leere Blatt Tabelle1 können Sie getrost löschen. – Spätestens jetzt sollten Sie Ihr Werk speichern.

Datei | Exportieren | PDF/XPS Dokument erstellen und vergeben Sie einen passenden Dateinamen, beispielsweise Geburtstagsliste Berlin. Ein Klick auf die Schaltfläche Optionen… und Sie passen an, was gedruckt werden soll und welche Informationen in jedem einzelnen Dokument enthalten sein sollen. Ein Klick auf Veröffentlichen und die PDF-Datei wird am vorgegebenen Ort gespeichert.

Vielleicht fragen Sie sich, warum ich als Dateinamen nur den Namen der Stadt und nicht zusätzlich das aktuelle Datum verwendet habe; der Vorteil dieser Vorgehensweise ist, dass im Normalfall beim Empfänger der PDF die alte Datei (auf Nachfrage) überschrieben wird und somit stets nur der aktuelle Stand der Dinge vorliegt.

… Und jetzt kommt der Ruf aus den einzelnen Niederlassungen, dass die Listen doch nicht so optimal sind wie es wünschenswert wäre. Die Sortierung jeder einzelnen Liste sollte so sein, dass die Geburtstage in Bezug auf das aktuelle Jahr chronologisch aufsteigend geordnet sind. Die ersten Einträge sollten also nahe dem aktuellen Datum sein. Natürlich brauchen Sie sich nicht noch einmal die ganze Arbeit zu machen, es reicht eine relativ kleine Änderung an der Abfrage Tabelle1. Öffnen Sie also diese Query beispielsweise durch einen Doppelklick auf den obersten Eintrag im rechten Seitenfenster und markieren Sie im Bereich Angewendete Schritte die Zeile Gefiltert Zeilen. Sortieren Sie nun die Spalte Geburtstag aktuelles Jahr aufsteigend. Im Dialog Schritte einfügen bestätigen Sie Ihre Entscheidung mit einem Klick auf Einfügen. Ein Klick im linken Seitenfenster auf die einzelnen Städte-Abfragen wird Ihnen zeigen, dass jetzt alles so ist wie gewünscht. 😎 – Und wenn Sie Ihr Ergebnis mit meinem vergleichen wollen, laden Sie sich meine Lösung einfach herunter.

▲ nach oben …

Dieser Beitrag wurde unter Allgemein, Verschiedenes, Datum & Zeit, File-Handling, Filtern & Sortieren, Power Query, PQ-Formeln (Sprache M) abgelegt und mit , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.