LuckyTemplates Between Dates Arbetstimmar Lösning

I dagens blogg kommer jag att demonstrera ett tillvägagångssätt för Access Analytics Start Stop Challenge där, med hjälp av LuckyTemplates, kommer totala arbetstimmar mellan datum att beräknas. Du kan se hela videon av denna handledning längst ner på den här bloggen .

Uppgiften är att beräkna det totala antalet timmar för en anställd per månad och datum. Jag presenterade start- och slutdatum som kan sträcka sig över månader och ta hänsyn till status vid hantering av eventuella stavfel. 

Innehållsförteckning

• Datauppsättning för Lucky Templates Between-Dates Arbetstimmar
• Filtrera raderna
• Lägga till en anpassad kolumn
• LuckyTemplates Between-Dates Arbetstider: Variablerna
• LuckyTemplates Between-Dates Arbetstider: Projektion
• LuckyTemplates Between-Dates Arbetstimmar: Samlade värden
• Slutsats

Datauppsättning för Lucky Templates Between-Dates Arbetstimmar

Låt oss gå till Power Query-redigeraren. 

Här är vår data för denna utmaning. Vi har en tabell med kolumner för personalens namn, startdatum, starttid, stoppdatum, stopptid och status.

Filtrera raderna

Låt oss börja med att välja raderna baserat på deras status, och frågeredigeraren kan hjälpa oss med denna logik. Klicka på rullgardinspilen bredvid Status. Väljoch välj Börjar inte med. 

Skriv "e" i fönstret Filterrader som kommer upp . Klicka sedan på OK .

Inget kommer att hända ännu och det är bra eftersom vi skriver ett gement "e" och "Exkludera" i vår data som börjar med ett stort "E".

För att fixa det använder vi den valfria tredje parametern Text.StartsWith . Gå till slutet av funktionen Text.StartsWith i formelfältet. Lägg till ett kommatecken och låt oss gå in i vårt Comparer.OrdinalIgnoreCase. Klicka på bockmarkeringen och det bör ta bort exkluderingsraderna .

Naturligtvis kan vi ha någon som felaktigt har skrivit "Uteslut" utan ett initialt "E." I så fall kan vi skapa ytterligare logik för att följa situationen. 

Vi kan testa om ordet inte innehåller ett "X" genom att utöka vår funktion och använda Text.Contains . För att göra det, lägg till eller Text.Contains ([Status], “x”, Comparer.OrdinalIgnoreCase .

Lägg märke till att jämfört med den första klausulen ersatte vi "e" med ett "x." Vi fortsätter att ignorera fallet och lindar en uppsättning parenteser runt båda våra klausuler. Klicka på bockmarkeringen och vi bör få en tabell utan raderna som innehåller statusen Exclude eller Xclude .

Lägga till en anpassad kolumn

Låt oss nu gå till huvuddelen av denna utmaning. Vi lägger till en anpassad kolumn med hjälp av användargränssnittet och byter sedan till redigeraren. 

Börja med minibordsikonen i det övre vänstra hörnet av bordet. Klicka på den och välj Lägg till i rullgardinsmenyn . 

För närvarande är vi bara intresserade av att ta med den data som vi behöver senare. Och eftersom vi kommer att arbeta med flera fält, låt oss skapa en post med hjälp av postinitierare, representerade med hakparenteser. 

Låt oss skapa variabler och tilldela tillgängliga kolumner. Börja med att skriva SD vilket vi kommer att likställa med Startdatum. Vi kan göra det genom att klicka på Startdatum i kolumnen till höger.

Vi kommer att göra samma sak för variablerna ST, ED och ET som ska tilldelas starttid, stoppdatum respektive stopptid . Stäng sedan av posten med en avslutande parentes. Klicka på OK.

En anpassad kolumn med posterna kommer att läggas till i tabellen. Klicka på det vita utrymmet bredvid Spela in och en förhandsgranskningsruta öppnas. Detta kommer att ta in alla värden från den här raden.

LuckyTemplates Between-Dates Arbetstider: Variablerna

Vi kan sedan formatera våra variabler genom fönstret Advanced Editor . Så här ska fönstret Advanced Editor se ut. 

Låt oss sedan lägga våra variabler i separata nya rader.

Nu kan vi utöka logiken som vi behöver. Så istället för en post kan vi också använda ett kapslat let-uttryck här. Observera att någon av dessa kommer att fungera eftersom vi har flera variabler eller fältnamn med värden tilldelade dem.

Vårt startdatum är för närvarande skrivet som ett textvärde och vi måste omvandla det till ett korrekt datumformat. För att göra det, linda funktionen Date.FromText runt den. 

För Starttiden som också är formaterad som en sträng måste vi konvertera den till ett tal. Sedan kan vi dividera det med hundra och runda det för att ta bort eventuella decimaler. 

Använd funktionen Number.From och dividera vårt startdatumvärde med 100 . Slå sedan in dem i nästa funktion, Number.Round , och lägg till , 0 i slutet för att avrunda utan decimaler. 

För Slutdatum och Sluttid kommer vi bara att kopiera samma funktioner som vi hade i Startdatum respektive Starttid , och ersätta dem med lämpliga variabler.

När variablerna är korrekt formaterade, skapa ett annat fält i vår post för ytterligare logik. Skapa en ny rad genom att trycka på Enter. 

Vi måste konstruera en lista med datum från det första datumet ända fram till slutdatumet. Vi kallar dessa datum LD och vi använder funktionen List.Dates . 

Den första parametern för den här funktionen är start som datum som skulle vara vårt SD. Sedan vill den räknas som nummer eller skillnaden mellan startdatum och slutdatum. Vi kan få det med funktionen Number.From följt av ( ED – SD) + 1 . Observera att "+1" inte visas i nästa uppsättningar av skärmdumpar, men det bör finnas en +1 .

Den tredje parametern kallas steget som varaktighet och vi vill ha ett steg på en dag. Vi kan få det med Duration.From (1). 

Vi har skapat en lista med datum från det allra första startdatumet fram till stoppdatumet. Vad vi vill härnäst är att skapa en lista över tider som går tillsammans med det. 

Låt oss kalla detta LT för listtider. LT kan ha en dagsperiod på en enda dag som kräver en något annorlunda logik än om den sträcker sig över flera dagar. Så det är något som vi måste skapa förutsättningar för. 

Vi vill att villkoret ska säga "om vårt startdatum är detsamma som vårt slutdatum, då sluttiden minus starttiden." Resultaten bör vara i en lista med format så vi använder listinitieraren som representeras av { } . 

Nu, om vi har ett datumintervall som sträcker sig över flera dagar, kommer det första datumet att vara 24 minus starttiden. Vi kommer att lägga till detta till vårt villkor som det andra uttalandet. Återigen, formatera den som en lista med de krulliga parenteserna.

Vid det här laget har vi inte tagit hänsyn till de andra hela dagarna så vi använder et-tecken (&) för att lägga till flera listor. Skapa en lista för varje hel dag med värdet 24 som representerar de 24 timmar vi har på en dag. Vi använder List.Repeat för det. 

Med funktionen List.Repeat , skapa en lista som innehåller 24 och upprepa det ett antal gånger genom att räkna antalet dagar i LD . För att uppnå det, använd List.Count (LD)   och subtrahera sedan 2 eftersom vi har en separat lista för vårt startdatum och vi skapar en annan lista för sluttiden. 

I huvudsak kommer detta att skapa en lista med endast 24 timmar för varje hel dag. 

För sluttiden kan vi lägga till den igen som en lista med et-tecken. Sedan, med hjälp av listinitierarna, låt oss kalla ET. 

Hittills har vi två stora listor - en lista över datum och en lista över tider - där deras längder är lika med varandra. Från dessa två listor kan vi konstruera en enda tabell. 

På en ny rad, skapa en annan variabel för tabellen som vi kallar t och använd funktionen Table.FromColumns . Denna funktion kräver listor som lista och vi använder vår LD. 

Vår LD innehåller individuella datum från startdatum till stoppdatum. Vi kan omvandla den listan med datum till slutet av månadens datumvärde genom att skicka LD och anropa Date.EndOfMonth -funktionen. Sedan i nästa rad, låt oss få vår LT också. 

Tryck på Enter för att skapa ytterligare en rad och ställ in vår tabell som typtabell . Vår tabell kommer att ha två kolumner, den första kolumnen är en datumkolumn. Så skapa en datumkolumn och anropa funktionen Date.Type .

Den andra kolumnen kommer att vara vår Hours- kolumn och den kommer att vara ett heltal. Därför skapar vi en Hrs- kolumn och anropar Int8.Type.

Vi har den avslutande parentesen för att stänga vår post och en avslutande parentes för att stänga vår Table.AddColumn -funktion. Klicka på Klar så får vi en lista med poster under en Anpassad kolumn. 

Återigen, klicka på det vita utrymmet bredvid varje post och bordet bör öppnas längst ner på skärmen. 

Lägg märke till att vi har vår t-tabell på sista raden. Låt oss försöka borra in i en av skivorna. Högerklicka på det första resultatet och Lägg till som ny fråga . 

Vi kommer att få samma sammanfattning som tidigare och om vi klickar på tabellen bör vi få våra datum- och timvärden .

Men vi är främst intresserade av den slutliga outputtabellen. För att fokusera på det, låt oss ta bort hjälpfrågan genom att högerklicka på Anpassad och välja Ta bort . Bekräfta genom att klicka på Ta bort i popup-fönstret.

När vi har raderat den kan vi gå tillbaka till vår ursprungliga fråga och klicka på det vita utrymmet bredvid Spela in igen. Det kommer att visa samma resultat som tidigare. Men den här gången, låt oss utöka formelfältet och anropa [t] bredvid våra avslutande parenteser.

I kolumnen Anpassad kan vi se att varje post ändras till Tabell . 

LuckyTemplates Between-Dates Arbetstider: Projektion

Från tabellen som vi tittar på finns det bara två kolumner av intresse – personalens namn och vår anpassade kolumn. Vi kan använda projektion för att behålla de två kolumnerna ensamma från den här tabellen. 

Gå till formelfältet, och bredvid vår avslutande parentes, välj de fält som vi vill behålla genom att placera dem inom hakparenteser. Tryck sedan på bocken.

Vi borde få det här resultatet.

Vi kan utöka genom att klicka på pilikonerna  bredvid Custom . Avmarkera Använd originalkolumnnamn som prefix och klicka på OK.

Vårt bord borde se ut så här.

LuckyTemplates Between-Dates Arbetstimmar: Samlade värden

Allt som återstår att göra nu är att aggregera dessa värden. För att göra det, välj kolumnen Personalnamn och gå till Transform. 

I popup-fönstret väljer du timmar under värdekolumnen eftersom det är det vi vill samla. Välj sedan Summa under . Klicka sedan på OK.

Vår slutliga produktion kommer att se ut så här. 

Slutsats

I den här bloggen har du sett en metod för Access Analytics Start Stop Challenge. Du lärde dig hur man beräknar det totala antalet arbetade timmar av varje anställd under en månad, och hur man hanterar eventuella stavfel i din data. 

Med denna teknik och LuckyTemplates kan arbetstimmar mellan datum enkelt beräknas i din egen organisation, eller som en praxis för att fördjupa dina LuckyTemplates kunskaper och färdigheter.