Creazione di valori per ogni data utilizzando la formula di Power Query

In questo tutorial imparerai come creare valori per ogni giorno utilizzando la formula di Power Query in LuckyTemplates.

La creazione di valori per le date è una tecnica importante da apprendere in LuckyTemplates. Può darti i dati che vuoi vedere durante i giorni nel tuo modello. Questa tecnica copre anche gli spazi tra le colonne.

Questo è un ottimo strumento da utilizzare quando le tabelle delle date mancano di informazioni.

Ecco una guida passo-passo su come farlo: Puoi guardare il video completo di questo tutorial in fondo a questo blog.

Sommario

• Imposta valori aggiuntivi
• Crea la condizione di filtro per gli ID
• Aggiungi la condizione del filtro per le date
• Controlla e ordina la colonna della data
• Utilizzare la funzione Table.FirstN
• Ottieni la differenza di giorno tra 2 valori di data
• Determina un passo di peso
• Riempi lo spazio
• Ottieni la proiezione del peso delle date
• Conclusione

Imposta valori aggiuntivi

Per i set di dati contenenti ID, date e pesi, è necessario colmare il divario tra tali misurazioni. Questo può essere fatto creando un intervallo di date per distribuire la differenza nel corso dei giorni.

Per farlo, vai a Power Query per vedere la tua tabella.

Devi caricare una copia dei tuoi dati in memoria. Affinché ciò accada, è necessario utilizzare la misura Table.Buffer .

Se fai clic su TableBuffer, vedrai cosa fa la misura.

Quindi, torna al tuo set di dati e fai clic su fx nella barra della formula. È necessario aggiungere Table.Buffer nella misura.

Quindi, rinomina questo passaggio in BufferedTable .

Successivamente, aggiungi una colonna personalizzata.

Crea la condizione di filtro per gli ID

In questa formula di Power Query, modificare il passaggio a cui si fa riferimento da BufferedTable a RenameColumns perché BufferedTable deve essere controllato prima.

Per controllare BufferedTable, utilizzare Table.SelectRows che restituisce una tabella che corrisponde alla condizione selezionata.

Quindi, per ogni espressione nella formula, utilizza Table.SelectRows e quindi utilizza BufferedTable.

Come condizione di filtro, utilizza una funzione di fine personalizzata e inserisci BT per Buffered Table . Controlla la colonna ID se è uguale all'ID del record corrente. Successivamente, accedi al record corrente utilizzando il carattere di sottolineatura come operatore di accesso, quindi fai riferimento alla stessa colonna ID.

Questo ti darà una tabella filtrata che contiene solo gli ID che corrispondono al record corrente. 

Aggiungi la condizione del filtro per le date

È necessario filtrare la colonna della data perché sono necessarie solo le date uguali al record corrente.

Utilizzare queste funzioni per controllare la colonna della data BT:

Questa colonna deve essere maggiore o uguale alla data del record corrente.

Con questo, la tabella conterrà quindi due righe invece di tre:

Controlla e ordina la colonna della data

È necessario verificare se l'ordinamento della colonna della data è corretto utilizzando Table.Sort . La funzione Table.Sort ordina una tabella su una o più colonne a seconda dei criteri desiderati.

La sintassi in questo esempio è piuttosto complicata, quindi devi fare un down-drill su un argomento della tabella.

È necessario fare clic con il pulsante destro del mouse su una tabella e aggiungerla come nuova query.

Facendo clic su questo si ottiene una query personalizzata che contiene quella tabella.

Successivamente, fai clic sulla colonna della data e scegli Ordinamento crescente.

Copia l'intera formula:

Torna alla query principale e incollala nella barra della formula:

Devi rimuovere il riferimento di Table.Sort perché la tabella che deve passare il parametro dovrebbe essere il risultato di Table.SelectRows.

Al termine, aggiungi nuovamente le condizioni di ordinamento. Dopo aver applicato le modifiche, la tua tabella è ora ordinata correttamente per data.

Utilizzare la funzione Table.FirstN

Per questo passaggio, sono necessari solo 2 record invece di 3.

Per risolvere questo problema, torna alla query personalizzata. Fare clic su Mantieni righe e quindi su Mantieni righe superiori.

Immettere 2 nello spazio vuoto per il numero di righe.

Noterai che la funzione Table.FirstN appare nella barra della formula. Table.FirstN restituisce le prime righe della tabella in base al valore di countOrCondition .

Inserisci quella funzione, insieme al numero di righe necessarie nella query principale. Includere la tabella del tipo di funzione alla fine.

Ogni oggetto tabella nel set ora avrà 1 o 2 righe.

Ottieni la differenza di giorno tra 2 valori di data

Il passaggio successivo consiste nell'ottenere il numero di giorni tra i valori di data ed estrarre la data che si trovava nel secondo record.

Inizia aggiungendo una colonna personalizzata. Chiamalo Giorni . Quindi, segui questa formula di Power Query:

La sintassi fa in modo che ci siano 2 record in una tabella. Se c'è solo 1, la differenza di giorno è 0 perché non ha un record successivo. Se ce ne sono 2, puoi ottenere la differenza dai valori della data.

Successivamente, aggiungi il numero di tipo nella formula in questo modo.

Come puoi vedere, ottieni una differenza di 385 giorni dal 14 settembre 2017 al 4 ottobre 2018.

Determina un passo di peso

Successivamente, devi calcolare quale dovrebbe essere il peso per ogni giorno.

Devi aggiungere nuovamente una colonna personalizzata, denominarla WeightStep e copiare questa formula di Power Query:

La sintassi controlla se la tabella ha più di 1 riga, sottrae il peso dal record corrente e divide tale differenza per il numero di giorni.

Ancora una volta, aggiungi il numero di tipo nella formula in modo da poter vedere il peso di ogni giorno.

Riempi lo spazio

Ora che hai il numero di giorni, puoi creare un elenco di date per colmare il vuoto nel set di dati.

Innanzitutto, aggiungi un'altra colonna personalizzata e chiamala DateKey . Quindi, segui questa formula di Power Query:

La formula controlla se c'è una differenza di data. Se c'è, crea un elenco di date tra la data di inizio che è dal record corrente e lo espande per il numero di giorni. Ma se non ci sono differenze, restituisce la data.

Questo è il risultato:

Ai soggetti della tabella che non presentavano differenze veniva restituita la data; i tavoli che lo hanno fatto, hanno un elenco di date.

Se espandi DateKey in nuove righe, puoi vedere i record di tutti i giorni.

Ottieni la proiezione del peso delle date

Dopo aver creato un elenco di date, è necessario ottenere una proiezione del peso per le date.

Innanzitutto, aggiungi una colonna personalizzata e chiamala WeightProjection . Quindi inserisci questa formula di Power Query:

La sintassi ottiene il valore del peso, lo aggiunge al valore DateKey, lo sottrae dal valore della data e lo moltiplica per WeightStep.

Ancora una volta, aggiungi il numero del tipo alla fine della formula.

Ora hai la proiezione del peso per ogni data.

Rimuovi le colonne helper: Custom, Days e WeightStep utilizzando la formula seguente e modifica il formato della colonna DateKey in Date.

Conclusione

In questo tutorial, hai imparato a colmare le lacune nel tuo set di dati creando valori in ogni elenco di date.

Questi valori di data ti aiutano a ottenere informazioni preziose dal tuo modello. Se le tue tabelle mancano di informazioni, puoi utilizzare questa tecnica per renderle più complete.

Potrebbero esserci dati essenziali nella tua tabella che stai tralasciando, quindi massimizza sempre ogni riga e colonna.

Ti auguro il meglio,

Melissa