Come unire dati con Power Query - Excel Professionale

Power Query è lo strumento integrato in Excel (chiamato Recupera e trasforma in italiano) che ti permette di connetterti a molte tipi di fonti per importarne i dati, unirli, modellarli ed elaborarli.

Per essere più chiari, Power Query è un set di strumenti evoluti con cui è possibile creare connessioni con fonti esterne di tutti i tipi e con cui è possibile creare query con cui ottenere dati dalle fonti, per elaborarli, riorganizzarli e riversarli in tabelle Excel o tabelle pivot.

Power Query è presente nativamente in Excel a partire dalla versione 2016, ma nelle ultime versioni di Excel è più ricco di opzioni. Per le versioni 2010 e 2013 è possibile installarlo come componente aggiuntivo.

Inoltre Power Query è installato anche in Power BI per gli stessi scopi, quindi imparare a usarlo apre una porta nel mondo degli strumenti di reportistica avanzata.

Quindi cos’è una query?

È automazione, un programma che puoi scrivere e modellare secondo i tuoi obiettivi grazie all’editor dedicato, il “power query editor”, tramite la nostra interazione con i comandi e le finestre predisposti.

Quando le query vengono eseguite, gli strumenti dedicati si connettono alle basedati, estraggono i dati, li trasformano e li caricano dove ti servono.

In pratica, Power Query genera una serie di istruzioni in linguaggio M che descrivono, passo dopo passo, cosa vuoi ottenere. Puoi vedere queste istruzioni cliccando su “Visualizza editor avanzato” nel Power Query Editor.

Dove trovi i comandi di Power Query in Excel?

Tutti i comandi di Recupera e trasforma si trovano nella scheda Dati della barra multifunzione di Excel. All’interno vedrai il gruppo denominato “Recupera e trasforma dati” e il gruppo “Query e connessioni”.

Il primo gruppo offre i comandi per l’impostazione della connessione, il secondo i comandi per la gestione e per l’aggiornamento/esecuzione delle query.

Nel menu “Recupera dati” troverai i comandi per collegarti alle diverse fonti di dati divisi per tipologia. Ognuno di questi comandi offre strumenti dedicati e una o più finestre che permettono di indicare file, url, o specifiche del servizio e credenziali annesse.

Unire i dati con Power Query

Come già detto, Power Query è uno strumento potente e versatile con cui puoi ottenere dati da molte fonti diverse, per elaborarli, riorganizzarli e riversarli in tabelle Excel o tabelle pivot. Chiaro, no?

Quindi con power query è possibile anche prendere dati da più tabelle, anche su file Excel diversi, per unirli insieme nella basedati che desideri per le tue analisi, report, strumenti di controllo, eccetera.

Saper unire correttamente i dati di più tabelle è una competenza importante per chi usa Excel per l’analisi dei dati. Quando gestisci informazioni distribuite su più fogli o file, consolidarle in un unico set permette di semplificare, risparmiare tempo e ridurre errori.

Puoi unificare i dati in diversi modi, per esempio con funzioni di ricerca, come abbiamo visto in altri post e tutorial, ma unire i dati con Power Query semplifica l’operazione e garantisce aggiornamenti dinamici ogni volta che le tabelle originali cambiano.

Vantaggi di unire i dati con Power Query

Possibilità di importare dati da più fonti diverse che gli altri modi non possono o hanno difficoltà a gestire.
Eliminazione della necessità di scrivere e gestire formule complesse o macro evolute.
Interfaccia visuale intuitiva che guida passo dopo passo.
Aggiornamento automatico dei dati con un semplice “refresh”.

Passaggi Pratici per Unire i dati con Power Query

Il metodo più semplice è questo:

devi prima creare una query per ogni tabella Excel,
devi unire le query create in un nuova query che restituirà i dati che desideri uniti in un’unica tabella.

Per semplicità partiremo da tabelle Excel diversi presenti nello stesso file Excel.

Crea le query collegate alle fonti

Seleziona una cella della prima tabella dei dati;
vai nella scheda “Dati” della barra multifunzione, clicca su “Da tabella/intervallo”. Questo apre automaticamente l’editor Power Query;
clicca sulla scritta “chiudi e carica” sotto la prima icona in alto a sinistra della barra multifunzione, per aprire il menu, poi clicca su “chiudi e carica in”. Questo chiuderà l’editor, facendoti tornare a excel e farà comparire una finestra “importa dati” con diverse opzioni;
seleziona “crea solo connessione”, se necessario imposta “in un nuovo foglio di lavoro, se taggato togli la spunta a “aggiunti questi dati al modello dati”, poi clicca su “OK”.

Ripeti per tutte le tabelle che vuoi unire.

Il Power Query Editor

Il Power Query Editor è l’interfaccia dove costruisci le tue query. Ogni passaggio applicato ai dati viene registrato come una singola istruzione, scritta in un linguaggio specifico chiamato M. Queste istruzioni guidano l’estrazione dai file Excel, database esterni o altre fonti, e la loro successiva unione, trasformazione ed elaborazione.

Caratteristiche principali dell’editor:

Interfaccia visuale intuitiva: non serve conoscere linguaggi di programmazione per operare. Ogni trasformazione è rappresentata da un passaggio modificabile, eliminabile o riordinabile.
Controllo completo sui dati: puoi vedere in anteprima i risultati di ogni passaggio e tornare indietro in qualsiasi momento per correggere o adattare la query.
Automazione: una volta definita la query, questa è automazione che viene eseguita con i comandi integrati in excel. A partire dal comando “aggiorna” del menu contestuale.

Panoramica sintetica del Power Query Editor

L’interfaccia mostra:

una barra multifunzione in alto con 4 schede (home, trasforma, aggiungi colonna, visualizza) con menu e icone con cui richiamare finestre e comandi,
una finestra centrale che mostra l’anteprima dei dati,
una scheda a sinistra che mostra l’elenco delle query presenti nel file
una scheda a destra che mostra la sequenza degli step, dei passaggi della query.

Power Query traduce i comandi che usi nei passaggi della query.

Unisci le query che hai creato

Per unire le query che hai creato, puoi usare comandi interni all’editor o i comandi esteri. Per iniziare ti invito a usare i comandi esterni che trovi nel menu “Recupera dati” della scheda “Dati” della barra multifunzione.

clicca su “Recupera dati”
clicca su “Combina query”
clicca su “unisci” o su “aggiungi” a seconda del tipo di unione che desideri.

I tipi di unione

Unisci: integra i dati di una tabella all’altra tramite chiavi (campi con valori univoci, come ad esempio il codice cliente) che devono essere presenti e predisposte.
Aggiungi: concatena più tabelle uguali o simili una sotto l’altra.

In pratica:

Con “aggiungi” comparirà una finestra “accoda” che permette di seleziona le query da accodare.
Con “unisci” comparirà una finestra “merge” divisa in due metà, in quella superiore devi selezionare prima la tabella principale, in quella sottostante la tabella da unire. Poi devi indicare la colonna chiave comune a entrambe (es: codice cliente).

Quindi per unire i dati con power query, il passaggio cruciale è la selezione precisa delle tabelle da unire e la definizione della chiave di collegamento. La chiave deve essere un campo univoco o comunque presente e coerente tra le due fonti.

Infine puoi scegliere tra diversi tipi di join, ma per iniziare ti invito a usare “left outer” per mantenere tutte le righe della tabella principale e arricchirle con i dati corrispondenti della seconda.

I passaggi successivi dell’unione

Dopo aver confermato, si aprirà il Power Query editor e mostrerà un’anteprima con i dati della prima tabella selezionata e una colonna con il nome della seconda tabella.

Puoi espandere le colonne della seconda tabella cliccando con il pulsante sinistro del mouse sul pulsante con il simbolo della doppia freccia divergente, in alto accanto al nome della tabella secondaria. Si aprirà una finestra con l’elenco dei campi della seconda tabella che puoi selezionare o deselezionare con un clic.
seleziona i campi che desideri e clicca su “ok” e l’anteprima mostrerà la tabella con i dati aggiunti.
poi clicca sull’icona sopra la scritta “chiudi e carica”, la prima icona in alto a sinistra della barra multifunzione. Questo chiuderà l’editor, facendoti tornare a excel dove comparira un nuovo foglio con il nome della query e in esso una tabella con i dati uniti dalla query.

Chiarimenti sui metodi di Unione

Come hai visto Power Query offre due metodi per combinare dati provenienti da più tabelle con finalità e modalità diverse: unisci e aggiungi.

Aggiungi unisce più tabelle simili in un’unica tabella sequenziale.

Le tabelle dovrebbero avere colonne corrispondenti;
le righe delle tabelle vengono semplicemente sovrapposte una dopo l’altra.
se alcune colonne non sono presenti in tutte le tabelle, Power Query aggiunge quelle mancanti con valori nulli nelle righe corrispondenti alle tabelle che non presentano le colonne.

Questo metodo è utile quando vuoi aggregare dati analoghi, ad esempio più fogli di vendite mensili da combinare in un unico elenco annuale.

Unione integrare i dati di una tabella nell’altra usando campi “chiave” (es: codice cliente o ID prodotto).

Questo metodo è ideale quando hai una tabella principale (ad esempio fatture) a cui vuoi aggiungere dettagli da una seconda tabella (ad esempio anagrafica clienti);
esistono diversi tipi di join, che determinano quali righe rimangono nel risultato finale e che approfondiremo in altri post;
puoi scegliere le colonne da aggiungere dalla seconda tabella per integrarli nella tabella principale.

I comandi all’interno dell’editor

Puoi ottenere lo stesso risultato con i comandi che trovi all’interno dell’editor. Li trovi nella scheda “home” della barra multifunzione, nei menu “merge di query” e “accoda query”.

Nel primo troverai 2 comandi:

“unisci query come nuova” che corrisponde al comando esterno “unisci”,
“merge di query” invece unisce alla query selezionata una seconda tabella tramite finestra dedicata simile alla precedente, ma con la prima tabella già selezionata.

Nel menu “accoda query” troverai 2 comandi:

“accoda query come nuova” che corrisponde al comando esterno “accoda”,
“accoda query” invece accoda alla query selezionata una o più query tramite finestra dedicata con cui puoi selezionarle.

Importare i dati da tabelle in altri file Excel

Se vuoi importare i dati di una tabella contenuta in un altro file Excel, procedi in questo modo:

Clicca sulla scheda “Dati” della barra multifunzione, poi su “Recupera dati”, “Da file” e “Da cartella di lavoro di Excel” per richiamare la finestra con cui puoi indicare il file Excel a cui vuoi collegarti.
Nella finestra di dialogo scegli il file excel che contiene i dati, che per esempio hai estratto dal gestionale, o ottenuto dal collega, e clicca su “Importa”.
Excel apre il pannello di Navigazione che mostra le tabelle e i fogli con dati presenti nel file. Vedi sia le tabelle che sono state formalmente create in Excel (icona con la riga blu in alto) ma anche i fogli del file che contengono matrici di dati (icona foglio con le linguette in basso).
Seleziona la tabella che vuoi importare e scegli tra “Carica” oppure “Trasforma dati”.

Carica per importazione rapida

Se scegli Carica Excel creerà automaticamente una query che scarica i dati e crea una Tabella Excel in un nuovo foglio. Questa è la strada più rapida se il file di origine è già pulito e formattato esattamente come ti serve.

In pochi secondi Excel si occuperà di creare la query, di aggiungere un nuovo foglio e di installare una nuova tabella Excel con i dati dell’origine. Tutti avranno lo stesso nome.

Se invece clicchi sul pulsantino a destra di Carica, puoi aprire un mini menu con 2 comandi: “Carica” e “Carica in…”. Quest’ultimo apre una finestra che ti permette di decidere se collegare i dati e quindi creare:

Una tabella Excel
Una tabella Pivot
Un grafico Pivot

O di impostare la sola connessione senza nessun caricamento immediato. Questo serve per impostare query utilizzate da altre query o altri strumenti.

Nelle versioni più datate le opzioni offerte sono solo 2: tabella Excel o connessione.

Trasforma dati per aprire l’Editor

Se clicchi su “Trasforma dati” si apre l’Editor Power Query: è un ambiente simile a Excel ma dedicato esclusivamente alla preparazione delle query. Nell’Editor trovi:

Una barra multifunzione con le schede “Home”, “Trasforma”, “Aggiungi colonna” e “Visualizza”.
Il pannello centrale con l’anteprima dei dati (attenzione: è solo anteprima, non stai modificando il file sorgente).
Il pannello di destra con i passaggi applicati che vengono registrati ad ogni operazione o modifica.
Il pannello a sinistra con la lista delle query presenti nel foglio Excel.

Tutte le operazioni che fai nell’Editor vengono registrate come passi (step) che puoi riordinare, modificare o cancellare. Quando chiudi Power Query tramite i comandi dedicati, la query viene salvata ed eseguita e il risultato viene caricato nella destinazione scelta.

Importare dati da un foglio Excel ma non da una tabella Excel

Spesso i dati del file excel sorgente non sono inseriti in una tabella Excel, ma sono semplici matrici di dati in un foglio. In questi casi, se il foglio presenta qualche intestazione o informazioni extra nelle prime righe, se importi direttamente i dati potresti ottenere righe indesiderate sopra la tabella vera e propria.

Per rimuovere queste righe, la soluzione più semplice è entrare nell’Editor e applicare questi pochi passaggi:

Clicca su “Trasforma dati” per aprire l’Editor.
Nella scheda “home” dell’editor, clicca su “Rimuovi righe” e poi “Rimuovi righe iniziali”, per aprire la finestra dedicata in cui indicare il numero di righe da eliminare.
Poi usa la funzione “Usa la prima riga come intestazione” per promuovere la riga corretta a intestazioni di colonna.
Verifica i tipi di dati delle colonne e, se necessario, modifica i tipi (ad esempio Data, Numero, Testo) usando il pulsante a sinistra del nome della colonna per richiamare il menu veloce con i formati.
Infine clicca sul comando “Chiudi e carica”.

Il risultato sarà sempre una tabella Excel in un nuovo foglio con una tabella Excel in cui la query riverserà i dati.

Come aggiornare le query

Uno dei grandi vantaggi delle query è la loro dinamicità e la possibilità di essere eseguite con facilità, anche di aggiornare i dati in automatico. Per aggiornare direttamente una query:

clicca con il tasto destro su una cella della tabella Excel o della pivot a cui è collegata e clicca su “aggiorna”. La tabella Excel offre il comando “Aggiorna” proprio perché è alimentata da una query.
Oppure vai alla scheda “Dati” della barra multifunzione e clicca su “Aggiorna tutto”.

Aggiorna le query in automatico

In alternativa puoi modificare le impostazioni per far aggiornare la query in automatico. Per farlo clicca con il pulsante destro del mouse sulla query selezionata nella finestra “Query e connessioni” e clicca sul comando “Proprietà”.

Nella finestra “proprietà query” puoi impostare:

“Aggiorna all’apertura del file” per far aggiornare la query automaticamente all’apertura della cartella excel;
“Aggiorna ogni X minuti” per aggiornare la query in automatico ogni tot minuti.

Tra le varie impostazioni trovi “abilita caricamento veloce dei dati” che ti invito a settare per velocizzare l’esecuzione delle query al costo di un maggior impegno di risorse.

Conclusione

Imparare a unire dati con Power Query è una competenza utile se usci Excel per l’analisi dei dati e per preparare report e strumenti di controllo. Ti farà risparmiare tempo, ridurrà gli errori e renderà più comodo il tuo lavoro con Excel.

Cosa aspetti? Inizia subito a usare Power Query. Come hai visto non serve essere esperti per ottenere risultati in grado di cambiare i risultati del tuo lavoro con Excel.

PS: Se questo post ti è piaciuto o ti è stato utile, condividilo con gli amici sui social.

Puoi farlo attraverso i pulsanti qui di seguito. Grazie.

Cos’è Power Query (Recupera e Trasforma)?