Soluție LuckyTemplates Between Dates Working Hours

În blogul de astăzi, voi demonstra o abordare a Start Stop Challenge a Access Analytic , în care, folosind LuckyTemplates, vor fi calculate orele totale de lucru între date. Puteți viziona videoclipul complet al acestui tutorial în partea de jos a acestui blog .

Sarcina este de a calcula numărul total de ore pentru un membru al personalului în fiecare lună și dată. Am prezentat date de începere și de sfârșit care pot trece peste luni și pot lua în considerare starea pentru a face față posibilelor greșeli de scriere. 

Cuprins

• Set de date pentru orele de lucru între date LuckyTemplates
• Filtrarea rândurilor
• Adăugarea unei coloane personalizate
• LuckyTemplates Orele de lucru între date: variabilele
• LuckyTemplates Orar de lucru între date: proiecție
• LuckyTemplates Orele de lucru între date: valori agregate
• Concluzie

Set de date pentru orele de lucru între date LuckyTemplates

Să mergem la editorul Power Query. 

Iată datele noastre pentru această provocare. Avem un tabel cu coloane pentru Numele personalului, Data de începere, Ora de începere, Data de oprire, Ora de oprire și Stare.

Filtrarea rândurilor

Să începem prin a selecta rândurile în funcție de starea lor, iar Editorul de interogări ne poate ajuta cu această logică. Faceți clic pe săgeata derulantă de lângă Stare. Selectați Filtre de text și alegeți Nu începe cu. 

În fereastra Filter Rows care va apărea, scrieți „e”. Apoi faceți clic pe OK .

Încă nu se va întâmpla nimic și este în regulă, deoarece scriem un „e” minuscul și „Exclude” în datele noastre care începe cu un „E” majuscul.

Pentru a remedia acest lucru, vom folosi al treilea parametru opțional al Text.StartsWith . În bara de formule, mergeți la sfârșitul funcției Text.StartsWith. Adăugați o virgulă și să introducem Comparer.OrdinalIgnoreCase. Faceți clic pe bifa și aceasta ar trebui să elimine rândurile Excludeți .

Desigur, am putea avea pe cineva care a tastat în mod eronat „Exclude” fără un „E” inițial. În acest caz, putem crea o logică suplimentară pentru a merge împreună cu situația. 

Putem testa dacă cuvântul nu include un „X” extinzându-ne funcția și utilizând Text.Contains . Pentru a face asta, adăugați sau Text.Contains ([Stare], „x”, Comparer.OrdinalIgnoreCase .

Observați că, în comparație cu prima clauză, am înlocuit „e” cu un „x”. Continuăm să ignorăm cazul și includem un set de paranteze în jurul ambelor clauze. Faceți clic pe bifa și ar trebui să obținem un tabel fără rândurile care conțin starea Exclude sau Xclude .

Adăugarea unei coloane personalizate

Să trecem acum la partea principală a acestei provocări. Vom adăuga o coloană personalizată folosind interfața de utilizare și apoi vom trece la editor. 

Începeți cu pictograma mini-tabel din colțul din stânga sus al tabelului. Faceți clic pe el și, în meniul drop-down, selectați Adăugați o coloană personalizată . 

În acest moment, suntem interesați doar să aducem datele de care vom avea nevoie mai târziu. Și pentru că vom lucra cu mai multe câmpuri, să creăm o înregistrare folosind inițializatoarele de înregistrare, reprezentate cu paranteze drepte. 

Să creăm variabile și să atribuim coloanele disponibile. Începeți prin a tasta SD , pe care îl vom echivala cu Data de începere. Putem face acest lucru făcând clic pe Data de începere din coloana din dreapta.

Vom face același lucru pentru variabilele ST, ED și ET care urmează să fie atribuite la Ora de începere, Data de oprire și , respectiv, Ora de oprire . Apoi, închideți înregistrarea cu o paranteză de închidere. Faceți clic pe OK.

O coloană personalizată cu înregistrările va fi adăugată la tabel. Faceți clic pe spațiul alb de lângă Înregistrare și se va deschide un panou de previzualizare. Acest lucru va aduce toate valorile din acest rând.

LuckyTemplates Orele de lucru între date: variabilele

Apoi ne putem formata variabilele prin fereastra Editor avansat . Așa ar trebui să arate fereastra Editor avansat. 

Apoi, să punem variabilele noastre în linii noi separate.

Acum, putem extinde logica de care avem nevoie. Deci, în loc de o înregistrare, am putea folosi și o expresie let imbricată aici. Rețineți că oricare dintre acestea va funcționa deoarece avem mai multe variabile sau nume de câmpuri cu valori atribuite acestora.

Data noastră de început este în prezent scrisă ca valoare text și trebuie să o transformăm într-un format de dată adecvat. Pentru a face acest lucru, includeți funcția Date.FromText în jurul acesteia. 

Pentru Ora de început , care este, de asemenea, formatată ca șir, trebuie să o convertim într-un număr. Apoi, îl putem împărți la o sută și îl putem rotunji pentru a elimina orice zecimale. 

Folosind funcția Number.From , împărțiți valoarea Data de începere la 100 . Apoi, includeți-le în următoarea funcție, Number.Round și adăugați , 0 la sfârșit pentru a rotunji fără zecimale. 

Pentru Data de încheiere și Ora de încheiere, vom copia doar aceleași funcții pe care le-am avut în Data de început și, respectiv, Ora de început și le vom înlocui cu variabilele corespunzătoare.

Odată ce variabilele sunt formatate corespunzător, creați un alt câmp în înregistrarea noastră pentru o logică suplimentară. Creați o linie nouă apăsând Enter. 

Trebuie să construim o listă de date de la prima dată până la data finală. Vom numi acele date LD și vom folosi funcția List.Dates . 

Primul parametru al acestei funcții este data de începere , care ar fi SD-ul nostru. Apoi, dorește o numărare ca număr sau diferența dintre data de început și data de încheiere. Putem obține asta folosind funcția Number.From urmată de ( ED – SD) + 1 . Rețineți că „+1” nu este afișat în următoarele seturi de capturi de ecran, dar ar trebui să existe un +1 .

Al treilea parametru se numește pas ca durată și dorim un increment de o zi. Putem obține asta folosind Duration.From (1). 

Am creat o listă cu date de la prima dată de începere până la data de oprire. Ceea ce ne dorim în continuare este să creăm o listă de timpi care să meargă împreună cu asta. 

Să numim acest LT pentru orele de listă. LT poate avea o perioadă de zi dintr-o singură zi care necesită o logică ușor diferită decât dacă se întinde pe mai multe zile. Deci, pentru asta trebuie să creăm o condiție. 

Dorim ca condiția să spună „dacă data noastră de început este aceeași cu data noastră de încheiere, atunci ora de încheiere minus ora de începere”. Rezultatele ar trebui să fie într-o listă de format, așa că folosim inițializatorul de listă reprezentat de { } . 

Acum, dacă avem un interval de date care se întinde pe mai multe zile, atunci prima dată va fi 24 minus ora de începere. Vom adăuga acest lucru la condiția noastră ca declarație else . Din nou, formatați-o ca o listă folosind parantezele.

În acest moment, nu am contabilizat celelalte zile întregi, așa că vom folosi simbolul ampersand (&) pentru a adăuga mai multe liste. Pentru fiecare zi întreagă, creați o listă cu valoarea 24 reprezentând cele 24 de ore pe care le avem într-o zi. Vom folosi List.Repeat pentru asta. 

Cu funcția List.Repeat , creați o listă care conține 24 și repetați-o de mai multe ori numărând numărul de zile din LD . Pentru a realiza acest lucru, utilizați List.Count (LD),   apoi scădeți 2 deoarece avem o listă separată pentru data de început și vom crea o altă listă pentru ora de încheiere. 

În esență, aceasta va crea o listă cu doar 24 de ore pentru fiecare zi întreagă. 

Pentru timpul de sfârșit, îl putem adăuga din nou ca o listă folosind ampersand, Apoi, folosind inițializatoarele de listă, să numim ET. 

Până acum, avem două liste mari - o listă de date și o listă de timp - cu lungimile egale una cu cealaltă. Din aceste două liste, putem construi un singur tabel. 

Într-o linie nouă, creați o altă variabilă pentru tabel pe care o vom numi t și folosim funcția Table.FromColumns . Această funcție necesită liste ca listă și vom folosi LD-ul nostru. 

LD- ul nostru conține date individuale de la data de începere până la data de încheiere. Putem transforma acea listă de date în valoarea datei de sfârșit de lună trecând LD și apelând funcția Date.EndOfMonth . Apoi, în rândul următor, să luăm și  LT- ul nostru.

Apăsați Enter pentru a crea o altă linie și a seta tabelul nostru ca tip tabel . Tabelul nostru va avea două coloane, prima coloană fiind o coloană de dată. Deci, creați o coloană Date și apelați funcția Date.Type .

A doua coloană va fi coloana noastră Ore și va fi un număr întreg. Astfel, vom crea o coloană Hrs și vom apela Int8.Type.

Avem paranteza de închidere pentru a închide înregistrarea noastră și o paranteză de închidere pentru a închide funcția Table.AddColumn . Faceți clic pe Terminat și obținem o listă de înregistrări sub o coloană Personalizată. 

Din nou, faceți clic pe spațiul alb de lângă fiecare înregistrare și tabelul ar trebui să se deschidă în partea de jos a ecranului. 

Observați că avem tabelul nostru t în ultimul rând. Să încercăm să detaliem una dintre înregistrări. Faceți clic dreapta pe primul rezultat și Adăugați ca interogare nouă . 

Vom obține același rezumat ca mai devreme și dacă facem clic pe Tabel , ar trebui să obținem valorile noastre Date și Hrs .

Dar suntem interesați în primul rând de tabelul final de rezultate. Pentru a ne concentra asupra acestui lucru, să eliminăm interogarea de ajutor făcând clic dreapta pe Personalizat și alegând Șterge . Confirmați făcând clic pe Ștergere în fereastra pop-up.

Odată șters, putem reveni la interogarea noastră inițială și facem din nou clic pe spațiul alb de lângă Înregistrare. Va afișa aceleași rezultate ca mai devreme. Dar de data aceasta, să extindem bara de formule și să apelăm [t] lângă parantezele noastre de închidere.

În coloana Personalizată, putem vedea că fiecare înregistrare este schimbată în Tabel . 

LuckyTemplates Orar de lucru între date: proiecție

Din tabelul pe care îl analizăm, există doar două coloane de interes — numele personalului și coloana noastră personalizată. Putem folosi proiecția pentru a reține numai acele două coloane din acest tabel. 

Accesați bara de formule și, lângă paranteza noastră de închidere, selectați câmpurile pe care vrem să le păstrăm plasându-le între paranteze pătrate. Apoi apăsați bifa.

Ar trebui să obținem acest rezultat.

Putem extinde tabelul imbricat făcând clic pe pictogramele săgeți  de lângă Personalizat . Debifați Utilizați numele coloanei originale ca prefix și faceți clic pe OK.

Tabelul nostru ar trebui să arate așa.

LuckyTemplates Orele de lucru între date: valori agregate

Tot ce mai rămâne de făcut acum este să adunăm aceste valori. Pentru a face acest lucru, selectați coloana Nume personal și accesați Transformare. 

În fereastra pop-up, alegeți Ore sub coloana Valori , deoarece este ceea ce vrem să cumulăm. Apoi, selectați Sumă sub Funcția Valoare agregată . Apoi faceți clic pe OK.

Rezultatul nostru final va arăta astfel. 

Crearea unui tabel de date în LuckyTemplates Cel mai rapid mod posibil
Comparație de timp pentru tabelele de date non-standard în LuckyTemplates
DateTime Value: Cum să eliminați secundele

Concluzie

În acest blog, ați văzut o abordare a Start Stop Challenge a Access Analytic. Ați învățat cum să calculați numărul total de ore lucrate de fiecare membru al personalului într-o lună și cum să faceți față posibilelor greșeli de scriere în datele dvs. 

Cu această tehnică și LuckyTemplates, orele de lucru între date pot fi calculate cu ușurință în propria organizație sau ca practică pentru a-ți aprofunda cunoștințele și abilitățile LuckyTemplates.