Konvertieren eines YYWWD-Codes in einen Datumswert in LuckyTemplates

In diesem Blog besprechen wir, wie man einen YYWWD-Code in einen Datumswert umwandelt . Dieses Tutorial basiert auf einer Antwort auf eine Anfrage eines Mitglieds des  . Sie können sich das vollständige Video dieses Tutorials unten in diesem Blog ansehen.

Das Mitglied fragte nach einer Möglichkeit, sein Feld „Bestätigtes Lieferdatum“ , das im Format „Jahr“, „Woche“ und „Wochentag“ vorliegt, in ein Datumsfeld umzuwandeln.

In seinem Beispiel erklärt das Mitglied, dass der Code 20145 in den 03.04.2020 umgewandelt werden soll. Daraus können wir schließen, dass die ersten beiden Ziffern dem Jahr entsprechen, die nächsten beiden Ziffern der Wochennummer und die letzte Ziffer dem Wochentag.

Nachdem wir nun bereits eine Vorstellung davon haben, wie wir die Abfrage angehen können, können wir beginnen.

Inhaltsverzeichnis

• Den Code auseinanderbrechen, um einen korrekten Datumswert zu erhalten
• Jahresspalte
• Wochenspalte
• Tagesspalte
• 3-Wege-Suche, um einen Datumswert zu erhalten
• Identifizieren der Paare
• Den richtigen Datumswert ermitteln
• Von der Tagesspalte subtrahieren
• Abschluss

Den Code auseinanderbrechen, um einen korrekten Datumswert zu erhalten

Um uns die Arbeit zu erleichtern, laden wir unsere Daten in den Power Query-Editor .

Das erste, was mir beim Laden der Daten in Power Query auffiel, war, dass sofort ein Änderungstypschritt ausgeführt wurde, wie unten gezeigt.

Da wir dies nicht benötigen, können wir diesen Schritt entfernen, indem wir auf die X-Schaltfläche daneben klicken.

Beginnen wir nun damit, den Code in separate Spalten für Jahr, Woche und Wochentag aufzuteilen.

Jahresspalte

Wie ich bereits erwähnt habe, entsprechen die ersten beiden Ziffern des Codes dem Jahr des Datumswerts.

Um eine separate Spalte für das Jahr hinzuzufügen, müssen wir sicherstellen, dass die Spalte „Bestätigtes Lieferdatum“ ausgewählt ist. Gehen Sie dazu zur Registerkarte „Spalte hinzufügen“ , klicken Sie auf „Extrahieren“ und wählen Sie dann „Erste Zeichen“ aus .

Da wir nur die ersten beiden Ziffern für die Jahresspalte benötigen, geben Sie „2“ in die Registerkarte „Anzahl“ ein und klicken Sie auf „OK“.

Jetzt ändern wir unsere neue Spalte, indem wir die Formelleiste bearbeiten. Um den Spaltennamen zu ändern, ändern Sie die ersten Zeichen in der Formelleiste in „Jahr“. Um es in den richtigen Jahreswert umzuwandeln, addieren wir 2000 zu jedem unserer Werte.

Beachten Sie, dass wir nach dem Ändern unserer Formel eine Fehlermeldung aufgrund einer Typinkongruenz erhalten.

Die Fehlermeldung besagt, dass wir den Operator + nicht auf die Zahlen- und Texttypwerte anwenden können.

Um dies zu beheben, müssen wir unsere Formel so bearbeiten, dass der Textwert in eine Zahl umgewandelt wird. Dazu verwenden wir die Funktion Number.from und schließen die nachfolgenden Teile in Klammern ein. Ändern Sie dann den Rückgabetyp von Text in Zahl.

Wochenspalte

Extrahieren wir nun die Wochennummer aus dem Code.

Stellen Sie erneut sicher, dass die Spalte „Bestätigtes Lieferdatum“ ausgewählt ist, gehen Sie zur Registerkarte „Spalte hinzufügen“, klicken Sie auf „Extrahieren“ und wählen Sie dann „Bereich“ aus.

Um die dritte und vierte Ziffer unseres Codes zu erhalten, müssen wir mit dem Zählen bei 0 beginnen, da Power Query auf Null basiert. Das bedeutet, dass unser Bereich bei 2 beginnen sollte, um die dritte Ziffer unseres Codes zu erhalten.

Und da wir für die Wochennummer nur 2 Ziffern des Codes benötigen, geben wir 2 in das Feld „Anzahl der Zeichen“ ein.

Auch hier gilt die gleiche Logik. Um die Spaltenbeschriftung in ihren richtigen Namen umzuwandeln, ändern Sie den Textbereich in der Bearbeitungsleiste in „Woche“.

Fügen Sie außerdem die Funktion Number.from in unsere Formel ein, schließen Sie die nachfolgenden Teile in Klammern ein und ändern Sie den Rückgabetyp in eine Zahl.

Tagesspalte

Für die Tagesspalte benötigen wir nur die letzte Ziffer des Codes. Um es zu extrahieren, wählen Sie die Spalte „Bestätigtes Lieferdatum“ aus, gehen Sie zur Registerkarte „Spalte hinzufügen“, wählen Sie „Extrahieren“ und klicken Sie dann auf „ Letzte Zeichen“ .

Geben Sie 1 ein, da vom Code nur noch eine Ziffer übrig ist, die wir extrahieren müssen.

Ändern Sie wie zuvor in der Formelleiste „Letzte Zeichen“ in „Tag“, damit unsere neue Spalte ihren richtigen Namen hat.

Fügen Sie erneut die Funktion „Number.from“ hinzu, schließen Sie die folgenden Teile der Formel in Klammern ein und ändern Sie den Rückgabetyp in „Zahl“.

3-Wege-Suche, um einen Datumswert zu erhalten

Da wir nun über alle Komponenten verfügen, um ein einzelnes Datum aus unserer Kalendertabelle abzurufen, müssen wir nur noch eine 3-Wege-Suche durchführen .

Gehen Sie dazu zur Registerkarte „Startseite“ und wählen Sie „Abfragen zusammenführen“.

Da wir mit unserer Datumstabelle zusammenführen, klicken Sie auf die Dropdown-Registerkarte und wählen Sie Datumsangaben aus.

Identifizieren der Paare

Jetzt werden wir die Paare identifizieren, die zusammengehören.

Wählen Sie in der oberen Tabelle die Jahresspalte aus und koppeln Sie sie dann mit der Jahresspalte der Tabelle unten.

Um ein anderes Paar auszuwählen, drücken Sie die Strg-Taste auf Ihrer Tastatur, wählen Sie die Wochenspalte aus der oberen Tabelle aus und koppeln Sie sie mit der Wochennummernspalte der Tabelle unten.

Wählen Sie abschließend, während Sie weiterhin die Strg-Taste gedrückt halten, die Tagesspalte aus der oberen Tabelle aus und koppeln Sie sie mit der DayInWeek-Spalte der Tabelle unten.

Um sicherzustellen, dass die Spalten korrekt gepaart sind, prüfen Sie, ob die Indexnummern miteinander übereinstimmen.

Wie unten gezeigt, sollten die Spalten „Jahr“ einen Index 1 haben, die Spalte „Wochen“ sollte einen Index 2 haben und die Spalte „Tage“ sollte einen Index 3 haben.

Den richtigen Datumswert ermitteln

Da wir nur die Datumsspalte benötigen, deaktivieren Sie alle anderen Spalten und lassen Sie die Datumsspalte wie unten gezeigt aktiviert.

Wie Sie sehen können, ist der erste Wert in unserer Spalte „Neue Daten“ der 04.04.2020. Bedenken Sie jedoch, dass im Forum der Code 20145 in 3-4-2020 umgewandelt werden sollte.

Denn wenn wir unsere Datumstabelle untersuchen, können wir sehen, dass in der Spalte „DayInWeek“ die Zählung bei 0 bis 6 beginnt und nicht bei 1 bis 7.

Von der Tagesspalte subtrahieren

Um dies zu beheben, subtrahieren wir 1 von allen Werten in unserer Tagesspalte.

Klicken Sie im Abschnitt „Angewandte Schritte“ auf den Schritt, dem wir die Tagesspalte hinzugefügt haben. Dies war der Schritt „Letzte Zeichen einfügen“ .

Subtrahieren Sie in der Formelleiste direkt nach den Klammern 1.

Danach können wir sehen, dass wir in unserer Datumsspalte bereits die richtigen Werte haben.

Schließlich müssen wir als letztes die Spalten bereinigen und entfernen, die wir nicht mehr benötigen.

Wählen Sie dazu „Spalten auswählen“, deaktivieren Sie alles andere und lassen Sie die Spalte „Bestätigtes Lieferdatum“ und die Spalte „Datum“ aktiviert.

Und wir sind fertig! Das Ergebnis unserer Arbeit sollte so aussehen.

Zeigen Sie Ergebnisse bis zum aktuellen Datum oder einem bestimmten Datum in LuckyTemplates an. Verhindern Sie, dass
Jahresergebnisse vorwärts projiziert werden, indem Sie
Daten nach benutzerdefinierten Geschäftsjahren und Quartalen mithilfe berechneter Spalten in LuckyTemplates filtern

Abschluss

In diesem Blog haben wir einen Code im Format Jahr, Woche und Wochentag in einen geeigneten Datumswert konvertiert. Wir haben den Code zerlegt und daraus drei Spalten erstellt. Mithilfe einer 3-Wege-Suche haben wir diese Spalten zusammengeführt, um einen einzigen Datumswert zu erhalten.

Die Technik, die wir zur Lösung des Problems im Support-Forum verwendet haben, kann in anderen Szenarien verwendet werden. Stellen Sie also sicher, dass Sie sie gut verstehen.

Alles Gute,

Melissa