Trasformazione del layout del tipo di calendario in formato tabulare in LuckyTemplates tramite leditor di query

Analizzare i dati che abbiamo può essere difficile se non è organizzato correttamente. In questo tutorial, spiegherò come trasformare i dati ripetuti verticali e orizzontali in un formato tabulare. Lo facciamo in modo da poterlo analizzare più facilmente utilizzando LuckyTemplates. Puoi guardare il video completo di questo tutorial in fondo a questo blog.

Questo tutorial è stato creato per rispondere alla domanda di un  membro  . Il membro ha chiesto un modo per rimodellare i suoi dati da un layout di tipo calendario in un formato tabulare.

Prima di iniziare, esaminiamo prima i dati che abbiamo.

Qui abbiamo un layout di dati in cui i giorni sono impostati orizzontalmente e le settimane sono impostate verticalmente. Questo tipo di disposizione è familiare alla maggior parte di noi poiché questo è il modo in cui possiamo facilmente comprendere i dati.

Tuttavia, questo tipo di layout è più difficile da analizzare poiché ci sono molte colonne e righe da prendere in considerazione.

Detto questo, trasformiamo i dati in un vero e proprio formato di tabella dove finiamo con una colonna per le date, una colonna per i movimenti e un'altra colonna per i nomi.

Sommario

• Pulizia dei dati per un corretto formato tabulare
• Rompere i tavoli
• Identificazione dell'elenco per le nuove tabelle per un corretto formato tabulare
• Determinare quali valori appartengono alla tabella
• Raggruppamento dei dati per un corretto formato tabulare
• Aggiunta di tabelle separate
• Conclusione

Pulizia dei dati per un corretto formato tabulare

Per semplificare le cose, trasformiamo i nostri dati all'interno dell'editor di Power Query . Ma prima di farlo, assicuriamoci che la barra della formula sia visibile sullo schermo. In caso contrario, vai alla scheda Visualizza, quindi seleziona la casella accanto alla barra della formula.

Il primo passo è rimuovere i valori non necessari nei nostri dati. Rimuoviamo la prima colonna e le prime due righe dei nostri dati poiché non ne abbiamo davvero bisogno. Per farlo, fai clic sulla scheda Rimuovi righe, fai clic su Rimuovi righe superiori, inserisci 2, quindi premi OK.

E ora per rimuovere la prima colonna, vai alla scheda Scegli colonne, quindi deseleziona la colonna 1 deselezionando la casella accanto ad essa.

Rimuoviamo anche l'indice delle righe da 14 a 16 poiché sono completamente riempite solo con valori nulli. Di nuovo, vai alla scheda Rimuovi righe , quindi fai clic su Rimuovi righe vuote .

Un altro problema che abbiamo è che i valori della data saltano ogni volta una colonna. Le date sono sempre seguite da un valore nullo come evidenziato di seguito. Questo accade non solo nella riga superiore ma anche nelle righe inferiori.

Per colmare queste lacune, trasponiamo prima la nostra tabella di dati andando alla  scheda Trasforma , quindi facendo clic sul pulsante Trasponi.

Il tavolo ora dovrebbe apparire come se fosse stato capovolto su un lato dopo aver eseguito l'azione.

Successivamente, seleziona tutte le colonne che contengono le date, fai clic con il pulsante destro del mouse sull'intestazione della colonna, quindi seleziona Riempi in basso.

Questo dovrebbe duplicare le date e colmare le lacune nelle colonne selezionate.

Rompere i tavoli

Guardando i dati che ora abbiamo, vediamo che ci sono quattro tabelle adiacenti l'una all'altra. Le colonne da 1 a 7 formano una tabella, le colonne da 8 a 13 formano un'altra tabella e così via.

Per accodarli in un'unica tabella, è necessario eseguire un'operazione di unpivot di base per trasformarla in un formato tabulare appropriato. Ma poiché i tavoli sono adiacenti l'uno all'altro, non possiamo semplicemente farlo. Dobbiamo rompere questo tavolo e ricucirlo insieme in tavoli più piccoli.

Per fare ciò, creeremo la prima formula per questo tutorial. Facciamo clic sul pulsante fx quindi aggiungiamo la formula che useremo. Useremo l' operazione Table.ToColumns . Questo crea un elenco di elenchi nidificati di valori di colonna da una tabella. Ogni colonna verrà trasformata in un elenco.

Ecco come ho impostato la formula.

La formula crea un elenco delle colonne che abbiamo precedentemente. Il primo valore dell'elenco contiene la colonna 1, il secondo valore contiene la colonna 2 e così via.

Identificazione dell'elenco per le nuove tabelle per un corretto formato tabulare

Ora, dovremmo identificare quali valori nell'elenco contengono valori di data. Una volta che sappiamo dove si trovano queste date, possiamo determinare dove inizia o finisce ciascuna delle nostre tabelle separate.

Per farlo, riconvertiamo prima l'elenco in una tabella facendo clic sul pulsante In tabella nell'angolo in alto a sinistra.

Quindi aggiungi una colonna di indice accanto ad essa.

La prossima cosa che faremo è aggiungere un'altra colonna accanto alla colonna dell'indice. Questa colonna dovrebbe aiutarci a determinare quali valori su Column1 contengono i valori di data. Chiamiamo questa colonna Index2.

Per fare ciò, incorporeremo una formula in Index2, che restituisce il valore di indice di Column1 se contiene una data.

Utilizzeremo la e quindi faremo riferimento a Column1. Le parentesi fungeranno da operatore; aggiungendo 0 tra queste parentesi significa che otteniamo il primo valore dalla lista; quindi determiniamo se si tratta di una data. Se lo è, voglio che la formula restituisca il numero di indice; se non lo è, non voglio che restituisca alcun valore.

Poiché il primo valore in Column1 contiene una data, il primo valore in Index2 dovrebbe contenere il suo numero di indice 0. Inoltre, poiché il secondo valore in Column1 non contiene una data, il secondo valore in Index2 dovrebbe contenere solo un valore null.

Ecco come ho impostato la formula per Index2. Assicurati solo che non ci siano errori di sintassi nella tua formula.

Dopo averlo fatto, la nostra colonna Index2 dovrebbe ora avere l'indice dei valori in Column1 che contiene la data. Includerà anche alcuni valori null come risultato della formula che abbiamo impostato.

Determinare quali valori appartengono alla tabella

Ora, questi valori di indice all'interno di Index2 indicano l'inizio e la fine delle tabelle separate che abbiamo. Cioè, 0 segna l'inizio della prima tabella, 7 segna la fine della prima tabella e l'inizio della seconda tabella e così via.

Per aiutarci a identificare quali valori in Column1 appartengono alla stessa tabella, applichiamo un'azione di riempimento nella nostra nuova colonna.

Dopo aver eseguito l'azione, i valori null dovrebbero ora essere sostituiti da zero, sette, tredici e venti. Questi numeri indicano che appartengono alla stessa tabella. Cioè, gli zeri formano una tabella, i sette formano un'altra tabella e così via.

Raggruppamento dei dati per un corretto formato tabulare

Poiché sappiamo già quali valori in Column1 appartengono alla stessa tabella, ora raggruppiamoli insieme. Per fare ciò, utilizzeremo l' operazione Group By.

In questo modo, ora finiamo con solo 4 valori nella nostra colonna Index2. Si noti che un'altra colonna denominata Count si trova accanto alla nostra colonna Index2. Questa colonna conta il numero di righe in ciascuno dei nostri valori nella colonna Index2.

Tuttavia, questo non è proprio quello che vogliamo fare. Quello che dobbiamo fare è formare una nuova tabella usando le colonne che abbiamo.

Per fare ciò, utilizzeremo l' operazione Table.FromColumns , faremo riferimento alla colonna che contiene i nostri valori di elenco che è Column1, quindi modificheremo il tipo restituito in una tabella.

È importante assicurarsi di aver fatto riferimento alla colonna corretta nella nostra formula. Controlla se Column1 contiene davvero i valori degli elenchi.

Questo unisce tutti i valori in Column1 con un valore Index2 pari a 0 in una tabella, quelli con il valore Index2 pari a 7 in un'altra tabella e così via.

Abbiamo verificato ogni record con valore 0, quindi recuperato il valore dell'elenco da Column1. Infine, abbiamo ricucito queste liste per formare un'unica tabella utilizzando questo codice M.

Abbiamo preso le prime 7 colonne e le abbiamo unite in un'unica tabella, abbiamo afferrato le colonne successive, le abbiamo unite a un'altra tabella e così via.

Aggiunta di tabelle separate

Ora che li abbiamo già raggruppati insieme, proviamo ad accodare tutte le tabelle aggiungendo un altro passaggio. Di nuovo, premiamo il pulsante fx per aggiungere una nuova formula.

L' operazione Table.Combine unisce un elenco di tabelle e quindi ne crea una singola. Qui faremo riferimento alla colonna che contiene il nostro elenco di tabelle, ovvero la colonna Conteggio.

Ora abbiamo una tabella in cui tutte le tabelle nidificate vengono accodate in un'unica grande tabella.

Dopo aver aggiunto tutte le tabelle, ora possiamo eseguire gli ultimi passaggi per trasformare i nostri dati in un formato tabulare appropriato. Seleziona le prime due colonne della tabella, quindi fai clic sul pulsante Unpivot Other Columns nella scheda Transform.

Successivamente, rimuovi la colonna degli attributi poiché non ne abbiamo davvero bisogno. Vai a Scegli colonne , quindi deseleziona la colonna degli attributi.

Quindi, infine, imposta i tipi corretti di tutte le colonne ed etichettale correttamente.

Fare doppio clic sull'intestazione di ogni colonna per modificarne il nome di conseguenza.

Per la prima colonna, cambia il suo tipo in data poiché contiene i nostri valori di data.

Quindi, seleziona la seconda e la terza colonna e imposta i loro tipi su testo.

Tutto fatto! Ecco come dovrebbe apparire il nostro formato tabulare corretto.

Conclusione

In questo tutorial, abbiamo trasformato alcuni dati, presentati in un layout di tipo calendario, in un formato tabulare appropriato. Per prima cosa abbiamo ripulito i nostri dati, li abbiamo suddivisi in tabelle più piccole, raggruppato le tabelle che appartengono insieme e alla fine le abbiamo accodate tutte.

Questo tutorial ti offre molte conoscenze su come modellare correttamente i tuoi dati per un'analisi più semplice.

Comprendere i concetti che sono stati usati qui è molto importante poiché possono essere applicati anche in molti altri modi.

Se i concetti non ti sono ancora chiari, controllare i link sottostanti potrebbe esserti d'aiuto.

Divertiti a lavorare su questo!

Melissa