Come importare in Excel i dati da file esterni in pochi semplici passaggi con Power Query

Power Query è lo strumento integrato in Excel (chiamato Recupera e trasforma in italiano, Get & Transform in inglese) che ti permette di connetterti a molte fonti dati per importarne i dati.

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, ogni volta che selezioni una fonte dati e clicchi su “Trasforma dati” o “Carica”, 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. I comandi più comuni sono quelli per collegarsi a file e servizi diversi, quelli disponibili anche per profili non business di solito sono:

Da file (Excel, testo/CSV, Pdf, Xml, Json e Cartella, con cui puoi importare i dati da tutti i file di una cartella);
Da database (Sql Server, Access, ecc.);
Da Azure;
Da piattaforma;
Da altre origini (tabella, Web, Feed, Odbc, Oledb, immagini).

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.

A seconda della versione di Excel, alcuni nomi cambiano leggermente e alcuni comandi sono leggermente diversi.

Perché importare dati esterni in Excel?

Importare dati da file esterni è ormai una necessità in qualsiasi contesto professionale. Per esempio:

Hai un file Excel che contiene le anagrafiche di clienti o fornitori e vuoi usarle in altri file/strumenti, per esempio nello strumento di preventivazione, crm, gestionale ordini, ecc.
Il gestionale aziendale esporta i dati delle vendite in CSV e vuoi analizzare regolarmente questi dati senza dover rifare tutto da capo.
I fornitori ti inviano i listini in formato xlsx o in PDF e devi estrarre le tabelle per analizzare i dati o per aggiornare la tua anagrafica.
Più persone della tua azienda gestiscono l’avanzamento delle commesse a loro affidate tramite modelli predisposti e vuoi controllarne l’attività importando tutti i dati in uno strumento di controllo.

Con Power Query puoi creare connessioni dinamiche alle sorgenti e preparare automazione che importa, filtra, elabora e trasforma i dati ogni volta che aggiorni la query. In pratica con le query non crei solo una copia statica dei dati, ma un sistema dinamico ed affidabile per aggiornali.

Importare da un altro file Excel: guida passo passo

Prendiamo il caso più comune: vuoi importare i dati di una tabella fatture 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: 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.

In ogni caso, dopo che la query è stata creata puoi sempre cambiare destinazione richiamando il pannello “Query e connessioni” e usando il comando “carica in” che trovi nel menu contestuale delle query.

Trasforma dati: aprire l’Editor di Power Query

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.

Importare da file CSV o TXT con Power query

Importare un CSV è anche più semplice dei file Excel:

Vai alla scheda “Dati”, poi clicca su “Recupera dati”, “Da file” e “Da testo/CSV”.
Nella finestra di dialogo scegli il file “csv” che contiene i dati e clicca su “Importa”.
Si aprirà una finestra di anteprima dove puoi anche scegliere una codifica e un delimitatore diversi (di solito non ne hai bisogno).
Clicca su “Carica” per creare automaticamente una query che scarica i dati e crea una Tabella Excel in un nuovo foglio.

Codifica e delimitatori verranno riconosciuti in automatico dallo strumento, ma in caso di problemi o di formati non standard prova a scegliere delle opzioni diverse. Una codifica errata può generare caratteri strani nei testi. Il delimitatore in Italia è di solito il punto e virgola (;) e non la virgola.

Importare dati da file PDF con Power query

Power Query dispone di una funzione anche per leggere tabelle direttamente dai PDF. Naturalmente la qualità del risultato dipende molto dal formato del PDF e delle tabelle che contengono i dati:

Se il PDF contiene tabelle strutturate, Power Query le riconosce correttamente e le inserisce nell’elenco di tabelle e pagine nella finestra di Navigazione.
Se i dati non sono inseriti in tabelle, ma per esempio in elenchi puntati, questo verranno letti, ma non è detto che vengano riconosciuti in modo corretto, di conseguenza si dovranno generare più query da unire e modellare.
Se i dati sono inseriti in tabelle fittizie, impostate come formattazione del testo, anch’essi possono essere letti, ma il risultato sarà probabilmente scarso e sarà necessario un notevole lavoro con l’editor.
Se il PDF è un’immagine, è opportuno trasformare il pdf in immagine e usare il connettore per le immagini, ma il risultato difficilmente sarà affidabile al 100%. In questo caso è consigliabile usare un software Ocr.

Quindi se dati e pdf sono standard, allora il risultato sarà quello atteso, analogo all’importazione da file excel. In caso contrario sarai costretto a lavorare sulla o sulle query create dall’estrazione.

Creare più query dalla stessa fonte dei dati.

Quando il PDF contiene più tabelle distribuite su più pagine, puoi selezionare più elementi contemporaneamente nella finestra di Navigazione. Per farlo devi taggare l’opzione per abilitare la scelta di tabelle multiple. Lo trovi in alto a sinistra. Poi devi selezionare una a una le tabelle che vuoi importare.

In tal caso Power Query creerà una query per ciascuna tabella selezionata, senza creare foglio con tabelle Excel impostata.

In realtà puoi creare più query con un solo passaggio anche in caso di tabella Excel, se il file contiene più fogli con più tabelle, o più in generale quando l’anteprima ti mostra più opzioni.

Una volta create le query, se i dati sono corrispondenti e se preferisci ottenere un’unica tabella combinata, dovrai unire le tabelle estratte tramite i comandi dedicati. Se invece le query create sono da perfezionare, dovrai revisionarle una alla volta tramite l’editor e successivamente unire i dati.

Unire i dati di più query con Power query

Capita spesso di dover unire dati provenienti da più file o più tabelle dello stesso file. Power Query offre due comandi per unire i dati:

Aggiungi, impila i record, una tabella sopra l’altra. Serve quando i dataset hanno la stessa struttura (stesse colonne) e vuoi un unico elenco dei record.
Unisci, combina due tabelle basandosi su una o più colonne chiave. Serve quando hai tabelle che contengono le chiavi rispettive (es: il codice cliente). Una chiave è un campo che contiene stringhe alfanumeriche univoche che puoi usare per identificare i singoli record.

Dove sono i comandi?

Li trovi nell’editor, ma li trovi anche tra i comandi disponibili nei menu del foglio dati. Per esempio se vuoi aggiungere i dati di più query in una sola, procedi in questo modo:

Vai alla scheda “Dati” della barra multifunzione, poi clicca su “Recupera dati”, “Combina query” e “Aggiungi”.
Si aprirà una finestra apposita che ti permette di indicare quali query “aggiungere”, puoi seleziona direttamente due tabelle o scegliere l’opzione “Tre o più tabelle” per aggiungere una lista di query.
Dai conferma e si aprirà l’Editor con la query creata.
Eventualmente controlla nomi delle colonne e formati.
Clicca su “Chiudi e carica” per chiudere l’editor e creare la tabella in un nuovo foglio.

Come gestire le query

Per la gestione delle query devi conoscere alcuni strumenti e comandi fondamentali.

Il primo è il pannello “Query e connessioni”, è fondamentale per gestire i tuoi collegamenti. Il comando omonimo per richiamarlo lo trovi sempre nella scheda Dati vicino al comando “aggiorna tutto”. Cliccalo per apri la scheda “Query e connessioni” sulla destra della finestra, cliccalo nuovamente per nasconderla.

Nella finestra sono elencate tutte le query presenti nel foglio excel. Sostando con il cursore sul nome di una query puoi richiamare la finestra di anteprima con le informazioni sui dati della query e con alcuni dei comandi di gestione. Cliccando con il pulsante destro sul nome di una query della scheda puoi richiamare il menu contestuale con tutti i comandi necessari per la gestione, tra cui:

Modifica: apre l’Editor.
Rinomina: ti permette di cambiare il nome della query.
Carica in…: cambia la destinazione della query, cioè in quale strumento vengono caricati i dati.
Aggiorna: esegue la query e aggiorna 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”.

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.

Alcuni consigli utili

Ecco una serie di consigli pratici che ti faranno risparmiare tempo e ridurranno gli errori:

Nomina le query in modo parlante: invece di Query1, usa “Anagrafica_Clienti”. Aiuta quando le query diventano molte.
Controlla i formati dei dati: dopo che la query viene impostata, verifica sempre i formati dei dati per individuare colonne con dati “misti” da gestire o errori restituiti da formule o altro.
Rimuovi i dati inutili: filtra i record che non ti servono (filtra i record tramite la solito finestra “ordina e filtra”) e rimuovi le colonne (seleziona le colonne e cancella) che non ti servono. Meno dati significano query più veloci.
Evita riferimenti a file con percorsi instabili: se i file sorgente cambiano o si spostano spesso, trova il modo di dare lo stesso nome ai file e valuta l’uso di una cartella condivisa stabile su nas o server.
Ti anticipo che è possibile prevenire gli errori di origine (ma richiede conoscenza del codice), che è possibile usare variabili “esterne” nelle query (argomento che vedremo in altri post), e che è possibile usare l’automazione aggiunta (vba) come supporto alle query.
Gestisci la privacy: Power Query può chiederti di impostare il livello di privacy per le connessioni (Privato, Organizzativo, Pubblico). Soprattutto in ambito aziendale, imposta la privacy correttamente tramite le finestre che vengono proposte per evitare situazioni indesiderate.

Alcuni errori o problemi comuni

Quando inizi a usare Power Query incontrerai alcuni problemi ricorrenti. Eccone alcuni:

Righe iniziali indesiderate

Come già spiegato sopra, usa il comando “Rimuovi righe iniziali” per rimuoverle, poi “Usa prima riga come intestazione” per promuovere la riga con i titoli effettivi.

Date non riconosciute

Verifica il formato del dato, se necessario imposta il formato data e in presenza di errori verifica la presenza di dati misti. In certi casi le date possono presentare formati anomali che richiedono riformattazioni con funzioni di testo.

Le tabelle estratte sono divise

Può accadere per esempio con i Pdf. Per creare un’unica tabella è necessario unire i dati con il comando “aggiungi” se le tabelle hanno gli stessi campi.

Compare un errore di origine

Se il file viene spostato o rinominato o cancellato, la query non può essere eseguita, i dati non possono essere aggiornati e comparirà una finestra per segnalarlo e le query verranno marcate con un’icona gialla.

Per risolvere il problema entra nell’editor, clicca sul pulsante nella barra gialla, se necessario clicca una seconda volta sul pulsante nella barra gialla e poi usa le finestre che verranno presentate per indicare la nuova posizione o il nuovo file.

Mancanza di credenziali

Se lavori con database aziendali o web service, avrai bisogno di credenziali adatte e talvolta di accessi specifici (es: tramite vpn). Se il file viene usato in file diversi senza credenziali impostate, comparirà una finestra che richiederà di inserire le credenziali per la connessione (windows, account organizzativo, anonimo, ecc.) o di accedere al profilo web di microsoft.

Importa i dati da più file

Quando i dati provengono da molti file con dati analoghi e struttura, puoi usare le query per importare i dati dai tutti i file di una cartella specifica. Per farlo:

Vai alla scheda “Dati” e clicca sul menu “Recupera dati”, poi “Da file” e infine sul comando “Da cartella”.
Tramite la finestra seleziona la cartella che contiene i file (es. i file csv con i dati mensili delle vendite).
Clicca su “Combina”, poi su “combina e carica” e poi clicca su “Ok” per confermare.

Power Query analizzerà la struttura e combinerà i file automaticamente applicando una procedura di trasformazione comune. Questo ti permetterà di aggiungere ogni nuovo file in quella cartella e poi semplicemente aggiornare la query per includerlo nell’analisi.

Le query, il codice e l’editor avanzato

Come spiegato in precedenza, l’editor di Power Query scrive il codice delle query al posto nostro. Il linguaggio usato per le query in Excel si chiama M.

È necessario che tu conosca il linguaggio?

Nella maggior parte dei casi no, perché l’interfaccia visuale dell’editor con cui manipoliamo i dati ci offre la maggior parte delle opzioni che ci servono. Tuttavia, se vuoi fare operazioni complesse o ottimizzazioni, la conoscenza del codice diventa davvero utile, se non indispensabile.

Ma finché l’obiettivo è quello di importare dati tramite query, come hai visto, non solo non devi conoscere il linguaggio, ma ti serve solo una conoscenza superficiale dell’editor. Ti invito a guardare questo tutorial per vedere in pratica ciò che ho spiegato fino ad ora.

Perché dovresti usare Power Query?

In sintesi perché è automazione efficiente ed efficace, dinamico e comoda.

Questi sono i principali vantaggi nell’adottare Power Query per l’importazione dei dati:

Automazione: imposti le query una volta e poi basta un clic per eseguirle quando devi aggiornare i dati.
Facilità: come hai visto finché parliamo di importare dati da altri file lo strumento fa quasi tutto il lavoro di preparazione.
Flessibilità: supporta molte fonti diverse.
Integrazione: le query caricano i dati direttamente in tabelle Excel o Pivot.
Scalabilità: puoi facilmente collegare più file trasferendone i dati dove serve senza dover spendere tempo a copia-incollarli o a collegarli tramite riferimenti.

Conclusione

Imparare a importare dati da file esterni con Power Query è una delle competenze più utili che puoi acquisire se devi lavorare con Excel. Ti permette di automatizzare attività ripetitive, di importare dati in modo semplice e di collegare tra loro i tuoi file excel.

E questo 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 programmatori per ottenere risultati in grado di cambiare i tuoi strumenti e il 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)?