Conversione di un codice YYWWD in un valore di data in LuckyTemplates

In questo blog, parleremo di come convertire un codice YYWWD in un valore data . Questo tutorial era basato su una risposta a una domanda di un membro  . Puoi guardare il video completo di questo tutorial in fondo a questo blog.

Il membro ha chiesto un modo per convertire il suo campo Data di consegna confermata , che è nel formato anno, settimana e giorno della settimana, e trasformarlo in un campo data.

Nel suo esempio, il membro spiega che il codice 20145 dovrebbe essere convertito in 2020-04-03. Quindi da questo possiamo concludere che le prime due cifre corrispondono all'anno, le due cifre successive corrispondono al numero della settimana, mentre l'ultima cifra corrisponde al giorno della settimana.

Ora che abbiamo già un'idea su come affrontare la query, iniziamo.

Sommario

• Rompere il codice a parte per un valore di data corretto
• Colonna dell'anno
• Colonna della settimana
• Colonna Giorno
• Ricerca a 3 vie per ottenere un valore di data
• Identificare le coppie
• Ottenere il valore della data corretto
• Sottrazione dalla colonna del giorno
• Conclusione

Rompere il codice a parte per un valore di data corretto

Per semplificare il nostro lavoro, carichiamo i nostri dati nell'editor di Power Query .

La prima cosa che ho notato quando ho caricato i dati in Power Query è che ha immediatamente effettuato un passaggio di modifica del tipo, come mostrato di seguito.

Poiché non ne abbiamo bisogno, possiamo rimuovere questo passaggio facendo clic sul pulsante X accanto ad esso.

Ora, iniziamo a suddividere il codice in colonne separate per l'anno, la settimana e il giorno della settimana.

Colonna dell'anno

Come accennato in precedenza, le prime due cifre del codice corrispondono all'anno del valore della data.

Per aggiungere una colonna separata per l'anno, dobbiamo assicurarci che la colonna Data di consegna confermata sia selezionata. Per fare ciò, vai alla scheda Aggiungi colonna , fai clic su Estrai , quindi seleziona Primi caratteri .

Poiché abbiamo bisogno solo delle prime due cifre per la colonna dell'anno, inserisci "2" nella scheda Conteggio e fai clic su OK.

Ora modifichiamo la nostra nuova colonna modificando la barra della formula. Quindi, per cambiare il nome della colonna, cambia i primi caratteri nella barra della formula in Anno. Per trasformarlo nel suo corretto valore dell'anno, aggiungiamo 2000 a ciascuno dei nostri valori.

Si noti che dopo aver modificato la nostra formula, riceveremo un errore a causa di una mancata corrispondenza del tipo.

Il messaggio di errore dice che non possiamo applicare l'operatore + ai valori del numero e del tipo di testo.

Per risolvere questo problema, dobbiamo modificare la nostra formula in modo che il valore del testo venga convertito in un numero. Useremo la funzione Number.from per fare ciò e racchiuderemo le parti successive tra parentesi. Quindi, modifica il tipo restituito da testo a numero.

Colonna della settimana

Estraiamo ora dal codice il numero della settimana.

Ancora una volta, assicurati che la colonna Data di consegna confermata sia selezionata, vai alla scheda Aggiungi colonna, fai clic su Estrai, quindi seleziona Intervallo.

Per ottenere la terza e la quarta cifra del nostro codice, dobbiamo iniziare a contare da 0 poiché Power Query è in base zero. Ciò significa che per ottenere la terza cifra del nostro codice, il nostro range dovrebbe partire da 2.

E poiché abbiamo bisogno solo di 2 cifre dal codice per il numero della settimana, inseriremo 2 nel campo Numero di caratteri.

Ancora una volta, la stessa logica si applica qui. Per trasformare l'etichetta della colonna nel suo nome corretto, modifica Intervallo di testo in Settimana nella barra della formula.

Inoltre, aggiungi la funzione Number.from nella nostra formula, racchiudi le parti successive tra parentesi e modifica il tipo restituito in un numero.

Colonna Giorno

Per la colonna del giorno, abbiamo solo bisogno dell'ultima cifra del codice. Per estrarlo, seleziona la colonna Data di consegna confermata , vai alla scheda Aggiungi colonna, seleziona Estrai, quindi fai clic su Ultimi caratteri .

Inserisci 1 perché è rimasta solo una cifra del codice che dobbiamo estrarre.

Come abbiamo fatto in precedenza, nella barra della formula, cambia Last Characters in Day in modo che la nostra nuova colonna abbia il suo nome proprio.

Ancora una volta, aggiungi la funzione Number.from , racchiudi le seguenti parti della formula tra parentesi e modifica il tipo restituito in number.

Ricerca a 3 vie per ottenere un valore di data

Ora che abbiamo tutti i componenti per recuperare una singola data dalla nostra tabella del calendario, l'unica cosa che resta da fare è eseguire una ricerca a 3 vie .

Per farlo, vai alla scheda Home e seleziona Unisci query.

Dal momento che ci uniremo con la nostra tabella Date, fai clic sulla scheda a discesa e seleziona Date.

Identificare le coppie

Ora, identificheremo le coppie che appartengono insieme.

Dalla tabella superiore, seleziona la colonna dell'anno, quindi abbinala alla colonna dell'anno della tabella sottostante.

Per selezionare un'altra coppia, premi il tasto Ctrl sulla tastiera, seleziona la colonna della settimana dalla tabella superiore e abbinala alla colonna del numero della settimana della tabella sottostante.

Infine, tenendo premuto il tasto Ctrl, seleziona la colonna del giorno dalla tabella superiore e abbinala alla colonna DayInWeek della tabella sottostante.

Per assicurarti che le colonne siano accoppiate correttamente, controlla se il numero di indice corrisponde tra loro.

Come mostrato di seguito, le colonne Anno dovrebbero avere un Indice 1, la colonna Settimane dovrebbe avere un Indice 2 e la colonna Giorni dovrebbe avere un Indice 3.

Ottenere il valore della data corretto

Poiché abbiamo bisogno solo della colonna delle date, deseleziona tutte le altre colonne e lascia selezionata la colonna delle date come mostrato di seguito.

Come puoi vedere, il primo valore nella nostra nuova colonna delle date è 4-4-2020. Ricordiamo però che nel forum il codice 20145 va convertito in 3-4-2020.

Questo perché se esaminiamo la nostra tabella delle date, possiamo vedere che nella colonna DayInWeek il conteggio parte da 0 a 6 e non da 1 a 7.

Sottrazione dalla colonna del giorno

Per risolvere questo problema, sottraiamo 1 da tutti i valori nella colonna Giorno.

Dalla sezione Passaggi applicati, fai clic sul passaggio in cui abbiamo aggiunto la colonna del giorno. Questo era il passaggio degli ultimi caratteri inseriti .

Nella barra della formula, sottrai 1 subito dopo le parentesi.

Dopo averlo fatto, possiamo vedere che abbiamo già i valori corretti nella nostra colonna della data.

Infine, l'ultima cosa che dobbiamo fare è ripulire e rimuovere le colonne che non ci servono più.

Per farlo, seleziona Scegli colonne, deseleziona tutto il resto e lascia selezionate la colonna Data di consegna confermata e la colonna Data.

E abbiamo finito! Il risultato del nostro lavoro dovrebbe assomigliare a questo.

Mostra i risultati fino alla data corrente o a una data specifica in LuckyTemplates
Prevenzione della proiezione dei risultati dall'inizio dell'anno
Filtraggio dei dati in base a anni e trimestri fiscali personalizzati Utilizzo di colonne calcolate in LuckyTemplates

Conclusione

In questo blog, abbiamo convertito un codice che è in formato anno, settimana e giorno della settimana in un valore di data appropriato. Abbiamo scomposto il codice e ne abbiamo creato 3 colonne. Utilizzando una ricerca a 3 vie, abbiamo unito queste colonne insieme per ottenere un unico valore di data.

La tecnica che abbiamo utilizzato per affrontare il problema nel forum di supporto può essere utilizzata in altri scenari. Quindi assicurati di capirli bene.

Ti auguro il meglio,

Melissa