Rozwiązanie LuckyTemplates między datami i godzinami pracy

W dzisiejszym blogu zademonstruję podejście do wyzwania Start Stop Challenge firmy Access Analytic , w którym przy użyciu usługi LuckyTemplates zostaną obliczone całkowite godziny pracy między datami. Możesz obejrzeć pełny film tego samouczka na dole tego bloga .

Zadanie polega na obliczeniu łącznej liczby godzin dla pracownika w każdym miesiącu i dniu. Przedstawiłem daty rozpoczęcia i zakończenia, które mogą przekraczać miesiące i uwzględniają status, zajmując się ewentualnymi literówkami. 

Spis treści

• Zestaw danych dla godzin pracy usługi LuckyTemplates między datami
• Filtrowanie wierszy
• Dodawanie niestandardowej kolumny
• Godziny pracy usługi LuckyTemplates między datami: zmienne
• Godziny pracy usługi LuckyTemplates między datami: prognoza
• Godziny pracy usługi LuckyTemplates między datami: wartości agregowane
• Wniosek

Zestaw danych dla godzin pracy usługi LuckyTemplates między datami

Przejdźmy do edytora Power Query. 

Oto nasze dane dotyczące tego wyzwania. Mamy tabelę z kolumnami dla nazwy personelu, daty rozpoczęcia, godziny rozpoczęcia, daty zakończenia, godziny zakończenia i statusu.

Filtrowanie wierszy

Zacznijmy od wybrania wierszy na podstawie ich stanu, a Edytor zapytań może nam w tym pomóc. Kliknij strzałkę listy rozwijanej obok Status. Wybierz Filtry tekstowe i wybierz Nie zaczyna się od. 

W oknie Filtruj wiersze , które się pojawi, wpisz „e”. Następnie kliknij OK .

Nic się jeszcze nie wydarzy i to jest w porządku, ponieważ piszemy małą literę „e” i „Wyklucz” w naszych danych, które zaczynają się od dużej litery „E”.

Aby to naprawić, użyjemy opcjonalnego trzeciego parametru Text.StartsWith . Na pasku formuły przejdź na koniec funkcji Text.StartsWith. Dodaj przecinek i wpiszmy nasz Comparer.OrdinalIgnoreCase. Kliknij znacznik wyboru, który powinien usunąć wiersze Wyklucz .

Oczywiście możemy mieć kogoś, kto błędnie wpisał „Wyklucz” bez początkowego „E”. W takim przypadku możemy stworzyć dodatkową logikę pasującą do sytuacji. 

Możemy sprawdzić, czy słowo nie zawiera „X”, rozszerzając naszą funkcję i używając Text.Contains . Aby to zrobić, dodaj lub Text.Contains ([Status], „x”, Comparer.OrdinalIgnoreCase .

Zauważ, że w porównaniu z pierwszą klauzulą, zastąpiliśmy „e” przez „x”. Nadal ignorujemy przypadek i zawijamy zestaw nawiasów wokół obu naszych klauzul. Kliknij znacznik wyboru i powinniśmy otrzymać tabelę bez wierszy zawierających status Exclude lub Xclude .

Dodawanie niestandardowej kolumny

Przejdźmy teraz do głównej części tego wyzwania. Dodamy niestandardową kolumnę za pomocą interfejsu użytkownika, a następnie przełączymy się do edytora. 

Zacznij od ikony ministolika w lewym górnym rogu stołu. Kliknij na nią iz listy rozwijanej wybierz Dodaj kolumnę niestandardową . 

W tym momencie jesteśmy zainteresowani tylko wprowadzeniem danych, które będą nam potrzebne później. A ponieważ będziemy pracować z wieloma polami, utwórzmy rekord, używając inicjatorów rekordów, reprezentowanych przez nawiasy kwadratowe. 

Stwórzmy zmienne i przypiszmy dostępne kolumny. Zacznij od wpisania SD , które zrównamy z datą rozpoczęcia. Możemy to zrobić, klikając Data rozpoczęcia w kolumnie po prawej stronie.

Zrobimy to samo dla zmiennych ST, ED i ET , które zostaną przypisane odpowiednio do czasu rozpoczęcia, daty zakończenia i czasu zakończenia . Następnie zamknij rekord za pomocą nawiasu zamykającego. Kliknij OK.

Do tabeli zostanie dodana niestandardowa kolumna z rekordami. Kliknij białe miejsce obok Nagraj , a otworzy się okienko podglądu. Spowoduje to wprowadzenie wszystkich wartości z tego wiersza.

Godziny pracy usługi LuckyTemplates między datami: zmienne

Następnie możemy sformatować nasze zmienne w oknie Edytora zaawansowanego . Tak powinno wyglądać okno Edytora zaawansowanego. 

Następnie umieśćmy nasze zmienne w osobnych nowych wierszach.

Teraz możemy rozszerzyć logikę, której potrzebujemy. Tak więc, zamiast rekordu, moglibyśmy tutaj również użyć zagnieżdżonego wyrażenia let. Zauważ, że każda z nich będzie działać, ponieważ mamy wiele zmiennych lub nazw pól z przypisanymi do nich wartościami.

Nasza data początkowa jest obecnie zapisywana jako wartość tekstowa i musimy ją przekształcić w odpowiedni format daty. Aby to zrobić, otocz ją  funkcją Date.FromText .

W przypadku czasu rozpoczęcia , który jest również sformatowany jako ciąg, musimy przekonwertować go na liczbę. Następnie możemy podzielić go przez sto i zaokrąglić, aby usunąć miejsca po przecinku. 

Korzystając z funkcji Number.From , podziel naszą wartość Data rozpoczęcia przez 100 . Następnie zawiń je w następną funkcję, Number.Round i dodaj 0 na końcu, aby zaokrąglić bez miejsc dziesiętnych. 

Dla opcji Data zakończenia i Czas zakończenia po prostu skopiujemy te same funkcje, które mieliśmy odpowiednio w Datach rozpoczęcia i Czasie rozpoczęcia , i zastąpimy je odpowiednimi zmiennymi.

Po prawidłowym sformatowaniu zmiennych utwórz kolejne pole w naszym rekordzie dla dodatkowej logiki. Utwórz nową linię, naciskając Enter. 

Musimy skonstruować listę dat od pierwszej daty aż do daty końcowej. Nazwiemy te daty LD i użyjemy funkcji List.Dates . 

Pierwszym parametrem tej funkcji jest data początkowa , która byłaby naszym SD. Następnie chce liczyć jako liczbę lub różnicę między datą początkową a datą końcową. Możemy to uzyskać za pomocą funkcji Number.From , po której następuje ( ED – SD) + 1 . Zwróć uwagę, że „+1” nie jest pokazany w kolejnych zestawach zrzutów ekranu, ale powinien być +1 .

Trzeci parametr nazywa się krokiem jako czasem trwania i chcemy przyrostu o jeden dzień. Możemy to uzyskać za pomocą Duration.From (1). 

Stworzyliśmy listę z datami od pierwszej daty rozpoczęcia do daty zakończenia. W następnej kolejności chcemy utworzyć listę czasów, które będą z tym zgodne. 

Nazwijmy to LT dla listy razy. LT może mieć okres jednego dnia, co wymaga nieco innej logiki niż w przypadku, gdy obejmuje wiele dni. Więc to jest coś, dla czego musimy stworzyć warunek. 

Chcemy, aby warunek brzmiał „jeśli nasza data początkowa jest taka sama jak data końcowa, to czas zakończenia minus czas rozpoczęcia”. Wyniki powinny być w formacie listy, więc używamy inicjatora listy reprezentowanego przez { } . 

Teraz, jeśli mamy zakres dat obejmujący wiele dni, pierwszą datą będzie 24 minus czas rozpoczęcia. Dodamy to do naszego warunku jako instrukcję else . Ponownie sformatuj to jako listę, używając nawiasów klamrowych.

W tym momencie nie uwzględniliśmy pozostałych pełnych dni, więc użyjemy symbolu ampersand (&) do dołączenia wielu list. Dla każdego całego dnia utwórz listę z wartością 24 reprezentującą 24 godziny, które mamy w ciągu dnia. W tym celu użyjemy List.Repeat . 

Za pomocą funkcji List.Repeat utwórz listę zawierającą 24 i powtórz ją kilka razy, licząc liczbę dni w LD . Aby to osiągnąć, użyj List.Count (LD),   a następnie odejmij 2, ponieważ mamy oddzielną listę dla naszej daty początkowej i utworzymy inną listę dla godziny zakończenia. 

Zasadniczo spowoduje to utworzenie listy zawierającej tylko 24 godziny na każdy cały dzień. 

Na czas zakończenia możemy ponownie dołączyć go jako listę za pomocą znaku ampersand. Następnie, używając inicjatorów listy, nazwijmy ET. 

Jak dotąd mamy dwie duże listy — listę dat i listę godzin — których długości są sobie równe. Z tych dwóch list możemy zbudować pojedynczą tabelę. 

W nowym wierszu utwórz kolejną zmienną dla tabeli, którą nazwiemy t i użyj funkcji Table.FromColumns . Ta funkcja wymaga list jako listy , a my użyjemy naszego LD. 

Nasz LD zawiera poszczególne daty od daty rozpoczęcia do daty zakończenia. Możemy przekształcić tę listę dat na wartość daty końca miesiąca, przekazując LD i wywołując funkcję Date.EndOfMonth . Następnie w następnym wierszu weźmy również  nasze LT .

Naciśnij Enter , aby utworzyć kolejną linię i ustaw naszą tabelę jako typ table . Nasza tabela będzie miała dwie kolumny, z których pierwsza będzie kolumną daty. Utwórz więc kolumnę Date i wywołaj funkcję Date.Type .

Druga kolumna będzie naszą kolumną Godziny i będzie to liczba całkowita. W ten sposób utworzymy kolumnę godzin i wywołamy Int8.Type.

Mamy nawias zamykający do zamknięcia naszego rekordu i nawias zamykający do zamknięcia naszej funkcji Table.AddColumn . Kliknij Gotowe , a otrzymamy listę rekordów w kolumnie Niestandardowa. 

Ponownie kliknij białe miejsce obok każdego rekordu , a tabela powinna otworzyć się w dolnej części ekranu. 

Zauważ, że mamy naszą tabelę t w ostatnim rzędzie. Spróbujmy zagłębić się w jeden z rekordów. Kliknij prawym przyciskiem myszy pierwszy wynik i Dodaj jako nowe zapytanie . 

Otrzymamy takie samo podsumowanie jak wcześniej, a jeśli klikniemy na Table , powinniśmy otrzymać nasze wartości Date i Hrs .

Ale nas przede wszystkim interesuje ostateczna tabela wyników. Aby się na tym skoncentrować, usuńmy zapytanie pomocy, klikając prawym przyciskiem myszy Niestandardowe i wybierając Usuń . Potwierdź, klikając Usuń w wyskakującym oknie.

Po usunięciu możemy wrócić do naszego początkowego zapytania i ponownie kliknąć białe miejsce obok Rekordu. Pokaże takie same wyniki jak wcześniej. Ale tym razem rozwińmy pasek formuły i wywołajmy [t] obok naszych nawiasów zamykających.

W kolumnie Niestandardowe widzimy, że każdy rekord jest zmieniany na Tabela . 

Godziny pracy usługi LuckyTemplates między datami: prognoza

Z tabeli, na którą patrzymy, są tylko dwie kolumny, które nas interesują — nazwisko pracownika i nasza niestandardowa kolumna. Możemy użyć projekcji, aby zachować tylko te dwie kolumny z tej tabeli. 

Przejdź do paska formuły i obok naszego nawiasu zamykającego zaznacz pola, które chcemy zachować, umieszczając je w nawiasach kwadratowych. Następnie naciśnij znacznik wyboru.

Taki wynik powinniśmy otrzymać.

Możemy rozwinąć zagnieżdżoną tabelę , klikając ikony strzałek  obok Custom . Usuń zaznaczenie opcji Użyj oryginalnej nazwy kolumny jako prefiksu i kliknij OK.

Nasza tabela powinna wyglądać tak.

Godziny pracy usługi LuckyTemplates między datami: wartości agregowane

Pozostało już tylko zagregować te wartości. W tym celu wybierz kolumnę Staff Name i przejdź do Transform. 

W wyskakującym oknie wybierz Godziny w kolumnie Wartości, ponieważ to właśnie chcemy zagregować. Następnie wybierz opcję Suma w obszarze Funkcja wartości agregowanej . Następnie kliknij OK.

Nasz ostateczny wynik będzie wyglądał tak. 

Tworzenie tabeli dat w usłudze LuckyTemplates Najszybszy możliwy sposób
Porównanie czasu dla niestandardowych tabel dat w usłudze LuckyTemplates
Wartość daty i godziny: jak usunąć sekundy

Wniosek

Na tym blogu poznałeś jedno podejście do wyzwania Start Stop w programie Access Analytic. Nauczyłeś się, jak obliczyć łączną liczbę godzin przepracowanych przez każdego pracownika w miesiącu i jak radzić sobie z ewentualnymi literówkami w danych. 

Dzięki tej technice i usłudze LuckyTemplates godziny pracy między datami można łatwo obliczyć we własnej organizacji lub w ramach praktyki pogłębiania wiedzy i umiejętności usługi LuckyTemplates.