In fondo: Impara come questa fantastica funzione di Excel e Power BI chiamata Power Query ti aiuterà ad automatizzare il processo di importazione, trasformazione e pulizia dei dati per risparmiare un sacco di tempo nel tuo lavoro.
Livello di competenza: Beginner
Video Tutorial
Scaricare i file di esempio
I file CSV che uso nel video sono disponibili per il download qui sotto. Dovrai estrarre i file dal file zip.
CSV Order Files For Power Query Overview.zip (337.0 KB)
Introduzione a Power Query
In questo tutorial fornisco una spiegazione introduttiva di Power Query. Imparerai perché questo è il mio nuovo strumento preferito di Excel per lavorare con i dati, e come può aiutare ad automatizzare i processi e farti risparmiare tempo!
La Power Query Data Machine
Stavo guardando un programma televisivo su come sono fatte le cose, e stavano spiegando come funzionava una macchina depositatrice in una pasticceria.
Le basi di una macchina depositatrice sono:
- ci aggiungi gli ingredienti.
- E crea magicamente dei pasticcini (biscotti, ciambelle, biscotti) pronti per essere infornati.
Una volta impostati i quadranti, il processo può essere ripetuto più e più volte per ottenere ogni volta pasticcini perfetti. Ti sta venendo fame…? 🙂
Power Query funziona in modo molto simile!
- Aggiungi le tue fonti dati (tabelle Excel, file CSV, tabelle di database, pagine web, ecc.)
- Premi i pulsanti nella finestra Power Query Editor per trasformare i tuoi dati.
- Fai uscire quei dati nel tuo foglio di lavoro o modello di dati (PowerPivot) che è pronto per le tabelle pivot o il reporting.
Power Query è come una macchina perché una volta che hai impostato la tua query, il processo può essere ripetuto con il clic di un pulsante (refresh) ogni volta che i tuoi dati cambiano.
Se hai usato macro per trasformare i tuoi dati, puoi pensare a questo come a un’alternativa molto più facile a VBA che NON richiede codifica.
Task comuni sui dati resi facili
Lavori con dati che sono stati esportati da un sistema di record? Potrebbe essere una contabilità generale, un ERP, un CRM, Salesforce.com, o qualsiasi sistema di reporting che contenga dati.
Se è così, probabilmente passate molto tempo a trasformare o rimodellare i vostri dati per creare ulteriori report, tabelle pivot o grafici.
Queste trasformazioni di dati potrebbero includere compiti come:
- Rimuovere colonne, righe, spazi vuoti
- Convertire i tipi di dati – testo, numeri, date
- Dividere o unire le colonne
- Ordinare & le colonne dei filtri
- Aggiungere colonne calcolate
- Aggregare o riassumere dati
- Trova & sostituisci il testo
- Unpivot di dati da usare per le tabelle pivot
Qualcuno di questi compiti vi suona familiare? Se è così, allora probabilmente suonano anche noiosi, ripetitivi e richiedono tempo. 🙂 Credetemi, ho passato la maggior parte della mia carriera a fare questi compiti e a cercare di trovare modi più veloci per svolgerli.
Fortunatamente, Power Query ha dei pulsanti che automatizzano tutti questi compiti!
Sguardo alla barra multifunzione di Power Query
A partire da Excel 2016 per Windows, Power Query è stato completamente integrato in Excel. Ora si trova nella scheda Dati della barra multifunzione nel gruppo Ottieni & trasformazione.
In Excel 2010 e 2013 per Windows, Power Query è un add-in gratuito. Una volta installato, la scheda Power Query sarà visibile nella barra multifunzione di Excel.
Si utilizzano i pulsanti nella scheda Dati o Power Query per ottenere i dati di origine. Anche in questo caso, i dati potrebbero essere memorizzati in file Excel, file csv, Access, database SQL server, SharePoint, Salesforce.com, Dynamics CRM, Facebook, Wikipedia, siti web, e altro ancora.
Una volta specificata la provenienza dei dati, si utilizza la finestra Power Query Editor per effettuare trasformazioni ai dati.
I pulsanti della finestra Power Query Editor ti permettono di trasformare i dati.
Pensate ad alcune delle attività che fate ripetutamente mentre sfogliate i pulsanti nell’immagine qui sopra. Ogni volta che premi un pulsante le tue azioni (passi) vengono registrate, e puoi riapplicare rapidamente i passi quando ricevi nuovi dati aggiornando la query.
Dopo aver completato i passi, puoi inviare i dati a una tabella nella tua cartella di lavoro Excel cliccando il pulsante Chiudi & Carica.
È anche possibile modificare le query esistenti e aggiornare le tabelle di output con le modifiche o i dati aggiornati.
Esempi di trasformazione dei dati
Qui ci sono alcuni esempi di ciò che Power Query può fare con i vostri dati.
Dati Unpivot per tabelle Pivot
La mia caratteristica preferita di Power Query è la sua capacità di Unpivot dei dati. Questa è una tecnica usata per preparare i tuoi dati per la fonte di una tabella pivot. Questo viene anche chiamato normalizzare i dati per metterli in un formato tabulare.
I dati potrebbero iniziare con un aspetto simile al seguente.
E si desidera che il risultato finale sia simile a questo.
Power Query può fare questo con un clic di pochi pulsanti, e preparare i vostri dati per l’uso in una tabella pivot.
Qui c’è un articolo e un video che spiegano esattamente come dispiegare i dati con Power Query.
Guarda il mio articolo su come strutturare i dati di origine per una tabella pivot se non hai familiarità con il motivo per cui i tuoi dati devono avere questo aspetto per una tabella pivot.
Appendi (combina) le tabelle con Power Query
La funzione Append di Power Query ti permette di combinare più tabelle (impilarle verticalmente) per creare una grande tabella. Può fare questo con più tabelle in un file, o può estrarre i dati da un mucchio di file/fonti diverse.
Diciamo che hai una cartella che contiene file CSV o Excel con i dati dei report per ogni mese. Gettate tutti questi file nella macchina di Power Query, e sputerà fuori una bella tabella che potrete poi usare per creare tabelle pivot e grafici.
Se i dati di questi report devono anche essere trasformati (rimuovere righe, dividere colonne, unpivot, ecc.), allora Power Query può gestirli nello stesso processo.
Una volta impostato, tutto quello che devi fare è premere il pulsante di aggiornamento ogni mese quando un nuovo file viene aggiunto alla cartella e le righe verranno aggiunte alla tua tabella di output.
Come è fantastico! 🙂
Tabelle unite – Un’alternativa VLOOKUP
Power Query ha la capacità di unire o unire tabelle. Questo può essere usato come alternativa alle formule VLOOKUP o INDEX/MATCH.
Diciamo che avete questa tabella dati di record di vendite, e state usando un VLOOKUP per portare informazioni sul prodotto in base al nome del prodotto venduto. Le informazioni sul tuo gruppo di prodotti si trovano in un’altra tabella su un altro foglio o cartella di lavoro.
Utilizzare le formule VLOOKUP è fantastico, ma spesso può significare aggiungere migliaia di formule alla tua cartella di lavoro. Il che aumenta la dimensione del file e il tempo di calcolo.
Power Query rende molto veloce e facile unire due tabelle con pochi clic. Fondamentalmente usa i join SQL, quindi puoi anche fare fusioni più avanzate come inner, outer, left, right, full e anti joins.
Crea funzioni personalizzate
Power Query è stato progettato in modo che NON devi sapere come codificare per usarlo. È molto facile da usare perché puoi semplicemente fare clic sui pulsanti e applicare i filtri come faresti normalmente in Excel.
Tuttavia, Power Query può essere programmato per creare funzioni personalizzate. Questo vi dà un potenziale apparentemente illimitato per trasformare i vostri dati in qualsiasi modo possibile.
Si basa sul linguaggio M, e la maggior parte delle funzioni sono molto simili alla scrittura di una formula in Excel. Questo lo rende anche più facile da usare e da imparare il codice.
Questo nuovo linguaggio e il set di funzioni significa che c’è molto da imparare, ma io considero questa la parte divertente e stimolante. Inoltre, i datori di lavoro del futuro cercheranno sicuramente impiegati con competenze in Power Query.
Power Query registra i tuoi passi & Automatizza i processi
Power Query non solo rende tutti questi compiti più facili, ma registra anche i tuoi passi in modo da NON doverli ripetere. Vi farà risparmiare un sacco di tempo se preparate gli stessi dati ogni giorno, settimana o mese.
Fa anche un buon lavoro nella gestione degli errori. Se la struttura dei vostri dati sorgente cambia, Power Query vi dirà a quale passo si è rotto e vi permetterà di correggerlo. Questo rende la manutenzione facile e non dovete rifare completamente il vostro processo quando qualcosa cambia.
Potete usare Power Query per preparare i vostri dati per l’uso in tabelle pivot, grafici e dashboard report. Questo è un passo fondamentale nel processo di sintesi e analisi dei dati.
La Power Query Machine & Power BI
Beh, non può esattamente fare i biscotti, ma Power Query è uno strumento davvero fantastico! Vi farà risparmiare un sacco di tempo quando trasformate i vostri dati.
Power Query è solo un pezzo della suite di prodotti Power BI (Business Intelligence) di Microsoft.
Se torniamo all’analogia della cottura dei biscotti in una fabbrica, potete pensare a Power Query come al primo passo della catena di montaggio. Una volta che i biscotti sono formati, dobbiamo cuocerli (Tabelle Pivot, PowerPivot) e poi confezionarli per la presentazione (Power View, Power Map, Charts, Dashboards, ecc.)
Si può pensare a Excel come all’edificio della fabbrica che ospita tutti questi strumenti. La parte eccitante è che c’è un futuro molto luminoso per Excel! Tutti questi progressi nella tecnologia ci aiuteranno a dare un senso ai nostri dati in nuovi modi, a risparmiare tempo e a impressionare i nostri capi. 🙂
Come posso ottenere Power Query?
L’altra parte interessante è che Power Query è ora integrato in Excel a partire da Excel 2016 per Windows. Se siete su Excel 2010 o 2013 allora Power Query è un add-in gratuito.
Ho una pagina dedicata che vi aiuterà a determinare se avete la giusta versione di Excel per ottenere Power Query. Fornisce anche istruzioni complete per l’installazione e il link per il download.
Guida completa all’installazione di Power Query
Per darvi un’idea dell’importanza di questo strumento, Power Query è stato completamente integrato in Excel in Excel 2016 per Windows, e si trova nella scheda Dati della barra multifunzione.
È anche conosciuto come Get & Transform, anche se il termine Power Query è più comune.
Risorse aggiuntive
Questo articolo ha fornito una panoramica delle basi di Power Query che dovrebbe aiutare a capire alcune delle caratteristiche principali. Power Query ha una tonnellata di caratteristiche e c’è sicuramente molto da imparare.
Condividerò altri articoli e video nelle prossime settimane. Qui ci sono alcune risorse che ti aiuteranno ad iniziare.
Come dispiegare i tuoi dati con Power Query + Video Tutorial
Webinar di formazione gratuita sui Power Tools
In questo momento sto tenendo un webinar di formazione gratuita su tutti i Power Tools di Excel. Questo include Power Query, Power Pivot, Power BI, tabelle pivot, macro & VBA, e altro ancora.
Si chiama The Modern Excel Blueprint. Durante il webinar spiego cosa sono questi strumenti e come possono inserirsi nel vostro flusso di lavoro.
Imparerai anche a diventare l’eroe Excel della tua organizzazione, quella ragazza o ragazzo su cui tutti fanno affidamento per l’aiuto Excel e i progetti divertenti.
Il webinar è in esecuzione in più giorni e orari. Clicca sul link qui sotto per registrarti e salvare il tuo posto.
Clicca qui per registrarti al webinar gratuito