You’ve got the Power! Automazione al top con Power Query (e qualche astuzia in linguaggio M)

Nel mondo della gestione dei dati, l’efficienza e la precisione sono cruciali, specialmente quando si lavora con grandi volumi di informazioni. Microsoft Power Query è un potente strumento in grado di compiere operazioni di ETL (Extract – Transform – Load), ossia estrarre i dati dalle sorgenti più disparate, trasformarli ed elaborarli come meglio si desidera e poi caricarli in un luogo dove poterli analizzare. Power Query è alla base del funzionamento di Microsoft Power BI ma, benché in posizione non troppo visibile, è già da molti anni presente anche all’interno di Excel per cui non dovrai installare nulla di nuovo e potrai già cominciare ad utilizzarlo.

Uno dei principali vantaggi di Power Query è la possibilità di modificare il linguaggio M (da lui automaticamente generato) per personalizzare le query, ossia l’insieme di operazioni da svolgere che conduce al risultato finale, generalmente una tabella. Conoscere anche solo un minimo di linguaggio M ti consentirà di sbloccare un mondo di possibilità, permettendoti di adattare le query esattamente alle tue esigenze senza ritrovarti a dire “vorrei, ma non posso…”.

Il caso di studio di questo articolo

In questo articolo ti mostrerò come importare in una singola tabella pivot tutti i dati contenuti in un unico file Excel ma distribuiti in numerosi fogli di lavoro, il cui numero potrebbe variare di volta in volta. Questa esigenza di consolidamento è comune a molti scenari, tanto più considerando come le tabelle pivot necessitino di un’unica fonte di dati coerente per poter sintetizzare, analizzare e presentare efficacemente le informazioni. Vedrai come in questa situazione Power Query ti permetterà da un lato di automatizzare l’intero processo, risparmiando tempo prezioso e riducendo al minimo gli errori manuali, dall’altro di poter rieseguire istantaneamente l’operazione quando arriveranno nuove versioni del file, contenenti i dati più aggiornati.

Come puoi osservare dall’immagine, il file contiene un foglio per ogni comune nella provincia di Frosinone e all’interno di ognuno di essi i dati hanno tutti la medesima struttura in termini di numero e ubicazione delle colonne. Il primo foglio è chiamato ALATRI, cui seguono in ordine alfabetico gli altri fogli con i relativi comuni, fino a VILLA SANTA LUCIA. A questo foglio ne segue uno contenente i dati di persone non residenti in un comune specifico (foglio SENZA COMUNE DI RESIDENZA) e un ulteriore foglio chiamato INFO ESTRAZIONE contenente informazioni sul gestionale che ha prodotto i dati, la data di estrazione e l’utente che l’ha eseguita (ovviamente quest’ultimo foglio andrà ignorato al momento di consolidare i dati fra loro). Ogni elenco può essere costituito da un numero variabile di righe; ipotizza anche che, se per un comune non esistono dati, non esisterà neanche il relativo foglio.

Trattandosi di molti fogli (usando la funzione FOGLI() potresti scoprire che sono ben 58) è “molto sconveniente” eseguire il consolidamento a mano, senza contare l’elevata probabilità di commettere errori di copia e incolla; utilizzerai quindi Power Query, richiamandolo da Excel visto che l’obiettivo è produrre una tabella pivot (in Power BI Desktop dovresti altrimenti produrre un oggetto visivo Matrice).

La parola a Power Query!

Ti consiglio di sviluppare la soluzione all’interno di un file Excel vuoto piuttosto che direttamente dentro al file con i dati, dal momento che esso è destinato ad essere sostituito nel tempo con versioni mano a mano più aggiornate e quindi in caso contrario ogni volta dovresti iniziare da capo.

Connettiti al file con i dati cliccando su Dati|Recupera e trasforma dati|Recupera dati|Da file|Da Excel cartella di lavoro. Nella finestra che comparirà dovrai selezionare il luogo da dove prelevare i dati: ogni foglio selezionato genererà una query distinta dalle altre e quindi selezionando tutti i fogli genererai altrettante tabelle separate. Questo approccio è da evitare non solo perché lungo (otterresti 57 tabelle distinte che dovresti poi alla fine accodare) ma soprattutto perché dipende dai dati attuali: se in futuro il file non contenesse più un determinato foglio, tutto il processo andrebbe in errore e i dati non verrebbero aggiornati; se in futuro il file contenesse un foglio in più o con un altro nome, esso verrebbe ignorato e con lui i dati contenuti.

Come soluzione scegli un foglio a caso (es: il primo, “ALATRI”) e clicca su Trasforma dati per entrare nell’editor di Power Query.

Nel pannello Impostazioni query di destra sono elencati tutti i passaggi eseguiti dalla query, che partendo dal file esterno conduce alla tabella che vedi al centro dello schermo; questi passaggi sono stati aggiunti in automatico da Power Query ma non sei certo obbligato a conservarli così come sono o a mantenerli. Prova a cliccare sul primo passaggio (Origine) e poi sequenzialmente sui successivi per vedere come i dati cambino e la barra della formula mostri la formula in linguaggio M che conduce a quel risultato a partire dal precedente. Il primo passaggio si occupa di aprire il file Excel esterno ed elencare gli oggetti in esso contenuti (fogli, nomi definiti e tabelle, così come leggi nella colonna Kind); se nella colonna Data clicchi sulla parte bianca a destra della parola Table potrai vedere in basso un’anteprima del contenuto di quel particolare foglio. Il secondo passaggio (Navigazione) si occupa di scegliere tra tutti gli oggetti quello che tu hai selezionato all’inizio: tu vuoi prendere i dati da tutti i fogli contemporaneamente e non solo da uno, quindi rimuovi tutti i passaggi da qui in poi (clicca sulla X rossa a sinistra di ognuno di essi oppure più velocemente seleziona il passaggio Navigazione, clicca con il tasto destro del mouse e scegli Elimina fino alla fine).

L’ultima riga della tabella fa riferimento al foglio “INFO ESTRAZIONE” dal quale non vuoi importare alcunché: per eliminarla clicca su Home|Riduci righe|Rimuovi righe|Rimuovi ultime righe ed inserisci il valore 1 prima di confermare. In alternativa potresti eliminare la riga filtrando la colonna Name (come faresti normalmente in Excel) e togliendo la spunta accanto a “INFO ESTRAZIONE”: questo metodo è pericoloso perché, pur funzionando oggi, cesserebbe di farlo se un domani l’ultimo foglio cambiasse nome (per esempio, un aggiornamento nel software che esporta i file potrebbe cambiare la lingua e cominciare a creare fogli chiamati “EXPORT INFO”). Meglio quindi basarsi sul fatto che il software accoda sempre questo foglio riassuntivo in ultima posizione per eliminare la singola riga qualunque sia il suo nome (tra l’altro questa operazione è più veloce perché un filtro viene valutato sempre su ognuna delle righe e queste, in altri contesti, potrebbero essere centinaia di migliaia).

I dati desiderati sono contenuti tutti nei singoli oggetti Table che si trovano dentro alla colonna Data ma tu vuoi conservare anche l’informazione sul comune cui fanno riferimento: seleziona la prima colonna (Name), premi Ctrl e clicca sulla colonna Data per aggiungerla alla selezione, richiama il menu contestuale e lì scegli Rimuovi altre colonne in modo da sbarazzarti di tutto ciò che non è utile.

La colonna Data contiene tabelle, quindi mostra a destra del proprio nome l’icona di espansione: premila per accedere alla finestra di dialogo e lì lascia tutte le colonne selezionate e deseleziona la voce Usa il nome della colonna originale come prefisso (altrimenti i nomi finali delle colonne saranno inutilmente lunghi). Come vedi, Power Query espande ogni tabella orizzontalmente e, dovendolo fare anche verticalmente, si occupa di ripetere più volte il contenuto della prima colonna (Name) in modo da non lasciare alcuna riga con dati mancanti.

Verifiche e correzioni finali

Hai quasi terminato: non resta che rimuovere tutte le 57 righe duplicate che contengono le intestazioni originarie delle colonne e assegnare un nome sensato a queste ultime. Per eliminare le righe duplicate scegli una colonna a piacere tra le ultime 5 e nel relativo filtro deseleziona la parola in questione (es: “CODICE” in colonna 2). Per rendere la soluzione più robusta e non basarti sul nome delle colonne (in futuro potrebbero cambiare?) potresti invece assegnare il tipo di dato Data alla colonna con le date di nascita e poi cliccare su Home|Riduci righe|Rimuovi righe|Rimuovi errori così da eliminare tutte le righe che prima contenevano un testo, impossibile da convertire in data.

Per assegnare i nomi alle colonne hai 2 strade alternative.

La prima è fare doppio clic sui nomi da modificare (o cliccare Rinomina dal menu contestuale) e scegliere i nuovi nomi. Banale quanto efficace (devi però essere sicuro che nelle future versioni del file le colonne rimangano sempre in quell’ordine).

La seconda strada, specialmente se sei abituato ad usare Power Query e a notare cosa fa lui in automatico, è quella di rimandare ad un secondo momento il filtraggio delle righe duplicate per sfruttare quelle stesse informazioni e usare la prima riga come intestazioni colonna: ti basta scegliere l’omonimo comando presente in Home|Trasforma o in Trasforma|Tabella (Power Query aggiungerà anche un ulteriore passaggio Modificato tipo). Soluzione meno banale della precedente ma con la prerogativa di mantenere nomi e posizioni delle colonne così come presenti nel file di origine, dovessero in futuro cambiare. La prima colonna mostrerà il nome di un comune (quello del primo foglio di calcolo, ad es. “ALATRI”) per cui dovrai rinominarla in qualcosa di più sensato, ad es. in “COMUNE”: è qui che si nasconde l’insidia. Come puoi notare guardando la barra della formula, Power Query scrive

= Table.RenameColumns(#”Modificato tipo”,{{“ALATRI”, “COMUNE”}})

Ossia cerca la colonna che si chiama ALATRI per rinominarla: e se un domani il file Excel non contenesse dati per quel comune e quindi il foglio fosse assente? L’intero processo si bloccherebbe con un errore: saresti costretto a modificare la query; la volta successiva magari dovresti correggerla ulteriormente perché i dati di ALATRI in quel momento sono tornati di nuovo presenti: ti ritroveresti continuamente a correggere una soluzione in Power Query che dovrebbe invece farti risparmiare tempo ed energie.

Puoi risolvere questo problema con una rapida modifica della formula scritta da Power Query, sostituendo al posto di “ALATRI” (virgolette incluse):

Table.ColumnNames(#”Modificato tipo”){0}

La funzione Table.ColumnNames() infatti genera una lista contenente i nomi delle colonne della tabella tra parentesi (nel tuo caso la tabella generata al passaggio precedente); la parte {0} in calce si occupa di estrarre da tale lista il primo elemento (nel linguaggio M si comincia a contare dallo 0, quindi 1 rappresenta il secondo elemento, 2 il terzo,…) ossia il nome della prima colonna, qualsiasi esso sia! Una minima conoscenza di linguaggio M ha reso la tua query molto più robusta e i tuoi futuri sonni tranquilli!

In realtà manca un ultimo passaggio fondamentale: quello con cui dichiari esplicitamente il tipo di dato di ogni singola colonna. Power Query potrebbe già averlo inserito, anche più volte (soprattutto all’inizio o se hai promosso la prima riga ad intestazione) ma è sempre bene assicurarsi che ce ne sia uno definitivo finale (e magari eliminare i precedenti, se non necessari a trasformazioni successive particolari). L’importante è che, alla fine, sulla sinistra di ogni intestazione colonna non vi sia alcuna icona ABC123, che rappresenta il tipo di dato sconosciuto (Any), perché ciò potrebbe causare problemi in fase di successivo utilizzo e analisi dei dati.

Generazione della tabella pivot

Non ti resta ora che caricare in Excel i dati così consolidati in modo da poter costruire la tua tabella pivot. Se premi Home|Chiudi|Chiudi e carica Power Query creerà in Excel una Tabella con il risultato della query e da lì potrai generare la tabella pivot come faresti normalmente.

Ma perché sprecare memoria? Ti interessa veramente vedere tutte le righe con il dettaglio dei dati consolidati? Puoi invece dire a Power Query di non riversare i dati in Excel ma di creare una tabella pivot che legga i dati direttamente dalla query! Per fare questo esci da Power Query scegliendo Home|Chiudi|Chiudi e carica in e lì seleziona In un rapporto di tabella pivot.

Tieni presente che se hai già chiuso Power Query con Chiudi e carica e vuoi cambiare idea, questa scelta risulterà non più selezionabile da Power Query ma potrai farla esclusivamente dal menu contestuale della query elencata nel pannello Query e connessioni (o dalla scheda dinamica Query che appare cliccando dentro alla Tabella).

Quando avrai una nuova versione del file Excel contenente i dati più aggiornati, ti basterà sovrascrivere il file obsoleto e nel file Excel con la tabella pivot premere Ctrl+Alt+F5 (oppure selezionare la tabella pivot e premere Analisi tabella pivot|Dati|Aggiorna|Aggiorna tutti. Farà tutto in automatico Power Query!

E se volessi farlo con una formula Excel?

Consolidare i dati mediante formule Excel rappresenta una sfida impegnativa.

Per semplificare il problema, potresti accettare copiare e incollare manualmente la prima riga con il nome delle colonne e di accodare i dati presenti in tutte le colonne senza aggiungere quella con il nome della località: in questo caso potresti sfruttare i riferimenti 3D e la funzione STACK.VERT() per impilare verticalmente tutte le singole matrici con i dati in una unica.

Dovendo operare in blocco su tutti i fogli assieme, il primo problema che si pone è quello di decidere il numero fisso di righe da copiare in ogni foglio: tante a sufficienza da includere tutte le persone del foglio con l’elenco più esteso ma non troppe da rallentare l’operazione né tantomeno causare un errore #NUM! per il superamento del numero massimo di righe di un singolo foglio (1048576 nei file con formato .xlsx o peggio 65536 nei file salvati nel vecchio formato .xls, che potrebbe però essere quello utilizzato dall’applicativo che esporta i dati!). Per evitare stime (e quindi continui controlli ad ogni nuovo file pervenuto) potresti creare una funzione personalizzata in VBA che passi in rassegna ogni foglio, conti il numero di celle piene in colonna A e restituisca il numero massimo di tali conteggi.

Il secondo problema è che molti fogli conterranno poche righe valorizzate e quindi la matrice dinamica finale risultato dell’impilamento sarà piena di blocchi di righe vuote (in realtà contenenti zeri) tra i dati di un comune e quelli del successivo. Fortunatamente puoi risolvere utilizzando la funzione FILTRO() impostando come criterio l’assenza di celle vuote. La formula finale, stimando di non avere elenchi con più di 500 righe, potrebbe quindi essere la seguente:

=FILTRO(STACK.VERT(‘ALATRI:SENZA COMUNE DI RESIDENZA’!A2:E500);

            STACK.VERT(‘ALATRI:SENZA COMUNE DI RESIDENZA’!A2:A500)<>””)

Se invece nella tabella consolidata volessi creare anche la colonna contenente il nome del comune, le cose si complicherebbero molto perché anche impiegando la formula

=TESTO.SUCCESSIVO(CELLA(“nomefile”);”]”)

per estrarre il nome dei fogli, essa verrebbe calcolata sempre nel medesimo foglio che ospiterà la tabella consolidata, fornendo sempre lo stesso risultato e rendendo il tentativo vano. Tanto varrebbe scrivere direttamente una macro in VBA…

In qualunque caso, riuscissi anche a progettare una soluzione ingegnosa, verrebbero comunque meno i vantaggi di Power Query relativi a poter aggiornare con un click i dati all’arrivo di nuove versioni del file. Dovresti infatti ogni volta aprire il nuovo file e riscrivere (o magari, meglio, incollare) integralmente tutte le formule, avendo però cura di verificarne il buon funzionamento nel caso in cui i nomi del primo e ultimo foglio cambiassero o in qualche foglio ci fossero più righe del numero massimo stimato la volta precedente. Sicuramente, poi, il numero totale delle righe sarebbe diverso e quindi dovresti ridefinire l’intervallo dati che alimenta la tabella pivot. Pensa al tempo perso se dovessi anche aggiungere la funzione in VBA e/o lanciare delle macro!

Conclusioni

Microsoft Power Query è uno strumento indispensabile per chi lavora con dati provenienti da file Excel complessi e variabili. La sua capacità di automatizzare e semplificare la gestione dei dati, combinata con la potenza e la flessibilità del linguaggio M, lo rende una risorsa inestimabile per analisti di dati, sviluppatori e professionisti IT.

Investire tempo nell’apprendimento di Power Query e del linguaggio M può portare a significativi risparmi di tempo e miglioramenti nell’accuratezza delle analisi, senza contare che i concetti che apprenderai potranno essere applicati tanto in Excel quanto in Microsoft Power BI, così che una eventuale espansione verso quest’ultima piattaforma sia più fluida e veloce, conoscendone già lo strumento di ETL.

Per un supporto più esaustivo e guidato su Power Query e Power BI puoi seguire i nostri corsi Microsoft Power BI Desktop e Microsoft Power Query Expert oppure richiedere un percorso formativo personalizzato: contattaci per maggiori informazioni!