LuckyTemplates tussen datums werkuren oplossing

In de blog van vandaag zal ik een aanpak demonstreren voor de Start Stop Challenge van Access Analytic , waarbij met behulp van LuckyTemplates de totale werkuren tussen datums worden berekend. Je kunt de volledige video van deze tutorial onderaan deze blog bekijken .

De taak is om het totale aantal uren van een medewerker op elke maand en datum te berekenen. Ik presenteerde begin- en einddatums die maanden kunnen duren en houd rekening met de status om eventuele typefouten op te lossen. 

Inhoudsopgave

• Gegevensset voor LuckyTemplates werkuren tussen datums
• De rijen filteren
• Een aangepaste kolom toevoegen
• LuckyTemplates werkuren tussen datums: de variabelen
• LuckyTemplates werkuren tussen datums: projectie
• LuckyTemplates werkuren tussen datums: geaggregeerde waarden
• Conclusie

Gegevensset voor LuckyTemplates werkuren tussen datums

Laten we naar de Power Query-editor gaan. 

Hier zijn onze gegevens voor deze uitdaging. We hebben een tabel met kolommen voor de personeelsnaam, startdatum, starttijd, stopdatum, stoptijd en status.

De rijen filteren

Laten we beginnen met het selecteren van de rijen op basis van hun status, en Query-editor kan ons helpen met deze logica. Klik op de vervolgkeuzepijl naast Status. Selecteer Tekstfilters en kies Begint niet met. 

Schrijf "e" in het venster Filterrijen dat verschijnt . Klik vervolgens op OK .

Er gebeurt nog niets en dat is prima want we schrijven een kleine letter “e” en de “Exclude” in onze data die begint met een hoofdletter “E”.

Om dat op te lossen, gebruiken we de optionele derde parameter van Text.StartsWith . Ga in de formulebalk naar het einde van de functie Text.StartsWith. Voeg een komma toe en laten we onze Comparer.OrdinalIgnoreCase invoeren. Klik op het vinkje en dat zou de rijen uitsluiten moeten verwijderen.

Natuurlijk kunnen we iemand hebben die per ongeluk "Uitsluiten" heeft getypt zonder een initiële "E". In dat geval kunnen we wat extra logica creëren om met de situatie mee te gaan. 

We kunnen testen of het woord geen "X" bevat door onze functie uit te breiden en Text.Contains te gebruiken . Om dat te doen, voegt u of toe Text.Contains ([Status], "x", Comparer.OrdinalIgnoreCase .

Merk op dat we in vergelijking met de eerste clausule "e" hebben vervangen door een "x". We blijven de naamval negeren en zetten een aantal haakjes om onze beide clausules. Klik op het vinkje en we zouden een tabel moeten krijgen zonder de rijen met de status Exclude of Xclude .

Een aangepaste kolom toevoegen

Laten we nu naar het belangrijkste deel van deze uitdaging gaan. We voegen een aangepaste kolom toe met behulp van de gebruikersinterface en schakelen vervolgens over naar de editor. 

Begin met het minitafelpictogram in de linkerbovenhoek van de tafel. Klik erop en selecteer in de vervolgkeuzelijst Aangepaste kolom toevoegen . 

Op dit moment zijn we alleen geïnteresseerd in het meenemen van de gegevens die we later nodig zullen hebben. En omdat we met meerdere velden zullen werken, gaan we een record maken met behulp van de recordinitializers, weergegeven met vierkante haken. 

Laten we variabelen maken en de beschikbare kolommen toewijzen. Begin met het typen van SD , wat we gelijkstellen aan de startdatum. We kunnen dat doen door op Startdatum in de kolom aan de rechterkant te klikken.

We doen hetzelfde voor de variabelen ST, ED en ET die moeten worden toegewezen aan respectievelijk Start Time, Stop Date en Stop Time . Sluit vervolgens de plaat af met een sluithaakje. Klik op OK.

Een aangepaste kolom met de records wordt aan de tabel toegevoegd. Klik op de witte ruimte naast Opnemen en er wordt een voorbeeldvenster geopend. Hiermee worden alle waarden uit deze rij opgehaald.

LuckyTemplates werkuren tussen datums: de variabelen

We kunnen onze variabelen vervolgens opmaken via het venster Geavanceerde editor . Zo zou het Advanced Editor-venster eruit moeten zien. 

Laten we dan onze variabelen in afzonderlijke nieuwe regels plaatsen.

Nu kunnen we de logica uitbreiden die we nodig hebben. Dus in plaats van een record kunnen we hier ook een geneste let-expressie gebruiken. Merk op dat beide zullen werken omdat we meerdere variabelen of veldnamen hebben waaraan waarden zijn toegewezen.

Onze startdatum wordt momenteel geschreven als een tekstwaarde en we moeten die omzetten in een correct datumformaat. Wikkel hiervoor de functie Date.FromText eromheen. 

Voor de starttijd , die ook is opgemaakt als een tekenreeks, moeten we deze converteren naar een getal. Dan kunnen we het delen door honderd en afronden om eventuele decimalen te verwijderen. 

Gebruik de functie Number.From om onze Start Date- waarde te delen door 100 . Wikkel ze vervolgens in de volgende functie, Number.Round , en voeg aan het einde 0 toe om af te ronden zonder decimalen. 

Voor Einddatum en Eindtijd kopiëren we dezelfde functies die we hadden in respectievelijk Startdatum en Starttijd , en vervangen ze door de juiste variabelen.

Zodra de variabelen correct zijn opgemaakt, maakt u een ander veld in ons record voor wat extra logica. Maak een nieuwe regel door op Enter te drukken. 

We moeten een lijst met datums samenstellen vanaf de eerste datum tot aan de einddatum. We noemen die datums LD en gebruiken de functie List.Dates . 

De eerste parameter van deze functie is de start als datum die onze SD zou zijn . Vervolgens wil het een telling als getal of het verschil tussen de startdatum en de einddatum. We kunnen dat krijgen met behulp van de Number.From- functie gevolgd door ( ED – SD) + 1 . Merk op dat de "+1" niet wordt weergegeven in de volgende sets schermafbeeldingen, maar er zou een +1 moeten zijn .

De derde parameter wordt de stap als duur genoemd en we willen een toename van één dag. We kunnen dat krijgen met behulp van Duration.From (1). 

We hebben een lijst gemaakt met datums vanaf de allereerste startdatum tot aan de stopdatum. Wat we vervolgens willen, is een lijst met tijden maken die daarbij hoort. 

Laten we dit LT noemen voor lijsttijden. LT kan een dagperiode van een enkele dag hebben, wat een iets andere logica vereist dan wanneer het zich over meerdere dagen uitstrekt. Dus dat is iets waarvoor we een voorwaarde moeten scheppen. 

We willen dat de voorwaarde zegt "als onze startdatum hetzelfde is als onze einddatum, dan eindtijd min de starttijd." De resultaten moeten in een lijst met indeling zijn, dus gebruiken we de lijstinitialisatie die wordt weergegeven door { } . 

Als we nu een datumbereik hebben dat meerdere dagen beslaat, dan is de eerste datum 24 min de starttijd. We zullen dit aan onze voorwaarde toevoegen als de else- verklaring. Nogmaals, formatteer dat als een lijst met behulp van de accolades.

Op dit moment hebben we geen rekening gehouden met de andere hele dagen, dus gebruiken we het ampersand-symbool (&) om meerdere lijsten toe te voegen. Maak voor elke hele dag een lijst met de waarde 24 die staat voor de 24 uur die we in een dag hebben. Daarvoor  gebruiken we List.Repeat .

Maak met de functie List.Repeat een lijst die 24 bevat en herhaal dat een aantal keer door het aantal dagen in LD te tellen . Om dat te bereiken, gebruikt u List.Count (LD)   en trekt u vervolgens 2 af omdat we een aparte lijst hebben voor onze startdatum en we zullen een andere lijst maken voor de eindtijd. 

In wezen zal dit een lijst creëren met slechts 24 uur voor elke hele dag. 

Voor de eindtijd kunnen we het opnieuw toevoegen als een lijst met ampersand. Laten we vervolgens, met behulp van de lijstinitialisatieprogramma's, de ET aanroepen. 

Tot nu toe hebben we twee grote lijsten - een lijst met datums en een lijst met tijden - waarvan de lengtes aan elkaar gelijk zijn. Van die twee lijsten kunnen we een enkele tabel maken. 

Maak op een nieuwe regel een andere variabele voor de tabel die we t zullen noemen en gebruik de functie Table.FromColumns . Deze functie vereist lijsten als lijst en we zullen onze LD gebruiken. 

Onze LD bevat individuele data vanaf de startdatum tot de stopdatum. We kunnen die lijst met datums transformeren naar de datumwaarde aan het einde van de maand door LD door te geven en de functie Date.EndOfMonth aan te roepen. Laten we in de volgende regel ook  onze LT opnemen.

Druk op Enter om nog een regel te maken en stel onze tabel in als type tabel . Onze tabel heeft twee kolommen, de eerste kolom is een datumkolom. Maak dus een Datum- kolom en roep de Date.Type- functie aan.

De tweede kolom is onze kolom Uren en is een geheel getal. We maken dus een Hrs- kolom en bellen Int8.Type.

We hebben de sluitingshaak om ons record te sluiten en een sluitingshaakje om onze Table.AddColumn- functie te sluiten. Klik op Gereed en we krijgen een lijst met records onder een aangepaste kolom. 

Klik nogmaals op de witte ruimte naast elk record en de tabel zou onderaan het scherm moeten openen. 

Merk op dat we onze t-tabel in de laatste rij hebben. Laten we proberen in te zoomen op een van de Records. Klik met de rechtermuisknop op het eerste resultaat en voeg toe als nieuwe query . 

We krijgen dezelfde samenvatting als eerder en als we op de Tabel klikken , zouden we onze Datum- en Uur- waarden moeten krijgen.

Maar we zijn vooral geïnteresseerd in de uiteindelijke outputtabel. Laten we, om ons daarop te concentreren, de hulpvraag verwijderen door met de rechtermuisknop op Aangepast te klikken en Verwijderen te kiezen . Bevestig door op Verwijderen te klikken in het pop-upvenster.

Eenmaal verwijderd, kunnen we teruggaan naar onze oorspronkelijke zoekopdracht en opnieuw op de witte ruimte naast Record klikken. Het zal dezelfde resultaten laten zien als eerder. Maar laten we deze keer de formulebalk uitvouwen en [t] aanroepen naast onze sluithaken.

In de kolom Aangepast kunnen we zien dat elke record is gewijzigd in Tabel . 

LuckyTemplates werkuren tussen datums: projectie

Van de tabel waar we naar kijken, zijn er slechts twee interessante kolommen: de naam van het personeel en onze aangepaste kolom. We kunnen projectie gebruiken om alleen die twee kolommen uit deze tabel te behouden. 

Ga naar de formulebalk en selecteer naast ons haakje sluiten de velden die we willen behouden door ze tussen vierkante haken te plaatsen. Druk dan op het vinkje.

We zouden dit resultaat moeten krijgen.

We kunnen de geneste tabel uitvouwen door op de pijlpictogrammen  naast Aangepast te klikken . Haal het vinkje weg bij Gebruik originele kolomnaam als voorvoegsel en klik op OK.

Onze tafel zou er zo uit moeten zien.

LuckyTemplates werkuren tussen datums: geaggregeerde waarden

Het enige dat u nu hoeft te doen, is deze waarden samen te voegen. Om dat te doen, selecteert u de kolom Personeelsnaam en gaat u naar Transformeren. 

Kies in het pop-upvenster Hrs onder de kolom Waarden , want dat is wat we willen aggregeren. Selecteer vervolgens Som onder Geaggregeerde waardefunctie . Klik vervolgens op OK.

Onze uiteindelijke uitvoer ziet er als volgt uit. 

Een datumtabel maken in LuckyTemplates De snelst mogelijke manier
Tijdvergelijking voor niet-standaard datumtabellen in LuckyTemplates
DateTime-waarde: hoe u seconden kunt verwijderen

Conclusie

In deze blog heeft u één benadering gezien van de Start Stop-uitdaging van Access Analytics. U leerde hoe u het totaal aantal gewerkte uren van elke medewerker in een maand berekent en hoe u omgaat met eventuele typefouten in uw gegevens. 

Met deze techniek en LuckyTemplates kunnen tussentijdse werkuren eenvoudig worden berekend in uw eigen organisatie, of als oefening om uw LuckyTemplates kennis en vaardigheden te verdiepen.