LuckyTemplates-Lösung zwischen Daten und Arbeitszeiten

Im heutigen Blog werde ich einen Ansatz für die Start-Stopp-Challenge von Access Analytic demonstrieren , bei dem mithilfe von LuckyTemplates die Gesamtarbeitszeit zwischen Daten berechnet wird. Das vollständige Video dieses Tutorials können Sie unten in diesem Blog ansehen .

Die Aufgabe besteht darin, die Gesamtstundenzahl eines Mitarbeiters für jeden Monat und jedes Datum zu berechnen. Ich habe Start- und Enddaten angegeben, die sich über Monate erstrecken können, und den Status berücksichtigt, um mögliche Tippfehler zu vermeiden. 

Inhaltsverzeichnis

• Datensatz für LuckyTemplates-Arbeitszeiten zwischen Daten
• Filtern der Zeilen
• Hinzufügen einer benutzerdefinierten Spalte
• LuckyTemplates-Arbeitszeiten zwischen Daten: Die Variablen
• LuckyTemplates-Arbeitszeiten zwischen Daten: Prognose
• LuckyTemplates-Arbeitszeiten zwischen Daten: Aggregierte Werte
• Abschluss

Datensatz für LuckyTemplates-Arbeitszeiten zwischen Daten

Gehen wir zum Power Query-Editor. 

Hier sind unsere Daten für diese Herausforderung. Wir haben eine Tabelle mit Spalten für den Mitarbeiternamen, das Startdatum, die Startzeit, das Enddatum, die Endzeit und den Status.

Filtern der Zeilen

Beginnen wir mit der Auswahl der Zeilen basierend auf ihrem Status. Der Abfrageeditor kann uns bei dieser Logik helfen. Klicken Sie auf den Dropdown-Pfeil neben Status. Wählen Sie „Textfilter“ und wählen Sie „Beginnt nicht mit“. 

Schreiben Sie in das angezeigte Fenster „Zeilen filtern “ „e“. Klicken Sie dann auf OK .

Es wird noch nichts passieren und das ist in Ordnung, denn wir schreiben ein kleines „e“ und das „Ausschließen“ in unseren Daten, das mit einem großen „E“ beginnt.

Um das zu beheben, verwenden wir den optionalen dritten Parameter von Text.StartsWith . Gehen Sie in der Bearbeitungsleiste zum Ende der Funktion Text.StartsWith. Fügen Sie ein Komma hinzu und geben Sie unseren Comparer.OrdinalIgnoreCase ein. Klicken Sie auf das Häkchen. Dadurch sollten die Ausschlusszeilen entfernt werden .

Natürlich könnte es sein, dass jemand fälschlicherweise „Ausschließen“ ohne ein „E“ am Anfang eingegeben hat. In diesem Fall können wir eine zusätzliche Logik erstellen, die der Situation entspricht. 

Wir können testen, ob das Wort kein „X“ enthält, indem wir unsere Funktion erweitern und Text.Contains verwenden . Fügen Sie dazu oder Text.Contains ([Status], „x“, Comparer.OrdinalIgnoreCase hinzu .

Beachten Sie, dass wir im Vergleich zum ersten Satz „e“ durch ein „x“ ersetzt haben. Wir ignorieren den Fall weiterhin und schließen unsere beiden Klauseln in Klammern ein. Klicken Sie auf das Häkchen und wir sollten eine Tabelle ohne die Zeilen mit dem Status „Ausschließen “ oder „Xclude “ erhalten .

Hinzufügen einer benutzerdefinierten Spalte

Kommen wir nun zum Hauptteil dieser Herausforderung. Wir fügen über die Benutzeroberfläche eine benutzerdefinierte Spalte hinzu und wechseln dann zum Editor. 

Beginnen Sie mit dem Minitabellensymbol in der oberen linken Ecke der Tabelle. Klicken Sie darauf und wählen Sie im Dropdown-Menü die Option „Benutzerdefinierte Spalte hinzufügen “ aus . 

An diesem Punkt geht es uns nur darum, die Daten bereitzustellen, die wir später benötigen. Und da wir mit mehreren Feldern arbeiten, erstellen wir einen Datensatz mithilfe der Datensatzinitialisierer, dargestellt durch eckige Klammern. 

Lassen Sie uns Variablen erstellen und die verfügbaren Spalten zuweisen. Beginnen Sie mit der Eingabe von SD , das wir dem Startdatum gleichsetzen. Wir können dies tun, indem wir in der Spalte rechts auf „Startdatum“ klicken.

Dasselbe machen wir für die Variablen ST, ED und ET, die jeweils der Startzeit, dem Stoppdatum und der Stoppzeit zugewiesen werden sollen . Schließen Sie den Datensatz dann mit einer schließenden Klammer ab. Klicken Sie auf OK.

Der Tabelle wird eine benutzerdefinierte Spalte mit den Datensätzen hinzugefügt. Klicken Sie auf den weißen Bereich neben „Aufzeichnen“ und ein Vorschaufenster wird geöffnet. Dadurch werden alle Werte aus dieser Zeile übernommen.

LuckyTemplates-Arbeitszeiten zwischen Daten: Die Variablen

Anschließend können wir unsere Variablen über das Fenster „Erweiterter Editor“ formatieren . So sollte das Fenster „Erweiterter Editor“ aussehen. 

Dann fügen wir unsere Variablen in separate neue Zeilen ein.

Jetzt können wir die Logik erweitern, die wir brauchen. Anstelle eines Datensatzes könnten wir hier also auch einen verschachtelten let-Ausdruck verwenden. Beachten Sie, dass beides funktioniert, da wir über mehrere Variablen oder Feldnamen verfügen, denen Werte zugewiesen sind.

Unser Startdatum wird derzeit als Textwert geschrieben und wir müssen ihn in ein geeignetes Datumsformat umwandeln. Umschließen Sie dazu die Funktion Date.FromText . 

Für die Startzeit , die ebenfalls als Zeichenfolge formatiert ist, müssen wir sie in eine Zahl umwandeln. Dann können wir es durch hundert dividieren und runden, um etwaige Dezimalstellen zu entfernen. 

Teilen Sie mithilfe der Funktion „Number.From“ unseren Startdatumswert durch 100 . Schließen Sie sie dann in die nächste Funktion „ Number.Round“ ein und fügen Sie am Ende „0“ hinzu, um ohne Dezimalstellen abzurunden. 

Für Enddatum und Endzeit kopieren wir einfach dieselben Funktionen, die wir jeweils für Startdatum und Startzeit hatten, und ersetzen sie durch die entsprechenden Variablen.

Sobald die Variablen richtig formatiert sind, erstellen Sie in unserem Datensatz ein weiteres Feld für zusätzliche Logik. Erstellen Sie eine neue Zeile, indem Sie die Eingabetaste drücken. 

Wir müssen eine Liste der Daten vom ersten Datum bis zum Enddatum erstellen. Wir nennen diese Daten LD und verwenden die Funktion List.Dates . 

Der erste Parameter dieser Funktion ist das Startdatum, das unser SD wäre . Dann möchte es eine Zählung als Zahl oder die Differenz zwischen dem Startdatum und dem Enddatum. Wir können das mithilfe der Number.From- Funktion gefolgt von ( ED – SD) + 1 ermitteln . Beachten Sie, dass das „+1“ in den nächsten Screenshots nicht angezeigt wird, es aber ein +1 geben sollte .

Der dritte Parameter wird als Schrittdauer bezeichnet und wir möchten eine Erhöhung um einen Tag. Wir können das mit Duration.From (1) erhalten. 

Wir haben eine Liste mit Daten vom allerersten Startdatum bis zum Enddatum erstellt. Als nächstes wollen wir eine entsprechende Zeitliste erstellen. 

Nennen wir dies LT für Listenzeiten. LT kann einen Tageszeitraum von einem einzigen Tag haben, was eine etwas andere Logik erfordert, als wenn es sich über mehrere Tage erstreckt. Dafür müssen wir also eine Voraussetzung schaffen. 

Wir möchten, dass die Bedingung lautet: „Wenn unser Startdatum mit unserem Enddatum übereinstimmt, dann Endzeit minus Startzeit.“ Die Ergebnisse sollten in einem Listenformat vorliegen, daher verwenden wir den durch { } dargestellten Listeninitialisierer . 

Wenn wir nun einen Datumsbereich haben, der sich über mehrere Tage erstreckt, ist das erste Datum 24 minus der Startzeit. Wir werden dies als else- Anweisung zu unserer Bedingung hinzufügen. Formatieren Sie dies erneut als Liste mit den geschweiften Klammern.

Zu diesem Zeitpunkt haben wir die anderen ganzen Tage noch nicht berücksichtigt, daher verwenden wir das kaufmännische Und-Symbol (&), um mehrere Listen anzuhängen. Erstellen Sie für jeden ganzen Tag eine Liste mit dem Wert 24, der die 24 Stunden an einem Tag darstellt. Wir verwenden dafür  List.Repeat .

Erstellen Sie mit der List.Repeat- Funktion eine Liste, die 24 enthält, und wiederholen Sie diese mehrmals, indem Sie die Anzahl der Tage in LD zählen . Um dies zu erreichen, verwenden Sie List.Count (LD)   und subtrahieren dann 2, da wir eine separate Liste für unser Startdatum haben und eine andere Liste für die Endzeit erstellen. 

Im Wesentlichen wird dadurch eine Liste mit nur 24 Stunden für jeden ganzen Tag erstellt. 

Für die Endzeit können wir es erneut als Liste mit dem kaufmännischen Und anhängen. Dann rufen wir mithilfe der Listeninitialisierer das ET auf. 

Bisher haben wir zwei große Listen – eine Liste mit Datumsangaben und eine Liste mit Uhrzeiten –, deren Länge einander entspricht. Aus diesen beiden Listen können wir eine einzelne Tabelle erstellen. 

Erstellen Sie in einer neuen Zeile eine weitere Variable für die Tabelle, die wir t nennen , und verwenden Sie die Funktion Table.FromColumns . Diese Funktion erfordert Listen als Liste und wir verwenden unser LD. 

Unser LD enthält einzelne Termine vom Startdatum bis zum Enddatum. Wir können diese Datumsliste in den Datumswert für das Monatsende umwandeln, indem wir LD übergeben und die Funktion Date.EndOfMonth aufrufen . Dann holen wir uns in der nächsten Zeile auch  unseren LT .

Drücken Sie die Eingabetaste, um eine weitere Zeile zu erstellen und unsere Tabelle als Typ table festzulegen . Unsere Tabelle wird zwei Spalten haben, wobei die erste Spalte eine Datumsspalte ist. Erstellen Sie also eine Datumsspalte und rufen Sie die Funktion Date.Type auf .

Die zweite Spalte ist unsere Stundenspalte und eine Ganzzahl. Daher erstellen wir eine Hrs- Spalte und rufen Int8.Type auf.

Wir haben die schließende Klammer zum Schließen unseres Datensatzes und eine schließende Klammer zum Schließen unserer Table.AddColumn- Funktion. Klicken Sie auf „Fertig“ und wir erhalten eine Liste der Datensätze in einer benutzerdefinierten Spalte. 

Klicken Sie erneut auf den weißen Bereich neben jedem Datensatz . Die Tabelle sollte sich im unteren Teil des Bildschirms öffnen. 

Beachten Sie, dass wir unsere t-Tabelle in der letzten Zeile haben. Versuchen wir, einen der Datensätze genauer zu untersuchen. Klicken Sie mit der rechten Maustaste auf das erste Ergebnis und wählen Sie „Als neue Abfrage hinzufügen“ . 

Wir erhalten die gleiche Zusammenfassung wie zuvor und wenn wir auf die Tabelle klicken , sollten wir unsere Datums- und Stundenwerte erhalten .

Uns interessiert aber vor allem die endgültige Ausgabetabelle. Um uns darauf zu konzentrieren, entfernen wir die Hilfeabfrage, indem wir mit der rechten Maustaste auf „ Benutzerdefiniert“ klicken und „Löschen“ wählen . Bestätigen Sie, indem Sie im Popup-Fenster auf Löschen klicken.

Nach dem Löschen können wir zu unserer ursprünglichen Abfrage zurückkehren und erneut auf das Leerzeichen neben „Datensatz“ klicken. Es werden die gleichen Ergebnisse wie zuvor angezeigt. Aber dieses Mal erweitern wir die Bearbeitungsleiste und rufen [t] neben unseren schließenden Klammern auf.

In der Spalte „Benutzerdefiniert“ können wir sehen, dass jeder Datensatz in „Tabelle“ geändert wird . 

LuckyTemplates-Arbeitszeiten zwischen Daten: Prognose

In der Tabelle, die wir betrachten, sind nur zwei Spalten von Interesse: der Personalname und unsere benutzerdefinierte Spalte. Wir können die Projektion verwenden, um nur diese beiden Spalten aus dieser Tabelle beizubehalten. 

Gehen Sie zur Bearbeitungsleiste und wählen Sie neben unserer schließenden Klammer die Felder aus, die wir behalten möchten, indem Sie sie in eckige Klammern setzen. Drücken Sie dann das Häkchen.

Wir sollten dieses Ergebnis erhalten.

Wir können die verschachtelte Tabelle erweitern , indem wir auf die Pfeilsymbole  neben „Benutzerdefiniert“ klicken . Deaktivieren Sie „Originalspaltennamen als Präfix verwenden“ und klicken Sie auf „OK“.

Unsere Tabelle sollte so aussehen.

LuckyTemplates-Arbeitszeiten zwischen Daten: Aggregierte Werte

Jetzt müssen Sie nur noch diese Werte aggregieren. Wählen Sie dazu die Spalte „Stabname“ aus und gehen Sie zu „Transformieren“. 

Wählen Sie im Popup-Fenster unter der Spalte „Werte“ die Option „ Stunden “ aus , da dies das ist, was wir aggregieren möchten. Wählen Sie dann unter Aggregate Value Function die Option Sum aus . Klicken Sie dann auf OK.

Unsere endgültige Ausgabe wird so aussehen. 

Erstellen einer Datumstabelle in LuckyTemplates Der schnellstmögliche
Zeitvergleich für nicht standardmäßige Datumstabellen in LuckyTemplates
DateTime-Wert: So entfernen Sie Sekunden

Abschluss

In diesem Blog haben Sie einen Ansatz für die Start Stop Challenge von Access Analytic gesehen. Sie haben gelernt, wie Sie die Gesamtzahl der Arbeitsstunden jedes Mitarbeiters in einem Monat berechnen und wie Sie mit möglichen Tippfehlern in Ihren Daten umgehen. 

Mit dieser Technik und LuckyTemplates können Sie die Arbeitszeiten zwischen zwei Terminen ganz einfach in Ihrer eigenen Organisation berechnen oder als Übung zur Vertiefung Ihrer LuckyTemplates-Kenntnisse und -Fähigkeiten nutzen.