Esplorazione della funzionalità Ottieni e trasforma di Excel

Sintesi

In questa epoca di data lake e database su scala petabyte, è scioccante la frequenza con cui ricevo ancora dati sotto forma di file CSV, testo ed Excel. Mentre l'analisi moderna si concentra sui progressi all'avanguardia negli algoritmi di apprendimento automatico, la fatica quotidiana dell'analisi dei dati è ancora un processo manuale per trovare, compilare e gestire tipi di dati disparati.

Per l'analista finanziario, i dati spesso arrivano come un foglio di calcolo Excel, ma altrettanto spesso si tratta di un dump di dati in un CSV o di una query in un database SQL. A volte, i dati sono disposti in un layout confuso o non hanno tutti i componenti necessari per l'analisi. Il tempo speso a ripulire questi dati è tempo prezioso sprecato per l'analista, ma a volte questo compito è accettato come un male necessario da tollerare.

Cosa fa Get & Transform?

Una soluzione a questo problema comune è in realtà abbastanza accessibile: Excel e Power BI dispongono di un intero set di strumenti di trasformazione dei dati di cui pochi utenti sono a conoscenza, denominati Get & Transform (precedentemente noto come Power Query). L'utilizzo della funzionalità ETL (Estratto, Trasforma e Carica) incorporata consente agli analisti finanziari di collegarsi senza problemi alle proprie origini dati e di ottenere informazioni dettagliate più rapidamente.

Quando raccogliamo i dati per caricarli in Excel o Power BI, di solito dobbiamo eseguire alcune trasformazioni dei dati. Alcuni esempi di manipolazione dei dati includerebbero:

• Rimozione di colonne,
• Filtrando i dati,
• Raggruppando i dati,
• Pivoting/unpivoting dei dati,
• Dividere le stringhe in sottostringhe,
• Estrazione di parole chiave da stringhe,
• Aggiunta di righe da un'altra tabella e
• Unire tabelle a due dimensioni.

Nel diagramma seguente, vediamo che Get & Transform svolge questo noioso ruolo di pre-elaborazione dei dati prima che vengano caricati.

Perché dovresti usare Get & Transform?

Perché vale la pena imparare a usare Get & Transform? Bene, quando guardo a cosa ho usato personalmente questa funzionalità, mi ha offerto un set malleabile di strumenti per:

• Caricamento di un'intera cartella di file di testo in un'unica tabella di dati
• Conversione dei file contabili esportati in un layout digeribile
• Caricamento diretto di milioni di righe di vendita in Power Pivot
• Raggruppamento dei dati giornalieri in risultati mensili gestibili prima di importarli in Excel
• Unione di dati da un'altra tabella unendo su colonne corrispondenti

In genere, quando ricevo nuovi dati, li esplorerò utilizzando Get & Transform prima di caricarli in Power Pivot. Questo mi permette di vedere quali trasformazioni potrebbero essere necessarie ed eseguire rapidamente alcuni pivot e raggruppamenti sui dati per formulare un framework per l'analisi. In molti casi, in questa fase, scoprirò che ho bisogno di più dati o che ci sono problemi con i dati. Utilizzando una piattaforma basata su Excel, posso scorrere rapidamente con la mia origine dati per trovare queste anomalie dei dati.

In definitiva, la decisione di rimanere in Excel o spostare l'analisi dei dati su un'altra piattaforma dipenderà dal pubblico e dalla ripetibilità e distribuzione dell'analisi. Se i miei clienti utilizzano solo Excel, userò quasi sempre Get & Transform per caricare i dati, Power Pivot per eseguire l'analisi ed Excel per produrre tabelle pivot e grafici. Per il cliente, questo sembrerà senza soluzione di continuità poiché è tutto ospitato in Excel.

Tuttavia, se il mio cliente:

1. Vuole utilizzare un altro strumento di visualizzazione,
2. Ha più utenti che aggiorneranno i dati o
3. Necessità di utilizzare modelli di apprendimento automatico,

Quindi userò Get & Transform esclusivamente per l'esplorazione iniziale dei dati, quindi sposterò il lavoro pesante in R.

Come accedere a Ottieni e trasforma in Excel o Power BI

Nelle versioni precedenti di Excel, Power Query era un componente aggiuntivo che poteva essere installato per aiutare con le funzioni ETL. Tuttavia, in Excel 2016 e Power BI, questi strumenti sono più strettamente integrati. In Excel 2016, è possibile accedervi tramite la scheda Dati e quindi la sezione Ottieni e trasforma dati .

In Power BI, la funzionalità esiste nella scheda Home , nella sezione Dati esterni .

In questo articolo, i miei esempi si svolgono in Power BI, ma l'interfaccia è quasi identica a quella di Excel. Indicherò le differenze quando si presentano, quindi il tutorial dovrebbe avere senso per entrambi i tipi di utenti.

1. Caricamento di file CSV

Per aiutare questo tutorial, ho creato alcuni esempi di dati di vendita per un rivenditore immaginario che vende abbigliamento e abbigliamento per esterni. In ciascuno di questi esempi, i dati verranno prodotti in modi diversi per dimostrare metodi realistici di dump dei dati.

Come esempio iniziale, vedremo i dati presentati come un dump di dati di grandi dimensioni in un file CSV. Il fattore complicante è che i dati vengono presentati con più colonne che rappresentano vari negozi. Idealmente vorremmo importare e trasformare i dati in un layout più utilizzabile.

Di seguito è riportato uno screenshot di come appare il CSV non elaborato:

Perché dovremmo voler cambiare questo? Per sfruttare le capacità di relazione possibili in queste applicazioni. Vedremo questo gioco più avanti nella discussione.

Per ora, supponiamo di aver bisogno di vedere i dati come una struttura "più stretta e più alta", piuttosto che una "più ampia e più corta". Il primo passo è caricare il CSV; quindi, inizieremo a "svitare" i dati.

Come puoi vedere, la struttura finale dei dati è più stretta dei dati iniziali e molto più lunga. Un altro punto è che, mentre stiamo facendo clic su diverse azioni, lo strumento sul lato destro sta generando un elenco di passaggi applicati utilizzati per creare la query. È importante capire che questo sta accadendo in background, poiché sarà rivisitato in seguito.

Ottieni e trasforma sembra e si comporta in modo simile per la maggior parte tra Power BI ed Excel. Tuttavia, in Excel, dopo aver fatto clic su Chiudi e carica , è presente un prompt aggiuntivo. Nella figura seguente, possiamo scegliere se caricare i dati in:

1. Una tabella in Excel,
2. Una tabella pivot creata rispetto ai dati,
3. Un grafico pivot creato rispetto ai dati o
4. "Crea solo una connessione".

Inoltre, abbiamo anche la possibilità di aggiungere o meno questi dati al modello di dati . Selezionando questa casella, i dati vengono caricati in una tabella PowerPivot. Se analizzeremo i dati in Power Pivot, ti consiglio di scegliere Crea solo una connessione e quindi di assicurarti che l'opzione Aggiungi questi dati al modello di dati sia selezionata. Se i dati rientrano nel limite di righe di Excel e preferiamo condurre la nostra analisi in Excel, scegli semplicemente Tabella .

Nella prossima clip, vedremo che il motivo per cui abbiamo formattato i dati in modo che siano lunghi e sottili è che siamo in grado di analizzare le vendite non solo per negozio ma anche per regione e stato. Per eseguire questa attività, importeremo una tabella che associa ogni negozio a una regione ea uno stato. Vedremo di seguito che possiamo creare rapidamente report che mostrano le vendite per questi diversi raggruppamenti.

Puoi immaginare come questo tipo di funzionalità per la trasformazione dei dati in Excel, o Power BI, possa essere applicato in modo efficace a tutti i casi in cui disponiamo di raggruppamenti dinamici di dati, ad esempio:

• Raggruppamento dei dati giornalieri in settimane, mesi e trimestri;
• Raggruppare il personale di vendita in dipartimenti e regioni; o
• Mappatura degli SKU ai tipi di prodotto.

Sebbene questo articolo affronti CSV e altri file Excel, Ottieni e trasforma affronta un'ampia gamma di tipi di dati. Una volta creata, una query può essere aggiornata nel tempo man mano che i dati cambiano.

2. Gestione delle stringhe di testo

Per dimostrare la capacità di Get & Transform di manipolare le stringhe, ho creato un altro set di dati che imita un file di testo che mostra le transazioni contabili dalla contabilità generale (GL) di un'azienda.

Hai notato come il numero di conto e il nome appaiono nella stessa stringa? In Power BI, possiamo facilmente analizzare il numero e il nome dell'account in campi separati.

In questo video, puoi vedere che dopo aver diviso la colonna, lo strumento ha indovinato che il nuovo lato sinistro del campo Account dovrebbe essere un numero e crea un passaggio "Tipo modificato1". Poiché alla fine vogliamo questo campo come una stringa, possiamo andare avanti ed eliminare manualmente il passaggio sotto i passaggi applicati.

Successivamente, prendiamo gli stessi dati e creiamo un piano dei conti con mappature alle categorie di conti.

Perché dovremmo eseguire tutti questi passaggi per mappare alcuni numeri di conto? Un vero libro mastro generale può essere costituito da centinaia o addirittura migliaia di conti. Questa query di mappatura rapida, come abbiamo mostrato, verrebbe scalata a quel livello senza lavoro aggiuntivo.

3. Lavorare con diverse origini dati

Ottieni e trasforma supporta molte origini dati diverse. Sebbene non sia un elenco esaustivo, di seguito sono riportati alcuni esempi:

Personalmente, ho provato solo circa la metà delle connessioni nell'elenco sopra. Ciascuno dei connettori che ho usato è stato abbastanza robusto; Sono passato dai dati grezzi alle informazioni dettagliate senza una mole di lavoro gravosa. Altrettanto importante, funge da validatore tra diverse fonti di dati, garantendo che gli output finali abbiano un livello normalizzato di controllo della qualità.

4. Personalizzazione del codice con la lingua M

In background, Get & Transform genera codice ogni volta che si fa clic su un pulsante nello strumento o si effettua una selezione. Di seguito è riportato un esempio di come accedere al codice per la query di mappatura dell'account che abbiamo creato:

Il codice utilizza un linguaggio funzionale denominato M, che viene generato automaticamente per i casi d'uso di base. Tuttavia, per la gestione dei dati più complicata, possiamo modificare e scrivere il nostro codice. Nella maggior parte dei casi, apporterò solo piccole modifiche a questo codice. Nelle trasformazioni più complicate, posso scrivere la maggior parte del codice da zero per mettere in scena tabelle temporanee o per eseguire join più complicati.

I limiti di Get & Transform

Excel tende a raggiungere i suoi limiti quando si tenta di esportare più di un milione di righe. Nei casi in cui ho trasformato milioni di righe con Get & Transform, l'unico modo per spedire righe non raggruppate è tramite noiosi hack o soluzioni alternative. Ho anche scoperto che le query Get & Transform possono essere instabili da distribuire a più utenti, soprattutto se si utilizzano più origini dati e join. In questi casi, userò sempre R per distribuire la disputa sui dati duplicabili. Infine, Excel non è progettato per la modellazione dei dati più avanzata. Puoi eseguire regressioni lineari abbastanza rapidamente, ma oltre a ciò, dovrai utilizzare una piattaforma più rigorosa.

Detto questo, trovo che Excel sia ciò con cui la maggior parte dei miei clienti si sente più a suo agio. Excel è ancora lo strumento più importante nell'arsenale di un analista finanziario. Incorporando la funzionalità Ottieni e trasforma, Excel e Power BI diventano ancora più potenti grazie alla gamma di origini dati che possono accettare.