LuckyTemplates Mellem Datoer Arbejdstid Løsning

I dagens blog vil jeg demonstrere en tilgang til Access Analytics Start Stop Challenge , hvor man ved hjælp af LuckyTemplates beregner den samlede arbejdstid mellem datoerne. Du kan se den fulde video af denne tutorial nederst på denne blog .

Opgaven er at beregne det samlede antal timer for en medarbejder på hver måned og dato. Jeg præsenterede start- og slutdatoer, der kan gå over måneder og tage status i betragtning ved håndtering af mulige tastefejl. 

Indholdsfortegnelse

• Datasæt til LuckyTemplates Mellem-datoer arbejdstid
• Filtrering af rækkerne
• Tilføjelse af en brugerdefineret kolonne
• LuckyTemplates mellem-datoer Arbejdstid: Variablerne
• LuckyTemplates mellem-datoer Arbejdstid: Projektion
• LuckyTemplates mellem-datoer Arbejdstid: Samlede værdier
• Konklusion

Datasæt til LuckyTemplates Mellem-datoer arbejdstid

Lad os gå til Power Query-editoren. 

Her er vores data til denne udfordring. Vi har en tabel med kolonner for personalenavn, startdato, starttidspunkt, stopdato, stoptidspunkt og status.

Filtrering af rækkerne

Lad os starte med at vælge rækkerne baseret på deres status, og Query editor kan hjælpe os med denne logik. Klik på rullemenuen ved siden af ​​Status. Vælgog vælg Begynder ikke med. 

Skriv "e" i vinduet Filter Rows , der vises . Klik derefter på OK .

Intet vil ske endnu, og det er fint, fordi vi skriver et lille "e" og "Ekskluder" i vores data, der starter med et stort "E".

For at rette op på det, bruger vi den valgfri tredje parameter i Text.StartsWith . Gå til slutningen af ​​funktionen Text.StartsWith i formellinjen . Tilføj et komma, og lad os indtaste vores Comparer.OrdinalIgnoreCase. Klik på fluebenet, og det skulle fjerne ekskluder rækkerne.

Selvfølgelig kunne vi have nogen, der fejlagtigt har skrevet "Ekskluder" uden et indledende "E." I så fald kan vi skabe noget yderligere logik, der passer til situationen. 

Vi kan teste om ordet ikke indeholder et "X" ved at udvide vores funktion og bruge Text.Contains . For at gøre det skal du tilføje eller Text.Contains ([Status], "x", Comparer.OrdinalIgnoreCase .

Bemærk, at sammenlignet med den første klausul erstattede vi "e" med et "x." Vi bliver ved med at ignorere sagen og vikler et sæt parenteser om begge vores klausuler. Klik på afkrydsningsfeltet, og vi skulle få en tabel uden de rækker, der indeholder Exclude eller Xclude status.

Tilføjelse af en brugerdefineret kolonne

Lad os nu gå til hoveddelen af ​​denne udfordring. Vi tilføjer en brugerdefineret kolonne ved hjælp af brugergrænsefladen og skifter derefter til editoren. 

Start med minibordikonet i øverste venstre hjørne af bordet. Klik på den, og vælg Tilføj i rullemenuen . 

På dette tidspunkt er vi kun interesserede i at bringe de data, vi skal bruge senere. Og fordi vi vil arbejde med flere felter, lad os oprette en post ved hjælp af postinitialisatorerne, repræsenteret med firkantede parenteser. 

Lad os oprette variabler og tildele de tilgængelige kolonner. Start med at skrive SD , som vi svarer til startdatoen. Det kan vi gøre ved at klikke på Startdato i kolonnen til højre.

Vi vil gøre det samme for variablerne ST, ED og ET , der skal tildeles til henholdsvis starttid, stopdato og stoptid . Luk derefter posten med en afsluttende parentes. Klik på OK.

En brugerdefineret kolonne med posterne vil blive føjet til tabellen. Klik på det hvide område ved siden af ​​Optag , og en forhåndsvisningsrude åbnes. Dette vil bringe alle værdierne fra denne række ind.

LuckyTemplates mellem-datoer Arbejdstid: Variablerne

Vi kan derefter formatere vores variabler gennem vinduet Advanced Editor . Sådan skal vinduet Advanced Editor se ud. 

Lad os derefter sætte vores variable i separate nye linjer.

Nu kan vi udvide den logik, vi har brug for. Så i stedet for en post kunne vi også bruge et indlejret let-udtryk her. Bemærk, at en af ​​disse vil fungere, fordi vi har flere variabler eller feltnavne med værdier tildelt dem.

Vores startdato er i øjeblikket skrevet som en tekstværdi, og vi skal omdanne den til et korrekt datoformat. For at gøre det skal du vikle funktionen Date.FromText omkring den. 

For starttidspunktet , som også er formateret som en streng, skal vi konvertere det til et tal. Derefter kan vi dividere det med hundrede og afrunde det for at fjerne eventuelle decimaler. 

Brug funktionen Number.From til at dividere vores startdatoværdi med 100 . Pak dem derefter ind i den næste funktion, Number.Round , og tilføj , 0 i slutningen for at runde af uden decimaler. 

For Slutdato og Sluttid kopierer vi bare de samme funktioner, som vi havde i henholdsvis Startdato og Starttid , og erstatter dem med de relevante variabler.

Når variablerne er korrekt formateret, skal du oprette et andet felt i vores post for at få noget yderligere logik. Opret en ny linje ved at trykke på Enter. 

Vi skal konstruere en liste over datoer fra den første dato og helt op til slutdatoen. Vi kalder disse datoer LD , og ​​vi bruger funktionen List.Dates . 

Den første parameter i denne funktion er start som dato , som ville være vores SD. Derefter vil den tælle som tal eller forskellen mellem startdatoen og slutdatoen. Det kan vi få ved at bruge funktionen Number.From efterfulgt af ( ED – SD) + 1 . Bemærk, at "+1" ikke vises i de næste sæt skærmbilleder, men der bør være en +1 .

Den tredje parameter kaldes trinnet som varighed , og vi ønsker en stigning på én dag. Det kan vi få ved at bruge Duration.From (1). 

Vi har lavet en liste med datoer fra den allerførste startdato op til stopdatoen. Det næste, vi ønsker, er at lave en liste over tidspunkter, der går sammen med det. 

Lad os kalde dette LT for listetider. LT kan have en dagsperiode på en enkelt dag, som kræver en lidt anden logik, end hvis den strækker sig over flere dage. Så det er noget, vi skal skabe forudsætninger for. 

Vi ønsker, at betingelsen skal sige "hvis vores startdato er den samme som vores slutdato, så sluttidspunkt minus starttidspunkt." Resultaterne skal være i en liste med formater, så vi bruger listeinitialisereren repræsenteret af { } . 

Hvis vi nu har et datointerval, der strækker sig over flere dage, så vil den første dato være 24 minus starttidspunktet. Vi vil tilføje dette til vores tilstand som den anden erklæring. Formater det igen som en liste ved hjælp af de krøllede parenteser.

På dette tidspunkt har vi ikke taget højde for de andre hele dage, så vi bruger og-tegnet (&) til at tilføje flere lister. For hver hel dag skal du oprette en liste med værdien 24, der repræsenterer de 24 timer, vi har på et døgn. Vi bruger List.Repeat til det. 

Med funktionen List.Repeat skal du oprette en liste, der indeholder 24, og gentage det et antal gange ved at tælle antallet af dage i LD . For at opnå det skal du bruge List.Count (LD)   og derefter trække 2 fra, fordi vi har en separat liste for vores startdato, og vi vil oprette en anden liste for sluttidspunktet. 

I det væsentlige vil dette skabe en liste med kun 24 timer for hver hel dag. 

Til sluttidspunktet kan vi tilføje det igen som en liste ved hjælp af og-tegn. Lad os derefter, ved at bruge listeinitialisererne, kalde ET. 

Indtil videre har vi to store lister - en liste over datoer og en liste over tidspunkter - med deres længder lig med hinanden. Fra disse to lister kan vi konstruere en enkelt tabel. 

I en ny linje skal du oprette en anden variabel til tabellen, som vi kalder t og bruge funktionen Table.FromColumns . Denne funktion kræver lister som liste, og vi bruger vores LD. 

Vores LD indeholder individuelle datoer fra startdatoen op til stopdatoen. Vi kan transformere denne liste over datoer til slutningen af ​​månedens datoværdi ved at overføre LD og kalde funktionen Date.EndOfMonth . Så i næste linje, lad os også  få vores LT .

Tryk på Enter for at oprette endnu en linje og indstil vores tabel som typetabel . Vores tabel vil have to kolonner, den første kolonne er en datokolonne. Så opret en Dato- kolonne og kald Date.Type- funktionen.

Den anden kolonne vil være vores Hours- kolonne, og den vil være et heltal. Derfor opretter vi en Hrs- kolonne og kalder Int8.Type.

Vi har den afsluttende parentes til at lukke vores post og en afsluttende parentes for at lukke vores Table.AddColumn funktion. Klik på Udført , og vi får en liste over poster under en brugerdefineret kolonne. 

Klik igen på det hvide felt ved siden af ​​hver post , og bordet skulle åbne nederst på skærmen. 

Bemærk, at vi har vores t-tabel i sidste række. Lad os prøve at bore i en af ​​Records. Højreklik på det første resultat og Tilføj som ny forespørgsel . 

Vi får den samme oversigt som tidligere, og hvis vi klikker på tabellen , skulle vi få vores Dato- og Hrs -værdier.

Men vi er primært interesserede i den endelige outputtabel. For at fokusere på det, lad os fjerne hjælpeforespørgslen ved at højreklikke på Brugerdefineret og vælge Slet . Bekræft ved at klikke på Slet i pop op-vinduet.

Når den er slettet, kan vi gå tilbage til vores oprindelige forespørgsel og igen klikke på det hvide felt ved siden af ​​Optag. Det vil vise de samme resultater som tidligere. Men denne gang, lad os udvide formellinjen og kalde [t] ved siden af ​​vores afsluttende parenteser.

I kolonnen Brugerdefineret kan vi se, at hver post er ændret til tabel . 

LuckyTemplates mellem-datoer Arbejdstid: Projektion

Fra den tabel, vi kigger på, er der kun to kolonner af interesse – personalenavnet og vores brugerdefinerede kolonne. Vi kan bruge projektion til at beholde disse to kolonner alene fra denne tabel. 

Gå til formellinjen, og ud for vores afsluttende parentes skal du vælge de felter, som vi vil beholde, ved at placere dem inden for firkantede parenteser. Tryk derefter på fluebenet.

Vi burde få dette resultat.

Vi kan udvide ved at klikke på pileikonerne  ved siden af ​​Custom . Fjern markeringen i Brug originalt kolonnenavn som præfiks, og klik på OK.

Vores tabel skulle se sådan ud.

LuckyTemplates mellem-datoer Arbejdstid: Samlede værdier

Det eneste, der er tilbage at gøre nu, er at aggregere disse værdier. For at gøre det skal du vælge kolonnen Personalenavn og gå til Transform. 

I pop op-vinduet skal du vælge Hrs under værdisøjlen, fordi det er det, vi vil samle. Vælg derefter Sum under . Klik derefter på OK.

Vores endelige output vil se sådan ud. 

Konklusion

I denne blog har du set én tilgang til Access Analytics Start Stop Challenge. Du lærte, hvordan du beregner det samlede antal timer, hver medarbejder har arbejdet på en måned, og hvordan du håndterer mulige tastefejl i dine data. 

Med denne teknik og LuckyTemplates kan arbejdstiden mellem datoer nemt beregnes i din egen organisation, eller som en praksis for at uddybe din LuckyTemplates viden og færdigheder.