Power Query ovvero come usare più velocemente Excel

Si supponga di avere a che fare con più insiemi di dati contenenti informazioni non omogenee. Si ipotizzi inoltre, come spesso accade, di dover effettuare delle elaborazioni usando dati che provengono da sorgenti disponibili in differente formato: fogli elettronici, file di testo, database e così via.

Grazie a Power Query l’unico lavoro importante che si dovrà svolgere è studiare bene la struttura dei dati e capire che cosa hanno in comune le varie sorgenti. Se il foglio di calcolo contenuto in un file Excel, un file di testo, la tabella di un database contenessero ad esempio uno stesso identificativo (ID) che punta alla stessa tipologia di dato (si pensi all’ID di un prodotto, di un ordine, di un cliente,…), allora con Power Query ed Excel sarà facilissimo mettere in correlazione origini di dati completamente differenti.

Un file di testo, comunque, anche quando non si avesse a che fare con un file in formato CSV consta comunque generalmente di separatori tra un’informazione e l’altra (possono essere semplici spazi, pipe (carattere |), segni di interpunzione o tabulazioni: Power Query è in grado di riconoscerle automaticamente e suddividere i dati in colonna.

Usando unicamente un foglio di calcolo Excel si può attingere alle varie origini di dati, mettere in correlazione i dati e realizzare un file che, addirittura, si aggiorna automaticamente allorquando i valori di partenza (contenuti nei vari file) dovessero cambiare.

Power Query può essere considerato come una sorta di intermediario tra vari origini di dati e un foglio di calcolo Excel che attinge a tale fonti per effettuare ogni genere di elaborazione.
Il sistema è dinamico nel senso che basta richiedere un semplice aggiornamento per adeguare il contenuto del foglio elettronico a seconda dei cambiamenti eventuali apportati ai file di origine.

Non solo. Dal momento che Power Query non lavora sulle origini dei dati e quindi non ne modifica né il contenuto né la struttura, si può creare un file Microsoft Excel contenente colonne non presenti nei file originali.
Con Power Query è possibile effettuare in tempo reale conversioni sui dati variandone la tipologia: un ID, ovviamente, non può essere una stringa di testo com’è quando viene estratta, per esempio, da un file di testo. Ricorrendo agli strumenti offerti da Power Query è possibile effettuare tutte le necessarie conversioni.

È inoltre semplicissimo estrarre porzioni di stringhe basandosi sui caratteri contenuti nel campo e inserirle in una nuova colonna. Il tutto senza mai dover mettere mano all’utilizzo di complesse formule.

Come usare Power Query con Excel

Per iniziare subito a utilizzare Power Query e rendersi conto dei benefici che introduce, basta avviare Excel, cliccare sul menu Dati quindi sul pulsante Nuova query.

Come si vede, è possibile effettuare query su tantissime origini di dati completamente diverse l’una dall’altra: si può addirittura attingere al contenuto di tabelle pubblicate sul web, usare tante basi di dati in differente formato, interfacciarsi con i servizi Microsoft Azure e con altri servizi online.

Per effettuare delle elaborazioni usando, intanto, il contenuto di un altro foglio di calcolo, basta cliccare su Da File, Da cartella di lavoro.

Facendo riferimento alla finestra che apparirà, si potrà scegliere il foglio da usare. Nel nostro caso, a titolo esemplificativo, stiamo lavorando su un file XLSX che contiene titoli di film e i corrispondenti identificativi (ID).

Cliccando su Trasforma dati, è possibile sfruttare tutta la potenza di Power Query per modificare eventualmente i dati di origine per metterli successivamente in correlazione con le informazioni provenienti da altre fonti.

Selezionando la prima colonna (ID) quindi cliccando su Trasforma, sarà possibile convertire tutti gli identificativi in numeri interi, ove necessario.

Con un clic sulla scheda Aggiungi colonna quindi su Estrai è addirittura possibile, come accennato in precedenza, creare una colonna con i valori derivati dal contenuto della colonna selezionata. Si possono ad esempio estrarre i caratteri iniziali o finali dei dati già presenti, oppure recuperare solo il testo che figura prima o dopo uno o più caratteri che fungono da delimitatori (ad esempio “,”, “-“, “;” ” ” ma anche “! !” e così via).

Nella colonna a destra Passaggi applicati, si possono trovare riassunte tutte le modifiche applicati ai dati di partenza: cliccando sull’icona a forma di “X” si potranno eventualmente annullarle.

Sempre usando l’editor di Power Query si potranno aggiungere altre colonne, effettuare calcoli, dividere le colonne già presenti e molto altro ancora.

Una volta terminato, basterà cliccare su Home quindi sulla freccia sotto al pulsante Chiudi e carica e infine su Chiudi e carica in.

Selezionando l’opzione Crea solo connessione, il foglio elettronico Excel conterrà soltanto un riferimento all’origine dei dati e memorizzerà tutte le preferenze e le richieste definite in precedenza con l’editor di Power Query.

Cliccando di nuovo su Dati, Nuova query, si immagini adesso di avere a disposizione un semplice file di testo che contiene gli ID dei film (senza i titoli) ma con le informazioni sul regista, sull’anno di produzione e qualche altro dato accessorio.
Con un clic su Da file, Da testo, specificando il file .TXT di partenza si otterrà un’anteprima del contenuto, automaticamente suddiviso per colonne.

Anche qui, cliccando su Trasforma dati si potrà controllare che la colonna ID sia numerica ed eventualmente apportare l’opportuna variazione (si può anche fare clic sull’icona “123” a sinistra del nome della colonna e scegliere Numero intero).

Al termine delle modifiche, si dovrà di nuovo selezionare Home, Chiudi e carica, Chiudi e carica in, Crea solo connessione.

Nella colonna Query cartella di lavoro, si può a questo punto cliccare con il tasto destro quindi scegliere Unione.

Cliccando sui nomi delle colonne in comune tra le due tabelle (nel nostro caso l’ID), Excel effettuerà l’operazione join che si utilizza abitualmente lavorando con i database relazionali.

Dopo aver effettuato le ultime variazioni, se necessario, cliccando su Chiudi e carica, si otterrà un foglio elettronico con i dati automaticamente combinati ed elaborati a partire da diverse sorgenti. È anche possibile creare una tabella pivot: Tabella pivot, cos’è e come utilizzare le funzioni avanzate di Excel.

Provando per esempio a modificare uno o più contenuti nei file che si usano come origine dei dati e cliccando su Aggiorna nella colonna di destra, le informazioni verranno automaticamente adeguate anche nel foglio elettronico e tutti i calcoli accessori impostati saranno di nuovo eseguiti.

Il tutto senza mai aver usato formule ed essersi ingegnati a realizzare complessi tool di conversione dei dati da un formato all’altro ricorrendo a funzioni e routine complicate.