Tabella delle dimensioni in un file di testo: soluzione Power Query

In questo post del blog, esaminerò il problema n. 2 del Problema della settimana in corso sul , dove abbiamo discusso su come ordinare i dati disordinati in una tabella delle dimensioni pulita. Questa è la seconda iterazione di questa nuova iniziativa che stiamo ospitando a LuckyTemplates. Sono particolarmente appassionato di questa serie perché offre a tutti la possibilità di fare più pratica su base regolare. Puoi guardare il video completo di questo tutorial in fondo a questo blog.

Il primo mercoledì di ogni mese c'è una sfida DAX e il terzo mercoledì c'è una sfida di query di potenza .

Questa è una grande opportunità per esplorare, scoprire e imparare cose nuove su questi linguaggi che devi sfruttare all'interno di LuckyTemplates.

Troverai una categoria sul problema della settimana nel forum di LuckyTemplates.

L'obiettivo principale è tutto sul processo e non tanto sul risultato.

Se riesci a scomporre un problema in parti più piccole, sarai in grado di risolvere qualsiasi problema ti trovi di fronte.

Melissa de Korte

Brian ha parlato in precedenza di una tecnica chiamata debugging delle papere di gomma . Se te lo sei perso, assicurati di dare un'occhiata al suo video. Può aiutarti quando rimani bloccato su queste sfide.

Per prima cosa, esaminiamo il compito a portata di mano. Abbiamo un file di testo disordinato che dobbiamo trasformare in una corretta tabella delle dimensioni.

Ora, quando guardo dati come questi, per la maggior parte, riguarderà la pulizia del testo, la rimozione di caratteri indesiderati, il taglio, l'uso delle maiuscole e così via.

Ma dobbiamo anche recuperare tutte le righe da questo stack di dati e trasformarle in un'unica riga per ogni paese. Lo chiamo stack data perché tutti i nomi dei campi sono ripetuti in una singola colonna per ogni paese.

Ho una certa esperienza in Excel e una delle sue funzionalità più potenti a mio avviso sono le tabelle pivot .

Sommario

• Tabelle pivot in Excel
• Utilizzo di Power Query per creare una tabella delle dimensioni
• Funzione di pulizia del testo personalizzata
• Pivoting dei dati
• Conclusione

Tabelle pivot in Excel

Le tabelle pivot mi consentono di visualizzare i dati segmento per segmento. A seconda di cosa posizioni nella sezione di riga, una tabella pivot condensa ogni occorrenza di quel segmento in una singola riga.

Puoi quindi suddividerlo ulteriormente trascinando i campi nella sezione della colonna.

Ho creato un esempio di come appare. Qui abbiamo praticamente gli stessi dati che avevamo nel file di testo.

E nella pagina successiva, ho creato una tabella pivot vuota.

Ora, quello che non ti ho mostrato è che ho tre colonne qui invece delle due che ti ho già mostrato.

Trascinerò il mio segmento sulle righe.

Poiché la colonna 1 aveva i nomi dei campi, lo trascinerò nella sezione della colonna.

La colonna 2 aveva tutti i valori del campo, quindi lo trascinerò nella sezione dei valori.

Possiamo vedere qui che le tabelle pivot non possono gestire stringhe di testo. Li conta, ma mostra che abbiamo un singolo valore per ogni campo.

Ora diamo un'occhiata a quel segmento che ho creato. Quindi tornerò ai dati e scoprirò la mia colonna. Puoi vedere che è solo un numero di indice, che identifica ciascuno dei blocchi separati dei dati che sono ancora impilati uno sopra l'altro.

Quindi per me, la trasformazione chiave sarà un perno dei dati per riportarli in quel formato tabulare.

Nel forum, ho visto altri modi per affrontare questo problema. Esistono altri modi per ottenere un formato tabulare oltre all'utilizzo di un'azione pivot e funzionano altrettanto bene. Se sei interessato a questo, vai al forum e inizia a esplorare.

Utilizzo di Power Query per creare una tabella delle dimensioni

Esaminiamo la query di potenza ed esaminiamo la mia soluzione.

Personalmente, penso che l'interfaccia utente faccia un ottimo lavoro nello scrivere la maggior parte del codice M per noi. Quindi tendo a progettare le mie query utilizzando l'interfaccia utente il più possibile.

Una volta che la query fa ciò di cui avevo bisogno, vado nell'editor avanzato ed esamino il codice M per vedere se posso modificarlo. Vediamo come appare.

Questo è il mio gruppo di base creato utilizzando l'interfaccia utente.

Puoi vedere che ci sono molti passaggi nei passaggi applicati sul lato destro.

Ora questo di per sé non è un problema, ma solo osservando questi passaggi, puoi vedere che ci sono molte trasformazioni che possono essere raggruppate insieme.

Apriamo l'editor avanzato.

Vediamo che questa query ha 31 passaggi.

Ho anche aggiunto alcuni commenti a questa stessa query contenente i 31 passaggi, ma l'ho suddivisa in sezioni.

La prima cosa che ho fatto è stata rimuovere il passaggio del tipo di modifica. Suggerisco di creare una funzione personalizzata per eseguire tutti questi passaggi di trasformazione del testo.

Ci sono molti commenti qui, ma ci sono solo due cose che voglio evidenziare in questo post del blog. La prima è la funzione personalizzata per la pulizia del testo .

Il secondo è il passaggio pivot per trasformare nuovamente questo tipo di dati in un formato tabulare appropriato .

Funzione di pulizia del testo personalizzata

Torniamo alle prime fasi della creazione della mia query, in cui ho eseguito tutti i passaggi raggruppati per ripulire questi testi: la colonna 1 e la colonna unita.

Ho anche aggiunto una colonna personalizzata aggiuntiva. Il suo unico scopo è costruire la mia funzione di pulizia del testo personalizzata . L'ho invocato sulla colonna unita.

In questo modo, non devo scrivere la funzione in una volta sola, ma costruirla gradualmente un passo alla volta, aggiungendo una nuova trasformazione dopo aver rivisto i risultati del passaggio precedente.

Diamo un'occhiata al codice M per la funzione di pulizia del testo.

Come puoi vedere, ho più passaggi. Quando stavo costruendo questa funzione di testo, sono andato avanti e indietro tra la query per guardare i risultati per vedere cosa costruire e cosa correggere dopo.

Con questo risultato, ho eseguito tutte le trasformazioni di cui avevo bisogno. Alcune delle funzioni M che ho usato qui sono state fornite dall'interfaccia utente quando ho creato la mia query iniziale, come Text. Funzione di taglio. Tuttavia, le altre funzioni utilizzate non lo erano.

Se non le conosci, puoi cercare tutte le funzioni M nella guida alle formule M online. Questo è il link dove devi andare.

Troverai una sezione dedicata a power query e funzioni.

Se scorri verso il basso, troverai la sezione sulla funzione di testo e ogni sezione inizia con una panoramica. C'è un elenco di tutte le funzioni di testo all'interno della query di potenza e del linguaggio formula M.

Se stai cercando una trasformazione specifica, puoi cercarla qui.

Pivoting dei dati

La seconda parte che volevo evidenziare durante la creazione di questa tabella delle dimensioni è il pivoting dei dati stessi. Diamo un'occhiata più da vicino anche a questo.

Ho iniziato aggiungendo un file index. Ho aggiornato quell'indice per segmentare correttamente i blocchi di dati. L'ho fatto restituendo il numero di indice per ogni riga in cui aveva il testo paese nella colonna 1, quindi ho inserito quel valore.

Tutto quello che dobbiamo fare è ruotare i dati stessi. Nella scheda di trasformazione troverai la colonna pivot. Con la colonna 1 selezionata, fai clic su Colonna pivot.

Utilizzerà i valori della colonna 1 come nuovo nome della colonna. Vuole anche sapere dove sono i valori per quei nomi di campo. Quelli sono nella nostra colonna unita.

Se Excel è in grado di gestire i valori di testo, anche Power Query può farlo grazie all'impostazione delle opzioni avanzate. Tutto quello che dobbiamo fare è selezionare Non aggregare perché sia ​​in grado di gestire i valori di testo.

Dopo aver fatto clic su OK, possiamo vedere che i nostri dati sono stati ruotati.

Conclusione

L'immagine sopra è la query di risultato finale. Spero ti sia piaciuto il modo in cui abbiamo rimodellato i dati disordinati nel file di testo fornito e li abbiamo trasformati in una tabella delle dimensioni pulita adatta all'analisi.

Se ti è piaciuto questo post del blog, iscriviti al canale LuckyTemplates in modo da non perdere nessun nuovo contenuto.

Spero di vedervi tutti nelle future sfide Problema della settimana.

Melissa