Nelle ultime versioni in Excel è stato integrato lo strumento Power Query, ora chiamato Recupera e Trasforma. Cos’è Power Query? È uno strumento che permette di creare connessioni a basedati esterne, di importarne i dati e di modellarli ed elaborarli in modo evoluto e comodo. Oggi parliamo di questo potente strumento che offre possibilità straordinarie a chi è in grado di usarlo.
Cos’è Power Query?
Power Query, ora chiamato “Recupera e trasforma”, è uno strumento integrato in Excel dalla versione 2016, che permette di creare in modo semplice connessioni e query.
Cos’è una connessione?
Una connessione è una serie di dati necessari agli strumenti che si occupano di collegarsi e interrogare basi dati esterne, nel nostro caso Excel stesso, o meglio uno strumento di Excel. I dati indicano l’obiettivo della connessione, le modalità della connessione, le credenziali, eccetera.
Cos’è una query?
Una query è una interrogazione a una basedati fatta nel linguaggio che comprende il programma che gestisce la base dati. Sostanzialmente una query è una stringa che indica quali dati vuole che la basedati restituisca, cioè quali campi e record, eventualmente filtrati, raggruppati, elaborati, eccetera.
A cosa serve Power Query?
Quindi Power Query offre strumenti, comandi e automazione per collegarsi a basedati esterne, estrarne i dati e se necessario elaborarli. Il risultato è una tabella Excel con i dati estratti, aggiornabile con un clic attraverso l’apposito comando, dati che possiamo usare nelle nostre elaborazioni.
In realtà in excel 365, i dati estratti dalle query possono essere usati per alimentare direttamente anche pivot e grafici pivot, evitando di dover creare la tabella Excel.
Quindi power query serve a:
- estrarre dati da fonti esterne (server aziendale, siti web, database di varia natura, ecc),
- modellare i dati come è necessario ai nostri bisogni, per esempio filtrarne record e campi,
- ripulire i dati da errori, valori anomali, fuori distribuzione, o altro,
- elaborare i dati raggruppando e unendo, applicando funzioni, creando campi calcolati e condizionali, e molto altro ancora.
Dov’è Power Query?
Come abbiamo già detto Power query è integrato in Excel a partire dal 2016, mentre è un plugin installabile in excel 2010 e 2013 (ma il componente non è più supportato e sviluppato dal produttore).
I comandi di Power Query si trovano nella scheda Dati della barra multifunzione. Nella versione 365 sono raccolti nei gruppi “Recupera e trasforma dati” e “Query e connessioni”.
Tra i molti comandi troviamo quelli per impostare le connessioni alle fonti dei dati, che siano server sql, servizi di vario tipo, database access, pagine web, testi, o anche solo tabelle Excel. E troviamo i comandi per la gestione delle query e delle connessioni, come “Apri l’editor”, “Opzioni query”, “Aggiorna tutti”, “Query e connessioni”, eccetera.
Gli strumenti principali del Power Query
Power query è uno strumento evoluto composto da centinaia di automatismi, decine di finestre di dialogo e strumenti complessi che possiamo dividere in:
- comandi per la creazione delle connessioni,
- l’editor delle query,
- la finestra “query e connessioni”,
- i comandi per la gestione.
I comandi per la creazione delle connessioni sono decine a causa della molteplicità delle fonti, accompagnati ognuno da finestre e automatismi specifici che impostano la connessione, per poi aprire l’editor. Di solito se ne usano pochi, legati ai nostri effettivi bisogni, e sono tutti comodi e relativamente semplici da utilizzare.
L’editor delle query è programma vero e proprio, con una sua finestra, barra multifunzione, centinaia di comandi, decide di strumenti. Lo scopo dell’editor è realizzare la query in modo visuale e intuitivo attraverso i comandi predisposti.
In realtà le query non sono altro che istruzioni in un linguaggio specifico, M, che potremmo scrivere con un editor di testo, naturalmente se lo conoscessimo bene. L’editor in realtà non fa altro che tradurre le nostre attività su un modello dei dati della basedati nelle istruzioni corrette.
La finestra “query e connessioni” con i suoi strumenti, finestre, anteprime, comandi e menu permette di accedere e gestire velocemente le query presenti nel foglio. Possiamo per esempio vedere le caratteristiche di ogni query, a quale basedati è connessa, possiamo aggiornarla, rinominarle e molto altro.
I comandi per la gestione sono una serie di comandi e strumenti con cui possiamo agire sulle impostazioni, le proprietà, aggiornare le query, visualizzare query e connessioni, gestire le impostazioni delle origini dei dati e molto altro. Tra questi il comando più usato è “Aggiorna tutti”, con cui possiamo richiedere l’aggiornamento dei dati a tutte le fonti esterne.
Importare dati esterni
I comandi destinati alla creazione delle connessioni sono automatismi, di solito accompagnati da finestre dedicate, che hanno lo scopo di interrogarci e ricevere le informazioni necessarie per contattare le fonti e creare la connessione.
Quali informazioni?
Possono essere la semplice indicazione del file o della tabella Excel a cui collegare la query, oppure del file di testo, xml, json, pdf, access, eccetera, o del sito, o dell’indirizzo del servizio, fino alla richiesta del nome/indirizzo e delle credenziali necessarie per l’accesso, ma anche dell’istruzione sql con cui interagire con il server, o altro ancora.
Tratteremo in altri post esempi di connessioni alle fonti più diffuse, ma partiamo subito dividendo le fonti in tre categorie:
- locali (file di vario tipo individuabili sullo stesso pc, in una cartella della rete aziendale, o dello spazio on cloud)
- server o servizi (raggiungibili tramite rete aziendale, o via web, tramite vpn o senza)
- altre fonti (siti, feed o database web, o altre ancora).
Quali connessioni sono le più usate?
Dipende dai bisogni di ognuno, dall’uso che le aziende fanno o permettono di Excel, dalle politiche aziendali, dalle competenze. Un utente base con una conoscenza intermedia o inferiore di Excel può usare power query per lavorare con basedati di altri fogli excel, o estrarre dati da file csv o di altro tipo. Un utente che lavora in ambito aziendale e si occupa di controllo e di analisi probabilmente userà connessioni a server sql o analoghi.
I comandi di power query
Spiegare tutti i comandi per l’uso e la gestione delle query richiederebbe da solo decine di pagine, quindi rimando ad altri post dedicati per gli approfondimenti, limitandomi ai comandi e strumenti più importanti.
Quali sono i comandi più importanti?
I comandi principali sono:
- “Aggiorna tutti”, per aggiornare tutte le query del foglio;
- “Query e connessioni”, per aprire la finestra omonima nel lato destro della finestra con due schede, una che mostra l’elenco delle query presenti nel foglio, l’altra con l’elenco delle connessioni esterne;
- “Opzioni query” per aprire la finestra delle opzioni.
In realtà le decine di comandi della scheda dati sono solo una piccola parte di tutti i comandi. Una volta impostata la connessione, per esempio a una tabella di un foglio Excel esterno, attività che richiede pochi secondi, la maggior parte del tempo la spenderai o sull’editor delle query per creare o revisionare le query, o sulla finestra “query e connessioni”. Ognuno di questi strumenti è composto da decine o centinaia di comandi.
Query e connessioni
La finestra vuota sembra un inutile strumento, mentre in presenza di query diventa una piattaforma di gestione che ti permette di:
- sapere con un colpo d’occhio se la query ha dei problemi, se vedi un triangolo giallo c’è un problema da risolvere che sta bloccando le query;
- saltare con un clic alla tabella che mostra i dati della query;
- vedere una finestra di anteprima che ti mostra un accenno della tabelle e le informazioni fondamentali (origini, campi richiesti, aggiornamento, ecc), ma che ti offre anche una serie di comandi utili;
- aprire l’editor con un doppio clic;
- aprire un menu contestuale con il tasto destro del mouse con una lunga serie di comandi di gestione della query stessa, come per esempio duplicarla, caricarla in una tabella o pivot, unirla con altre query, e altro ancora.
Questa finestra è una vera e propria piattaforma per la gestione delle query e forse è lo strumento che userai di più.
L’Editor di Power Query
L’editor si apre al termine della creazione di ogni connessione, oppure si apre cliccando sulle query della finestra “Query e connessioni”, oppure sui comandi appositi della finestra di anteprima, della barra multifunzione, dei menu relativi, eccetera.
L’editor ha una finestra propria che costringe excel in secondo piano e a tutti gli effetti è un programma a parte, complesso, evoluto, comodo e pesantemente automatizzato. La finestra è divisa in 4 parti:
- la barra multifunzione in alto con quattro schede e un menu, con un centinaio di comandi che richiamano decine di finestre specifiche per la gestione delle diverse funzioni;
- una finestra centrale che mostra la tabella dei dati via via che la modifichiamo, ma che in realtà è solo una rappresentazione dei dati estratti, come li vedremmo nella tabella in cui li possiamo riversare;
- una barra a sinistra, di solito chiusa, che mostra l’elenco delle query presenti nel foglio e che ci permette di passare da una all’altra;
- e la barra a destra che mostra il nome della query, che possiamo modificare a piacimento, e l’elenco delle attività e operazioni che applichiamo alla tabella attraverso i molti comandi.
Naturalmente nel programma tutto è un oggetto con cui possiamo interagire e ci sono comandi presenti direttamente sulle intestazioni dei campi, come nei menu contestuali richiamabili con il tasto destro del mouse.
Operare sui dati attraverso l’editor delle query
Cosa possiamo fare attraverso l’editor?
Come ho anticipato possiamo operare in molti modi. I più comuni sono i seguenti:
- rimuovere e filtrare campi e record,
- modellare campi e record,
- unire più basedati,
- rimuovere e sostituire valori o errori,
- modificare i valori, i tipi e i formati,
- scomporre e unire i campi,
- trasformare ed elaborare i dati,
- aggiungere campi calcolati con apposite funzioni,
- aggiungere colonne di dati personalizzate e condizionali.
Descrivere ogni comando e funzione dell’editor e il loro impiego pratico richiede un corso di una decina di ore, ma se vuoi ottenere il massimo dalle query in Excel, allora vale la pena imparare a utilizzarlo al meglio.
Vediamo alcuni esempi delle funzionalità.
Rimuovere e filtrare i dati
Una classica operazione che viene fatta per ottenere dalla basedati quello che ci interessa e nulla di più è rimuovere il superfluo. Per farlo selezioniamo e rimuoviamo i campi con i comandi appositi del menu contestuale o semplicemente premendo il tasto Canc.
Invece per rimuovere i record il modo più semplice è filtrare la tabella usando la classica finestra ordina e filtra richiamabile con il comando nell’intestazione della colonna.
Ripulire i dati
Per eliminare valori che non vogliamo vedere possiamo usare i filtri, ma tramite gli appositi comandi della barra multifunzione e del menu contestuale, richiamabile cliccando con il pulsante destro del mouse sopra l’intestazione del campo che ci interessa, possiamo sostituirli con altri valori. In modo analogo possiamo rimuovere e sostituire errori, zeri e valori vuoti (null).
Modificare i dati
Con appositi comandi possiamo scomporre una colonna in due o più colonne, o estrarne una parte, come il mese da una data, o una porzione di una stringa. Possiamo anche unire colonne e modificarne il formato, per esempio rendendo tutto maiuscolo o minuscolo.
Elaborare i dati
Inoltre possiamo usare le query per elaborare i dati estratti almeno in modo basilare, per esempio sommando o contando i record o individuandone il minimo o il massimo. Oppure possiamo aggiungere colonne con valori calcolati da altre colonne, per esempio sommandoli, oppure usando apposite funzioni.
Ma possiamo anche aggiungere colonne calcolate in modo condizionale, cioè estrarre valori da altre colonne a certe condizioni, oppure elaborandoli in presenza di altre condizioni, oppure restituendo valori definiti per altre condizioni ancora.
Power query non è uno strumento di elaborazione come formule o pivot, ma in molte situazioni permette di elaborare i dati direttamente per ottenere i risultati cercati, senza dover usare altri strumenti di elaborazione. Naturalmente per sfruttarne il pieno potenziale è necessaria una buona conoscenza dello strumento e una discreta esperienza.
E molto altro ancora
Quanto descritto finora è solo una parte delle potenzialità di questo strumento davvero notevole, che non solo estrae dati da una fonte ma permette di modellarli ed elaborarli come noi desideriamo.
Per esempio possiamo applicare funzioni statistiche, matematiche e trigonometriche in modo intuitivo. Come possiamo far modellare i dati da Excel fornendo esempi, oppure trasformare colonne in record e viceversa. E molto altro.
A chi serve Power query
Power query è uno strumento davvero notevole, ma apprenderlo richiede decine di ore di studio e pratica. Vale la pena spendere questo tempo? Per ottenere cosa? A chi serve davvero power query?
Power query è stato pensato per chi deve lavorare con i dati estratti da fonti esterne, di solito in ambito aziendale, come server Sql. D’altra parte offre numerose possibilità di impego in ambito più “locale” e può rivelarsi utile a utenti meno competenti e professionali.
Per esempio, per estrarre dati da altri fogli Excel, oppure da siti web o da file di testo, per unire tabelle diverse, per creare strumenti utili, come maschere di ricerca, e altro ancora. Considerato il potenziale di power query sarebbe come usare un elicottero per andare a fare la spesa sotto caso, ma in fondo lo strumento è a nostra disposizione, integrato in Excel.
Conclusioni
In questo post abbiamo introdotto power query, uno strumento tra i più evoluti di Excel con cui possiamo creare connessioni a basedati esterne per importarne i dati, ma non solo ci permette di modellare ed elaborare questi dati nel modo che desideriamo.
Per farlo offre un’ampia gamma di strumenti, tra cui l’editor visuale che permette di creare la nostra query, in fondo nient’altro che una stringa di testo destinata all’interprete che risponde alle nostre richieste. L’editor delle query ci consente di modellare i dati per ottenere il risultato da noi voluto e di farlo in modo comodo e intuitivo attraverso un ambiente familiare e centinaia di comandi.
Abbiamo visto che vale la pena imparare a usare power query se ci occupiamo di elaborazione di dati in ambito aziendale e professionale, in quanto ci permette di ottenere risultati che con strumenti tradizionale otterremmo solo con grandi competenze e molto più tempo.
Per questo consiglio di studiare questo strumento, per i risultati che offre e l’efficienza che dimostra.
A questa breve introduzione seguiranno altri post dedicati al power query e ai suoi utilizzi pratici per le nostre attività, che naturalmente ti invito a leggere.
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.
