Soluzione LuckyTemplates tra date e orario di lavoro

Nel blog di oggi, dimostrerò un approccio alla sfida Start Stop di Access Analytic in cui, utilizzando LuckyTemplates, verranno calcolate le ore lavorative totali tra le date. Puoi guardare il video completo di questo tutorial in fondo a questo blog .

L'attività consiste nel calcolare il numero totale di ore per un membro del personale in ogni mese e data. Ho presentato date di inizio e fine che possono superare i mesi e tenere conto dello stato affrontando possibili errori di battitura. 

Sommario

• Set di dati per l'orario di lavoro tra le date di LuckyTemplates
• Filtraggio delle righe
• Aggiunta di una colonna personalizzata
• Orario di lavoro tra le date di LuckyTemplates: le variabili
• Orario di lavoro tra le date di LuckyTemplates: proiezione
• Orario di lavoro tra le date di LuckyTemplates: valori aggregati
• Conclusione

Set di dati per l'orario di lavoro tra le date di LuckyTemplates

Passiamo all'editor di Power Query. 

Ecco i nostri dati per questa sfida. Abbiamo una tabella con colonne per il nome del personale, la data di inizio, l'ora di inizio, la data di fine, l'ora di fine e lo stato.

Filtraggio delle righe

Iniziamo selezionando le righe in base al loro stato e l'editor di query può aiutarci con questa logica. Fare clic sulla freccia del menu a discesa accanto a Stato. Seleziona Filtri di testo e scegli Non inizia con. 

Nella finestra Filtra righe che verrà visualizzata, scrivi "e". Quindi fare clic su OK .

Non succederà ancora nulla e va bene perché scriviamo una "e" minuscola e l'"Escludi" nei nostri dati che inizia con una "E" maiuscola.

Per risolvere questo problema, utilizzeremo il terzo parametro facoltativo di Text.StartsWith . Nella barra della formula, vai alla fine della funzione Text.StartsWith. Aggiungi una virgola e entriamo nel nostro Comparer.OrdinalIgnoreCase. Fai clic sul segno di spunta e questo dovrebbe rimuovere le righe Escludi .

Naturalmente, potremmo avere qualcuno che ha digitato erroneamente "Escludi" senza una "E" iniziale. In tal caso, possiamo creare una logica aggiuntiva per andare d'accordo con la situazione. 

Possiamo verificare se la parola non include una "X" estendendo la nostra funzione e utilizzando Text.Contains . Per farlo, aggiungi o Text.Contains ([Status], "x", Comparer.OrdinalIgnoreCase .

Si noti che rispetto alla prima clausola, abbiamo sostituito la "e" con una "x". Continuiamo a ignorare il caso e avvolgiamo una serie di parentesi attorno a entrambe le nostre clausole. Fai clic sul segno di spunta e dovremmo ottenere una tabella senza le righe contenenti lo stato Exclude o Xclude .

Aggiunta di una colonna personalizzata

Passiamo ora alla parte principale di questa sfida. Aggiungeremo una colonna personalizzata utilizzando l'interfaccia utente e poi passeremo all'editor. 

Inizia con l' icona del mini tavolo nell'angolo in alto a sinistra del tavolo. Fare clic su di esso e nel menu a discesa selezionare Aggiungi colonna personalizzata . 

A questo punto, ci interessa solo portare i dati che ci serviranno in seguito. E poiché lavoreremo con più campi, creiamo un record utilizzando gli inizializzatori di record, rappresentati con parentesi quadre. 

Creiamo le variabili e assegniamo le colonne disponibili. Inizia digitando SD che equipareremo alla data di inizio. Possiamo farlo facendo clic su Data di inizio nella colonna a destra.

Faremo lo stesso per le variabili ST, ED e ET da assegnare rispettivamente a Start Time, Stop Date e Stop Time . Quindi, chiudi il record con una parentesi di chiusura. Fare clic su OK.

Una colonna personalizzata con i record verrà aggiunta alla tabella. Fare clic sullo spazio bianco accanto a Registra e si aprirà un riquadro di anteprima. Questo introdurrà tutti i valori di questa riga.

Orario di lavoro tra le date di LuckyTemplates: le variabili

Possiamo quindi formattare le nostre variabili attraverso la finestra Editor avanzato . Ecco come dovrebbe apparire la finestra dell'editor avanzato. 

Quindi, mettiamo le nostre variabili in nuove righe separate.

Ora possiamo espandere la logica di cui abbiamo bisogno. Quindi, invece di un record, potremmo anche usare un'espressione let nidificata qui. Nota che uno di questi funzionerà perché abbiamo più variabili o nomi di campo con valori assegnati a loro.

La nostra data di inizio è attualmente scritta come valore di testo e dobbiamo trasformarla in un formato di data appropriato. Per fare ciò, avvolgi la funzione Date.FromText attorno ad esso. 

Per l' ora di inizio , anch'essa formattata come stringa, dobbiamo convertirla in un numero. Quindi, possiamo dividerlo per cento e arrotondarlo per rimuovere eventuali cifre decimali. 

Utilizzando la funzione Number.From , dividi il valore della data di inizio per 100 . Quindi, avvolgili all'interno della funzione successiva, Number.Round , e aggiungi , 0 alla fine per arrotondare senza cifre decimali. 

Per End Date e End Time, copieremo semplicemente le stesse funzioni che avevamo rispettivamente in Start Date e Start Time e le sostituiremo con le variabili appropriate.

Una volta che le variabili sono formattate correttamente, crea un altro campo nel nostro record per una logica aggiuntiva. Crea una nuova riga premendo Invio. 

Dobbiamo costruire un elenco di date dalla prima data fino alla data di fine. Chiameremo quelle date LD e useremo la funzione List.Dates . 

Il primo parametro di questa funzione è la data di inizio che sarebbe la nostra SD. Quindi, vuole un conteggio come numero o la differenza tra la data di inizio e la data di fine. Possiamo ottenerlo usando la funzione Number.From seguita da ( ED – SD) + 1 . Nota che il "+1" non viene mostrato nelle prossime serie di schermate, ma dovrebbe esserci un +1 .

Il terzo parametro si chiama step come durata e vogliamo un incremento di un giorno. Possiamo ottenerlo usando Duration.From (1). 

Abbiamo creato un elenco con le date dalla primissima data di inizio fino alla data di fine. Quello che vogliamo dopo è creare un elenco di orari che vada di pari passo con quello. 

Chiamiamo questo LT per i tempi di elenco. LT può avere un periodo giornaliero di un solo giorno che richiede una logica leggermente diversa rispetto a se si estende su più giorni. Quindi è qualcosa per cui dobbiamo creare una condizione. 

Vogliamo che la condizione dica "se la nostra data di inizio è la stessa della nostra data di fine, allora l'ora di fine meno l'ora di inizio". I risultati dovrebbero essere in un elenco di formato, quindi usiamo l' inizializzatore di elenco rappresentato da { } . 

Ora, se disponiamo di un intervallo di date che si estende su più giorni, la prima data sarà 24 meno l'ora di inizio. Lo aggiungeremo alla nostra condizione come istruzione else . Ancora una volta, formattalo come un elenco usando le parentesi graffe.

A questo punto, non abbiamo tenuto conto degli altri giorni interi, quindi utilizzeremo il simbolo e commerciale (&) per aggiungere più elenchi. Per ogni giorno intero, crea un elenco con il valore 24 che rappresenta le 24 ore che abbiamo in un giorno. Useremo List.Repeat per questo. 

Con la funzione List.Repeat , crea un elenco che contiene 24 e ripetilo un numero di volte contando il numero di giorni in LD . Per ottenere ciò, usa List.Count (LD)   quindi sottrai 2 perché abbiamo un elenco separato per la nostra data di inizio e creeremo un altro elenco per l'ora di fine. 

In sostanza, questo creerà un elenco con solo 24 ore per ogni giorno intero. 

Per l'ora di fine, possiamo aggiungerla di nuovo come elenco utilizzando la e commerciale, quindi, utilizzando gli inizializzatori di elenco, chiamiamo ET. 

Finora, abbiamo due grandi elenchi, un elenco di date e un elenco di orari, con le loro lunghezze uguali tra loro. Da questi due elenchi, possiamo costruire una singola tabella. 

In una nuova riga, creiamo un'altra variabile per la tabella che chiameremo t e utilizzeremo la funzione Table.FromColumns . Questa funzione richiede liste come lista e useremo il nostro LD. 

Il nostro LD contiene singole date dalla data di inizio fino alla data di fine. Possiamo trasformare quell'elenco di date nel valore della data di fine mese passando LD e chiamando la funzione Date.EndOfMonth . Quindi nella riga successiva, prendiamo anche  il nostro LT .

Premi Invio per creare un'altra riga e imposta la nostra tabella come tipo table . La nostra tabella avrà due colonne, la prima colonna è una colonna data. Quindi, crea una colonna Data e chiama la funzione Date.Type .

La seconda colonna sarà la nostra colonna Ore e sarà un numero intero. Pertanto, creeremo una colonna Hrs e chiameremo Int8.Type.

Abbiamo la parentesi di chiusura per chiudere il nostro record e una parentesi di chiusura per chiudere la nostra funzione Table.AddColumn . Fare clic su Fine e otteniamo un elenco di record in una colonna personalizzata. 

Ancora una volta, fai clic sullo spazio bianco accanto a ciascun record e la tabella dovrebbe aprirsi nella parte inferiore dello schermo. 

Si noti che abbiamo la nostra tabella t nell'ultima riga. Proviamo a scavare in uno dei Registri. Fare clic con il tasto destro sul primo risultato e Aggiungi come nuova query . 

Otterremo lo stesso riepilogo di prima e se clicchiamo su Table , dovremmo ottenere i nostri valori Date e Hrs .

Ma siamo principalmente interessati alla tabella di output finale. Per concentrarci su questo, rimuoviamo la query di aiuto facendo clic con il pulsante destro del mouse su Custom e scegliendo Delete . Conferma facendo clic su Elimina nella finestra pop-up.

Una volta eliminato, possiamo tornare alla nostra query iniziale e fare nuovamente clic sullo spazio bianco accanto a Record. Mostrerà gli stessi risultati di prima. Ma questa volta, espandiamo la barra della formula e chiamiamo [t] accanto alle nostre parentesi di chiusura.

Nella colonna Custom, possiamo vedere che ogni Record è cambiato in Table . 

Orario di lavoro tra le date di LuckyTemplates: proiezione

Dalla tabella che stiamo osservando, ci sono solo due colonne di interesse: il nome dello staff e la nostra colonna personalizzata. Possiamo usare la proiezione per conservare solo quelle due colonne di questa tabella. 

Vai alla barra della formula e accanto alla nostra parentesi di chiusura, seleziona i campi che vogliamo mantenere inserendoli tra parentesi quadre. Quindi premere il segno di spunta.

Dovremmo ottenere questo risultato.

Possiamo espandere la tabella nidificata facendo clic sulle icone delle frecce  accanto a Custom . Deseleziona Usa il nome della colonna originale come prefisso e fai clic su OK.

La nostra tabella dovrebbe apparire così.

Orario di lavoro tra le date di LuckyTemplates: valori aggregati

Tutto ciò che resta da fare ora è aggregare questi valori. Per farlo, seleziona la colonna Staff Name e vai a Transform. 

Nella finestra pop-up, scegli Hrs sotto la colonna Values ​​perché è ciò che vogliamo aggregare. Quindi, seleziona Sum sotto Aggregate Value Function . Quindi fare clic su OK.

Il nostro output finale sarà simile a questo. 

Creazione di una tabella di date in LuckyTemplates Il modo più rapido possibile
Confronto temporale per tabelle di date non standard in LuckyTemplates
Valore DateTime: come eliminare i secondi

Conclusione

In questo blog, hai visto un approccio alla Start Stop Challenge di Access Analytic. Hai imparato a calcolare il numero totale di ore lavorate da ciascun membro del personale in un mese e come gestire eventuali errori di battitura nei tuoi dati. 

Con questa tecnica e LuckyTemplates, l'orario di lavoro tra le date può essere facilmente calcolato nella propria organizzazione o come pratica per approfondire le proprie conoscenze e competenze di LuckyTemplates.