Calendario perpetuo delle festività – POTW #12 (Soluzione Power Query)

In questo post del blog, esploreremo la mia soluzione al . Per questa sfida, abbiamo rivisitato la prima sfida Problem of the Week di LuckyTemplates, in cui abbiamo creato un calendario perpetuo delle festività da un mix di date specifiche e relative. Ma questa volta utilizzeremo solo Power Query . Puoi guardare il video completo di questo tutorial in fondo a questo blog.

Se sei bloccato sulla scomposizione del problema stesso, puoi rivisitare la per vedere se ci sono elementi in qualcuno degli approcci che possono aiutarti con questa sfida.

Durante la lettura degli invii, ho visto un mix di persone sfruttare l'interfaccia utente e scrivere funzioni personalizzate per creare un calendario perpetuo. È stato divertente analizzare ogni voce e, se vuoi essere ispirato, ti incoraggio a fare lo stesso.

Quando stavo lavorando io stesso a questo scenario, ho finito per creare più soluzioni. Quello con cui sono andato meglio illustra come non solo puoi scomporre un problema in pezzi più piccoli , ma anche renderli visibili . Questo è molto utile se incontri un errore o risultati imprevisti.

Sommario

• Il punto di partenza per la creazione di un calendario perpetuo
• Recupero degli anni dalla tabella delle date
• Ottenere il numero dalla colonna del giorno
• Richiamo della funzione personalizzata per il calendario perpetuo delle festività
• Creazione di un elenco di date
• Applicazione della logica condizionale
• Revisione del codice M per il calendario perpetuo delle festività
• Conclusione

Il punto di partenza per la creazione di un calendario perpetuo

Questo tavolo festivo grezzo è il nostro punto di partenza. Per mettere insieme una data, abbiamo bisogno di un anno, un mese e un giorno per date specifiche. Manca solo l'anno.

Ma per le date relative, dobbiamo fare un po' più di lavoro. Per cominciare, dobbiamo dividere la colonna del giorno sia nel numero che nel giorno della settimana. Rivediamo questi passaggi.

Recupero degli anni dalla tabella delle date

Per rendere dinamica questa soluzione, recupererò gli anni dalla tabella delle date . Nella barra della formula, puoi vedere che ho usato una proiezione per ottenere una tabella a colonna singola dalla mia tabella delle date. Questo, ovviamente, conterrà duplicati. Per rimuoverli, ho aggiunto Table.Distinct .

Quando faccio clic di lato nello spazio bianco accanto a una di queste tabelle, vedrai anche un elenco con gli anni distinti presenti nella mia query della tabella delle date.

Ci è stato assegnato il nome di un mese , ma in realtà avremo bisogno di un numero di mese . Ora, quando concateniamo un anno al nome di un mese, possiamo usare la funzione Date.From per restituire il primo di quel mese come valore di data.

Aggiungendo Date.Month , ci rimane solo il numero del mese per quella data specifica. Per gennaio restituisce 1. Per febbraio restituisce 2 e così via.

Ottenere il numero dalla colonna del giorno

Per ottenere il numero dalla colonna del giorno, abbiamo deciso di non dividere la colonna ed estrarre invece il valore. Come puoi vedere nella barra della formula, abbiamo utilizzato Text.BeforeDelimiter e utilizzato quello spazio per identificare la parte destra e sinistra di quella stringa di testo.

Se non c'è spazio, vogliamo restituire ciò che rimane nella colonna del giorno. Quindi, per il primo record, restituisce 1. Per il secondo record che contiene quello spazio, restituisce la prima parte della stringa di testo, la parola Third .

Useremo un approccio simile per ottenere il nome della data, ma utilizzeremo la funzione List.PositionOf . Questo restituisce l'offset in base zero del valore nell'elenco passato come primo argomento e restituisce -1 se il valore non appare in quell'elenco.

Per il primo record passa il valore 1, che non è presente nella nostra lista. Questo è il motivo per cui restituisce il valore di -1. Per il secondo record, restituisce il valore Monday .

Lo passa alla nostra funzione e restituisce 1 perché è il secondo elemento della nostra lista. Successivamente, sostituiremo tutti i valori -1 con un null .

A questo punto, abbiamo creato tutti gli elementi costitutivi di cui abbiamo bisogno per richiamare la funzione personalizzata.

Richiamo della funzione personalizzata per il calendario perpetuo delle festività

Nella barra della formula, possiamo vedere che la funzione accetta quattro argomenti: un anno , un mese , un numero e un giorno della settimana .

Ma come determina quel valore di data? Useremo di nuovo List.PositionOf per ottenere un valore numerico per il numero. Abbiamo un elenco contenente il primo, il secondo, il terzo e il quarto. Quando passiamo il numero a List.PositionOf , restituisce l'offset in base zero.

Di nuovo, se non è presente un valore, restituisce -1. Quindi, per il primo record, stiamo passando il valore 1. 1 non è presente in questo elenco, motivo per cui restituisce il valore -1. Per il secondo record, stiamo passando il valore Third, che è il terzo elemento nel nostro elenco. Questo restituisce il valore di 2.

Creazione di un elenco di date

Creiamo ora un elenco di date. Determinare la data di inizio per quel mese, determinare il numero di giorni in quel mese e utilizzare List.Dates per creare un elenco contenente tutti i giorni in quel mese.

Successivamente, utilizzeremo List.Select per conservare solo le date che si trovano in un giorno della settimana specifico.

Quando facciamo clic sul lato accanto a uno di questi elenchi, possiamo vedere che contiene solo questi quattro elementi.

Applicazione della logica condizionale

Infine, applicheremo la logica condizionale per costruire o recuperare una data. Se il giorno della settimana è null , a indicare che abbiamo una data specifica, possiamo usare l'intrinseco #date per costruire un valore di data.

Se il numero è uguale a last, possiamo utilizzare la funzione List.Reverse per invertire l'ordine nell'elenco ed estrarre il primo valore.

Se vogliamo cambiare questo ordine crescente in decrescente, possiamo sempre estrarre l'ultima data da quell'intervallo di date. Non importa se abbiamo quattro o cinque elementi nell'elenco. Invertendo l'ordine ed estraendo il primo articolo, abbiamo sempre l'ultima data.

Per tutti gli altri casi, utilizziamo List.Skip .

Possiamo estrarre il valore in base a questa posizione nell'elenco. Per il giorno del Ringraziamento, il numero è il quarto e il quarto restituisce un 3. Ciò salterà i primi tre valori nell'elenco e restituirà sempre il quarto elemento.

Mettiamo tutto insieme e andiamo alla query di soluzione. Per il passaggio di origine, chiamiamo la tabella delle date delle festività RAW. Successivamente, aggiungeremo una colonna che restituisce un elenco con le date.

Revisione del codice M per il calendario perpetuo delle festività

Controlliamo il codice M per la colonna. Per ogni riga della tabella, abbiamo nidificato una tabella contenente gli anni distinti dalla tabella delle date. Quindi abbiamo trasformato i valori in quella colonna dell'anno chiamando la funzione personalizzata.

Naturalmente, per poter invocare la funzione personalizzata, dobbiamo prima eseguire alcune impostazioni. Dobbiamo ottenere il numero del mese , il valore numerico e, naturalmente, il giorno della settimana . Quindi da quella tabella nidificata, abbiamo mantenuto l'elenco con le date.

Quando facciamo clic di lato nello spazio bianco, possiamo vedere un'anteprima di quell'elenco in basso.

Il passaggio successivo consiste nell'espandere tale elenco e aggiungere CelebratedOnDate .

Questa è la logica che abbiamo usato in questo calcolo: se la data è di sabato, sottraiamo un giorno in modo da arrivare a venerdì. Se è domenica, aggiungeremo un giorno per atterrare su lunedì. Se non è un sabato o una domenica, allora vogliamo un null.

Infine, l'ultimo passaggio consiste nel modificare i tipi.

Conclusione

Solo un rapido promemoria che è una serie in corso che si svolge ogni 1° e 3° mercoledì sul forum LuckyTemplates. Il problema pubblicato la prima settimana richiede una soluzione basata su DAX, mentre il problema della terza settimana richiede una soluzione basata su Power Query.

Se ti sono piaciuti i contenuti trattati in questo POTW, iscriviti al canale TV LuckyTemplates per saperne di più. Abbiamo un'enorme quantità di contenuti che escono continuamente da me e da una serie di creatori di contenuti, tutti dedicati a migliorare il modo in cui utilizzi LuckyTemplates e Power Platform.

Melissa