Se trascorri ore ogni settimana a scaricare file CSV, eliminare righe vuote, formattare date e scrivere complesse formule nidificate solo per preparare i tuoi dati per l'analisi, stai lavorando più del necessario. Benvenuto in Power Query, lo strumento di automazione dei dati più potente integrato direttamente in Microsoft Excel.
Spesso definito "Recupera e trasforma dati", Power Query ti consente di connetterti a quasi tutte le origini dati, pulire e rimodellare le informazioni e caricarle nel tuo foglio di calcolo. La parte migliore? Registra i tuoi passaggi. La volta successiva che ricevi nuovi dati, non dovrai ripetere il lavoro manuale; ti basterà fare clic su Aggiorna.
In questa guida completa, esploreremo cos'è Power Query, come navigare nella sua interfaccia e analizzeremo un esempio pratico per trasformare un set di dati disordinato in informazioni pulite e pronte per l'analisi.
Power Query è un motore di connessione e preparazione dei dati. Nel mondo della gestione dei database, questo processo è noto come ETL: Extract, Transform, Load (Estrazione, Trasformazione e Caricamento).
Tradizionalmente, gli utenti di Excel si affidavano a una combinazione di funzioni come TRIM, PROPER, SUBSTITUTE e VLOOKUP associate a operazioni manuali di copia e incolla per gestire queste attività . Power Query sostituisce quel flusso di lavoro noioso con un'interfaccia visiva e intuitiva.
Se sei ancora indeciso se imparare o meno a usare un nuovo strumento in Excel, ecco perché padroneggiare Power Query cambierà le regole del gioco per la tua produttività :
Per accedere a Power Query, apri una cartella di lavoro vuota di Excel e vai alla scheda Dati sulla barra multifunzione. Cerca il gruppo Recupera e trasforma dati all'estrema sinistra.
Da qui, puoi fare clic su Recupera dati per visualizzare un menu a discesa delle origini dati disponibili. Una volta selezionato un file e fatto clic su "Trasforma dati", Excel apre l'Editor di Power Query in una nuova finestra. Questa interfaccia è composta da quattro aree principali:
Diamo un'occhiata a un esempio pratico e reale. Immagina di esportare un report di vendita settimanale dal CRM della tua azienda. L'esportazione grezza è disordinata e contiene intestazioni non necessarie, stringhe di testo combinate e formattazione incoerente.
Ecco un esempio dei nostri dati grezzi e disordinati:
| Esportazione sistema: Report vendite Q3 | Colonna2 | Colonna3 |
|---|---|---|
| Generato il: 10/01/2023 | ||
| Rep_ID_Name | Order_Date | Revenue |
| 101-John Doe | 2023-08-15 | 1500.5 |
| 102-Jane Smith | 09/01/2023 | $2,340.00 |
| 103-Bob_Jones | 23-Sep-2023 | 850.75 |
Se usassimo le formule tradizionali, dovremmo usare LEFT, RIGHT, FIND e VALUE per estrarre i nomi dei rappresentanti e correggere i numeri. Usiamo invece Power Query.
Salva i dati disordinati come file CSV o Excel. Apri una nuova cartella di lavoro di Excel, vai su Dati > Recupera dati > Da file e seleziona il tuo file. Quando appare la finestra di anteprima, fai clic su Trasforma dati. Si aprirà l'Editor di Power Query.
Le prime due righe dei nostri dati sono metadati dell'esportazione di sistema, non record di dati veri e propri. Dobbiamo sbarazzarcene.
La colonna "Rep_ID_Name" contiene sia il numero ID che il nome del dipendente separati da un trattino.
Per ripulire i trattini bassi nel nome di Bob (Bob_Jones), fai clic con il pulsante destro del mouse sulla colonna Rep_Name, scegli Sostituisci valori, digita un trattino basso (_) nella casella "Valore da trovare" e lascia "Sostituisci con" vuoto o aggiungi uno spazio. Fai clic su OK.
Hai notato come le nostre date e i ricavi (Revenue) siano in formati completamente diversi? Power Query semplifica la standardizzazione di tutto questo.
Supponiamo di voler classificare le vendite superiori a $1.000 come "Valore elevato" (High Value). Invece di scrivere una complessa funzione IF come =IF(C2>=1000, "High Value", "Standard") in Excel, possiamo usare l'interfaccia utente di Power Query.
Vai alla scheda Aggiungi colonna e fai clic su Colonna condizionale. Imposta le regole: Se [Revenue] è maggiore o uguale a 1000, l'output sarà "High Value", altrimenti "Standard". Dietro le quinte, Power Query genera il seguente codice M per questo passaggio:
= Table.AddColumn(#"Changed Type", "Sales Category", each if [Revenue] >= 1000 then "High Value" else "Standard")Una delle attività più comuni nell'analisi dei dati è combinare tabelle. Se disponi di una tabella separata contenente la regione per ciascun rappresentante di vendita, potresti solitamente rivolgerti alla nostra guida completa su VLOOKUP per importare quei dati.
Tuttavia, l'esecuzione di migliaia di formule VLOOKUP o INDEX e MATCH può rallentare drasticamente la tua cartella di lavoro. In Power Query, utilizzi la funzione Unisci query.
Importa semplicemente entrambe le tabelle in Power Query, seleziona la tabella delle vendite principale e fai clic su Unisci query nella scheda Home. Seleziona la seconda tabella (la tabella Regions), fai clic sulla colonna corrispondente in entrambe le tabelle (ad es. "Rep_ID") e fai clic su OK. Power Query esegue l'equivalente di un VLOOKUP ultra-veloce in pochi secondi, indipendentemente dal fatto che tu abbia dieci righe o dieci milioni.
Spesso ricevi dati che sono già raggruppati in una struttura simile a una pivot (ad esempio, i mesi che scorrono lungo le colonne: Gen, Feb, Mar, Apr). Sebbene questo sia facile da leggere per gli esseri umani, è pessimo per la creazione di grafici o tabelle pivot.
Seleziona le tue colonne identificative (come Rep Name), fai clic con il pulsante destro del mouse sull'intestazione e scegli Trasforma altre colonne tramite unpivot. Power Query trasforma istantaneamente i tuoi dati tabulari incrociati e larghi in un layout tabulare piatto con una nuova colonna "Attributo" (Mese) e "Valore" (Vendite). Fare questo con le formule standard di Excel è quasi impossibile, rendendo l'Unpivot una delle funzionalità più celebri di Power Query.
Una volta che i tuoi dati sono perfettamente puliti, è il momento di rimandarli in Excel.
Nella scheda Home, fai clic su Chiudi e carica. Per impostazione predefinita, questo caricherà i tuoi dati trasformati in una nuovissima tabella Excel verde su un nuovo foglio di lavoro. Se preferisci inviare i dati direttamente alla fase di analisi, puoi fare clic sulla freccia del menu a discesa, scegliere Chiudi e carica in... e selezionare invece un Rapporto tabella pivot. Se hai bisogno di un ripasso sulla creazione di questi riepiloghi, dai un'occhiata al nostro tutorial sulla creazione di tabelle pivot per principianti.
La vera potenza di Power Query diventa evidente la settimana successiva quando ricevi una nuova esportazione di vendite grezze. Non ripetere i passaggi precedenti!
Salva semplicemente il nuovo file CSV sovrascrivendolo a quello vecchio (mantieni esattamente lo stesso nome del file e la stessa posizione della cartella). Quindi, apri la tua cartella di lavoro di Excel, fai clic con il pulsante destro del mouse ovunque nella tua tabella di dati pulita e fai clic su Aggiorna.
Power Query si collega al file, riapplica ogni singolo passaggio (rimozione di righe, innalzamento delle intestazioni, divisione di colonne, sostituzione di testo, verifica delle condizioni e unione di tabelle) e aggiorna il risultato finale in una frazione di secondo. Questo è un componente vitale dei flussi di lavoro di automazione di Excel.
Sebbene Power Query gestisca brillantemente le trasformazioni strutturali, a volte hai bisogno di una logica condizionale specifica o di un'analisi di testo complessa che richiede formule Excel avanzate o codice M personalizzato. Invece di setacciare i forum in cerca di risposte, puoi sfruttare l'intelligenza artificiale.
Se hai difficoltà a scrivere il perfetto calcolo per una colonna personalizzata, GPTExcel è il compagno ideale. Descrivi semplicemente ciò che stai cercando di ottenere in italiano — ad esempio, "Ho bisogno di una formula per estrarre solo i numeri da una stringa di testo mista" — ed GPTExcel genererà istantaneamente la formula corretta o il codice M. Combinare Power Query con l'IA per la pulizia dei dati ti offre un kit di strumenti inarrestabile per l'analisi dei dati.
No. Power Query crea una connessione unidirezionale ai tuoi dati di origine. Legge i dati, applica le trasformazioni in memoria e restituisce un nuovo risultato in Excel. Il tuo CSV, database o cartella di lavoro originale rimane completamente intatto e al sicuro.
Sì, Microsoft ha notevolmente migliorato il supporto per Power Query in Excel per Mac. Mentre la versione per Mac tradizionalmente mancava di alcuni dei connettori avanzati e delle funzionalità dell'interfaccia utente disponibili su Windows, ora puoi connetterti a file locali, database e aggiornare senza problemi le query esistenti nelle versioni moderne di Microsoft 365.
Unisci (Merge) è l'equivalente di un VLOOKUP o di INDEX/MATCH. Lo usi per aggiungere nuove colonne di dati abbinando un ID comune tra due tabelle. Accoda (Append) è come copiare e incollare i dati in fondo a un foglio. Lo usi per impilare le tabelle una sull'altra, aggiungendo nuove righe (ad esempio, combinando le vendite di gennaio e le vendite di febbraio).
Il motivo più comune per cui l'aggiornamento di una query fallisce è che il file di origine è stato spostato, rinominato o eliminato. Un altro problema frequente è che un'intestazione di colonna nei dati grezzi è cambiata (ad es., "Revenue" è stato modificato in "Total Revenue" dal sistema). Puoi risolvere questo problema aprendo l'Editor di Power Query, andando nel riquadro Passaggi applicati e aggiornando il passaggio Origine o rinominando la colonna nella logica del tuo passaggio.
Scopri come usare le funzioni statistiche essenziali di Excel come AVERAGE, MEDIAN, MODE e STDEV per riepilogare e analizzare efficacemente i tuoi set di dati.
Padroneggia la Convalida dati di Excel per applicare regole, creare elenchi a discesa personalizzati e mantenere una qualità dei dati impeccabile nei tuoi fogli di calcolo professionali.
Scopri come utilizzare Power Query per automatizzare le attività di importazione e trasformazione dei dati in Excel. Dì addio alla pulizia manuale con questa guida passo-passo.