Ewiger Feiertagskalender – POTW #12 (Power Query Solution)

In diesem Blogbeitrag werden wir meine Lösung für untersuchen . Für diese Herausforderung haben wir LuckyTemplates‘ erste Herausforderung „Problem der Woche“ noch einmal aufgegriffen, bei der wir einen ewigen Feiertagskalender aus einer Mischung spezifischer und relativer Daten erstellt haben. Aber dieses Mal werden wir nur Power Query verwenden . Das vollständige Video dieses Tutorials können Sie unten in diesem Blog ansehen.

Wenn Sie bei der eigentlichen Problemaufschlüsselung nicht weiterkommen, können Sie die noch einmal durchgehen , um zu sehen, ob einer der Ansätze Elemente enthält, die Ihnen bei dieser Herausforderung helfen können.

Beim Lesen der Einsendungen habe ich eine Mischung aus Leuten gesehen, die die Benutzeroberfläche nutzten und benutzerdefinierte Funktionen schrieben , um einen ewigen Kalender zu erstellen. Es hat Spaß gemacht, jeden Eintrag zu analysieren, und wenn Sie sich inspirieren lassen möchten, empfehle ich Ihnen, dasselbe zu tun.

Als ich dieses Szenario selbst durchgearbeitet habe, habe ich letztendlich mehrere Lösungen entwickelt. Das, für das ich mich entschieden habe, veranschaulicht am besten, wie man ein Problem nicht nur in kleinere Teile zerlegen , sondern diese auch sichtbar machen kann . Dies ist sehr hilfreich, wenn ein Fehler oder unerwartete Ergebnisse auftreten.

Inhaltsverzeichnis

• Der Ausgangspunkt für die Erstellung eines ewigen Kalenders
• Abrufen der Jahre aus der Datumstabelle
• Abrufen der Zahl aus der Tagesspalte
• Aufrufen der benutzerdefinierten Funktion für den ewigen Feiertagskalender
• Erstellen einer Liste mit Terminen
• Anwenden bedingter Logik
• Überprüfung des M-Codes für den ewigen Feiertagskalender
• Abschluss

Der Ausgangspunkt für die Erstellung eines ewigen Kalenders

Dieser rohe Feiertagstisch ist unser Ausgangspunkt. Um ein Datum zusammenzusetzen, benötigen wir für bestimmte Daten ein Jahr, einen Monat und einen Tag. Es fehlt nur noch die Jahreszahl.

Aber für relative Daten müssen wir etwas mehr Arbeit leisten. Zunächst müssen wir diese Tagesspalte sowohl in die Zahl als auch in den Wochentag aufteilen. Sehen wir uns diese Schritte noch einmal an.

Abrufen der Jahre aus der Datumstabelle

Um diese Lösung dynamisch zu gestalten, werde ich die Jahre aus der Datumstabelle abrufen . In der Bearbeitungsleiste sehen Sie, dass ich eine Projektion verwendet habe, um eine einspaltige Tabelle aus meiner Datumstabelle zu erhalten. Dies wird natürlich Duplikate enthalten. Um diese zu entfernen, habe ich Table.Distinct hinzugefügt .

Wenn ich auf den weißen Bereich neben einer dieser Tabellen klicke, sehen Sie auch eine Liste mit den einzelnen Jahren, die in meiner Datumstabellenabfrage vorhanden sind.

Uns wurde ein Monatsname gegeben , wir benötigen jedoch tatsächlich eine Monatsnummer . Wenn wir nun ein Jahr mit einem Monatsnamen verketten, können wir die Funktion „Date.From“ verwenden , um den ersten Tag dieses Monats als Datumswert zurückzugeben.

Durch das Hinzufügen von Date.Month bleibt uns nur noch die Monatszahl für dieses bestimmte Datum. Für Januar wird eine 1 zurückgegeben. Für Februar wird eine 2 zurückgegeben und so weiter.

Abrufen der Zahl aus der Tagesspalte

Um die Zahl aus der Tagesspalte zu erhalten, haben wir uns entschieden, die Spalte nicht zu teilen und stattdessen den Wert zu extrahieren. Wie Sie in der Bearbeitungsleiste sehen können, haben wir Text.BeforeDelimiter verwendet und dieses Leerzeichen verwendet, um den rechten und linken Teil dieser Textzeichenfolge zu identifizieren.

Wenn kein Platz vorhanden ist, möchten wir alles zurückgeben, was in der Tagesspalte übrig bleibt. Für den ersten Datensatz wird also eine 1 zurückgegeben. Für den zweiten Datensatz, der dieses Leerzeichen enthält, wird der erste Teil der Textzeichenfolge zurückgegeben, das Wort Third .

Wir werden einen ähnlichen Ansatz verwenden, um den Datumsnamen zu erhalten, verwenden jedoch die Funktion List.PositionOf . Dies gibt den nullbasierten Offset des Werts in der als erstes Argument übergebenen Liste zurück und gibt -1 zurück, wenn der Wert nicht in dieser Liste erscheint.

Für den ersten Datensatz wird der Wert 1 übergeben, der in unserer Liste nicht vorhanden ist. Aus diesem Grund wird der Wert -1 zurückgegeben. Für den zweiten Datensatz wird der Wert Monday zurückgegeben .

Es übergibt das an unsere Funktion und gibt eine 1 zurück, da es das zweite Element in unserer Liste ist. Als Nächstes ersetzen wir alle -1-Werte durch einen Nullwert .

Zu diesem Zeitpunkt haben wir alle Bausteine ​​erstellt, die wir zum Aufrufen der benutzerdefinierten Funktion benötigen.

Aufrufen der benutzerdefinierten Funktion für den ewigen Feiertagskalender

In der Formelleiste können wir sehen, dass die Funktion vier Argumente akzeptiert: ein Jahr , einen Monat , eine Zahl und einen Wochentag .

Aber wie wird dieser Datumswert bestimmt? Wir verwenden List.PositionOf erneut, um einen numerischen Wert für die Zahl zu erhalten. Wir haben eine Liste mit dem ersten, zweiten, dritten und vierten. Wenn wir die Zahl an List.PositionOf übergeben , wird der auf Null basierende Offset zurückgegeben.

Auch hier gilt: Wenn ein Wert nicht vorhanden ist, wird -1 zurückgegeben. Für den ersten Datensatz übergeben wir also den Wert 1. 1 ist in dieser Liste nicht vorhanden, weshalb sie den Wert -1 zurückgibt. Für den zweiten Datensatz übergeben wir den Wert Third, das dritte Element in unserer Liste. Dies gibt den Wert 2 zurück.

Erstellen einer Liste mit Terminen

Lassen Sie uns nun eine Liste mit Daten erstellen. Wir bestimmen das Startdatum für diesen Monat, bestimmen die Anzahl der Tage in diesem Monat und verwenden List.Dates , um eine Liste mit allen Tagen in diesem Monat zu erstellen.

Als nächstes verwenden wir List.Select, um nur die Daten beizubehalten, die an einem bestimmten Wochentag liegen.

Wenn wir auf die Seite neben einer dieser Listen klicken, sehen wir, dass sie nur diese vier Elemente enthält.

Anwenden bedingter Logik

Abschließend wenden wir bedingte Logik an, um ein Datum zu erstellen oder abzurufen. Wenn der Wochentag null ist , was bedeutet, dass wir ein bestimmtes Datum haben, können wir das intrinsische #date verwenden , um einen Datumswert zu erstellen.

Wenn die Zahl dem letzten Wert entspricht, können wir die Funktion List.Reverse verwenden , um die Reihenfolge in der Liste umzukehren und den ersten Wert zu extrahieren.

Wenn wir diese aufsteigende Reihenfolge in absteigend ändern möchten, können wir jederzeit das letzte Datum aus diesem Datumsbereich extrahieren. Es spielt keine Rolle, ob die Liste vier oder fünf Elemente enthält. Durch Umkehren der Bestellung und Herausnehmen des ersten Artikels haben wir immer das letzte Datum.

Für alle anderen Fälle verwenden wir List.Skip .

Wir können den Wert basierend auf dieser Position in der Liste extrahieren. Für den Erntedankfesttag ist die Zahl viert, und viert gibt eine 3 zurück. Dadurch werden die ersten drei Werte in der Liste übersprungen und immer das vierte Element zurückgegeben.

Fassen wir alles zusammen und gehen wir zur Lösungsabfrage. Für den Quellschritt rufen wir die RAW-Feiertagstabelle auf. Als Nächstes fügen wir eine Spalte hinzu, die eine Liste mit Datumsangaben zurückgibt.

Überprüfung des M-Codes für den ewigen Feiertagskalender

Lassen Sie uns den M-Code für die Spalte überprüfen. Für jede Zeile in der Tabelle haben wir eine Tabelle verschachtelt, die die unterschiedlichen Jahre aus der Datumstabelle enthält. Dann haben wir die Werte in dieser Jahresspalte transformiert, indem wir die benutzerdefinierte Funktion aufgerufen haben.

Um die benutzerdefinierte Funktion aufrufen zu können, müssen wir natürlich zunächst einige Einstellungen vornehmen. Wir müssen die Monatszahl , den Zahlenwert und natürlich den Wochentag ermitteln . Dann haben wir aus dieser verschachtelten Tabelle die Liste mit Datumsangaben geführt.

Wenn wir im Leerraum zur Seite klicken, können wir unten eine Vorschau dieser Liste sehen.

Der nächste Schritt besteht darin, diese Liste zu erweitern und CelebratedOnDate hinzuzufügen .

Dies ist die Logik, die wir bei dieser Berechnung verwendet haben: Wenn das Datum auf einen Samstag fällt, subtrahieren wir einen Tag, sodass wir auf einem Freitag landen. Wenn es ein Sonntag ist, fügen wir einen Tag hinzu, um auf einem Montag zu landen. Wenn es kein Samstag oder Sonntag ist, dann wollen wir eine Null.

Der letzte Schritt besteht schließlich darin, die Typen zu ändern.

Abschluss

Nur eine kurze Erinnerung daran, dass eine fortlaufende Serie ist, die jeden 1. und 3. Mittwoch im LuckyTemplates-Forum stattfindet. Das in der ersten Woche veröffentlichte Problem erfordert eine DAX-basierte Lösung, während das Problem der dritten Woche eine auf Power Query basierende Lösung erfordert.

Wenn Ihnen der in diesem POTW behandelte Inhalt gefallen hat, abonnieren Sie bitte den TV-Kanal LuckyTemplates, um mehr zu erfahren. Wir veröffentlichen ständig eine große Menge an Inhalten von mir selbst und einer Reihe von Inhaltserstellern, die sich alle dafür einsetzen, die Art und Weise zu verbessern, wie Sie LuckyTemplates und die Power Platform nutzen.

Melissa