Power Query: Importa e Trasforma i Dati come un Professionista

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.

Cos'è Power Query?

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).

• Estrazione: Estrae i dati da varie fonti (cartelle di lavoro di Excel, file di testo e CSV, pagine web, database SQL o persino servizi cloud).
• Trasformazione: Pulisce e rimodella i dati. Questo include il filtraggio delle righe, la modifica dei tipi di dati, la divisione del testo, l'unione di tabelle e la creazione di colonne calcolate.
• Caricamento: Invia i dati ripuliti direttamente in una tabella di Excel, in una tabella pivot o nel modello di dati di Power Pivot.

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.

Perché dovresti imparare a usare Power Query

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à:

• Nessun codice richiesto: Sebbene Power Query funzioni con un linguaggio di formule chiamato "M", puoi eseguire il 95% delle trasformazioni dei dati utilizzando semplicemente i pulsanti della barra multifunzione e i menu contestuali.
• Automazione senza VBA: Non è necessario saper scrivere macro per automatizzare le attività ripetitive. Power Query ricorda i tuoi "Passaggi applicati" e li applica automaticamente ai nuovi dati.
• Gestione dei Big Data: Excel ha un limite fisico di 1.048.576 righe. Power Query può connettersi a set di dati con milioni di righe, aggregare i dati in background e caricare solo i risultati riepilogativi nel tuo foglio di calcolo.
• Modifiche non distruttive: Power Query non altera mai l'origine dati originale. Crea una connessione, legge i dati e restituisce una copia appena pulita.

Per iniziare: l'Editor di Power Query

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:

1. La barra multifunzione: Situata in alto, contiene schede come Home, Trasforma, Aggiungi colonna e Visualizza. È qui che si trovano tutti i tuoi strumenti.
2. Il riquadro Query: Situato a sinistra, elenca tutte le connessioni dati attive nella tua cartella di lavoro.
3. L'anteprima dati: La griglia centrale che mostra un'anteprima in tempo reale di come appaiono i tuoi dati nel passaggio attualmente selezionato.
4. Il riquadro Impostazioni query (Passaggi applicati): Situato a destra. Questa è la parte più importante dell'editor. Ogni volta che modifichi qualcosa, Power Query lo registra qui come passaggio sequenziale. Puoi eliminare, riordinare o modificare questi passaggi in qualsiasi momento.

Guida passo-passo: Pulizia di dati di vendita disordinati

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.

Passaggio 1: Importare i dati

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.

Passaggio 2: Rimuovere le prime righe e alzare di livello le intestazioni

Le prime due righe dei nostri dati sono metadati dell'esportazione di sistema, non record di dati veri e propri. Dobbiamo sbarazzarcene.

• Nella scheda Home, fai clic su Rimuovi righe > Rimuovi prime righe. Inserisci "2" e fai clic su OK.
• Ora, le nostre vere intestazioni di colonna si trovano nella riga 1. Fai clic sul pulsante Usa prima riga come intestazione nella scheda Home. All'istante, "Rep_ID_Name", "Order_Date" e "Revenue" diventeranno i titoli ufficiali delle tue colonne.

Passaggio 3: Dividere la colonna del rappresentante

La colonna "Rep_ID_Name" contiene sia il numero ID che il nome del dipendente separati da un trattino.

• Fai clic con il pulsante destro del mouse sull'intestazione della colonna "Rep_ID_Name".
• Seleziona Dividi colonna > In base al delimitatore.
• Scegli "Personalizzato" e digita un trattino ( - ). Fai clic su OK.
• Power Query divide i dati in due colonne. Ora puoi fare doppio clic sulle nuove intestazioni di colonna per rinominarle in "Rep_ID" e "Rep_Name".

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.

Passaggio 4: Standardizzare i tipi di dati

Hai notato come le nostre date e i ricavi (Revenue) siano in formati completamente diversi? Power Query semplifica la standardizzazione di tutto questo.

• Fai clic sulla piccola icona a sinistra dell'intestazione della colonna "Order_Date" (probabilmente c'è scritto "ABC" o "Qualsiasi"). Cambiala in Data. Power Query traduce automaticamente i vari formati di data in una data standard unificata.
• Fai clic sull'icona accanto a "Revenue" e cambiala in Valuta (o Numero decimale). Rimuove i simboli del dollaro e formatta tutto in modo pulito.

Passaggio 5: Aggiungere una colonna condizionale

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")

Unire query: l'alternativa moderna a VLOOKUP

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.

Trasformare le colonne in righe (Unpivot): l'arma segreta di Power Query

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.

Caricamento dei dati trasformati

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 magia del pulsante "Aggiorna"

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.

Semplificare il tuo percorso nei dati con l'IA

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, ExcelGPT è 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 ExcelGPT 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.

Domande Frequenti (FAQ)

Power Query modifica il mio file di dati originale?

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.

Posso usare Power Query su Mac?

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.

Qual è la differenza tra Unisci (Merge) e Accoda (Append) in Power Query?

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).

Perché l'aggiornamento di Power Query non è andato a buon fine?

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.