Come utilizzare Power Query in Excel

Scopriamo come utilizzare al meglio Power Query in Excel, lo strumento principe per automatizzare l'estrazione, la pulizia, la trasformazione e il caricamento dei dati in Excel.

Microsoft Excel ha ampliato notevolmente le armi a nostra disposizione per lavorare sui dati negli ultimi anni (vedi Python, Copilot e non solo) e offre strumenti incredibilmente performanti per l'analisi dei dati che aiutano a migliorare la produttività e semplificano la vita degli analisti. Fra questi, probabilmente Power Query è ancora poco conosciuto, benchè sia compreso all'interno di Microsoft Excel ormai dal 2013.

Chiunque lavori con Excel dovrebbe saper utilizzare Power Query almeno a un livello basilare, dato che velocizza enormemente i processi manuali. L'ampia panoramica di questa guida ti aiuterà a capire meglio il contesto di riferimento, in particolare nel caso tu non abbia mai sentito parlare o utilizzato questo strumento in precedenza.

Se non hai mai visto Power Query, puoi fare una prova sul tuo PC seguendo queste semplici istruzioni:

• Apri un nuovo file di Excel
• Clicca su Dati > Recupera e Trasforma dati
• Seleziona un file Excel che conosci bene
• Clicca su Importa
• Seleziona i dati che ti interessano
• Clicca su Trasforma

A questo punto, si aprirà l'interfaccia di Power Query. La prima volta che la vedi potrebbe metterti un po' in difficoltà, quindi vediamo i singoli passaggi insieme, spiegando nel dettaglio il senso di questo strumento. Per darti un'idea dei benefici nell'utilizzarlo, ti segnalo tre esempi di casi reali in cui Power Query può tornare comodo, ma ce ne sarebbero davvero tantissimi:

1. Immagina di avere dati di vendita da diverse fonti, come file Excel, CSV e JSON. Con Power Query, puoi importare questi dati in pochi clic e pulirli e trasformarli in modo rapido e semplice, eliminando la necessità di utilizzare formule e funzioni complesse in Excel. Mettendoli tutti all'interno dello stesso file, puoi creare delle dashboard molto più velocemente e aggiornare le informazioni con intervalli regolari, minimizzando la possibilità di errori umani
2. Se lavori in campo HR o hai una tua attività, avrai sicuramente molte informazioni sui dipendenti, come i loro dati personali, i loro stipendi, la loro area di competenza e non solo. Anche in questo caso possiamo pulire velocemente i dati e aggregarli per creare delle statistiche che fino ad ora erano create solamente con formule (quali ad esempio la longevità in azienda). Avere un solo punto di entrata è particolarmente importante in questo caso, dato che sono informazioni sensibili che rischiano di creare problemi se fossero copiate e incollate senza un controllo preventivo
3. Chi è impiegato in uffici amministrativi ha a che fare tutti i giorni con voci quali acquisti, pagamenti e spese che potrebbero arrivare da Excel, CSV, PDF e non solo. Queste informazioni difficilmente arrivano dalla stessa sorgente, motivo per il quale si rende necessario un allineamento dei dati prima di poter fare delle statistiche dedicate. Anche in questo caso, Power Query può essere molto di aiuto per rivedere tutte le informazioni, ripulirle e caricarle su Excel evitando numerosi passaggi manuali

In sintesi: se hai un processo in Excel che richiede di ottenere dati, da poi lavorare e analizzare, Power Query è lo strumento che fa per te.

Per seguire la guida, puoi utilizzare un file qualunque - ma, se ne avessi bisogno, puoi scaricare da qui il file che vedrai nella maggior parte delle schermate seguenti.

Cos’è Power Query e perché utilizzarlo

Power Query è lo strumento di Microsoft che consente di importare, trasformare e caricare dati da diverse fonti all'interno di Excel, Power BI e altri prodotti Microsoft.

Power Query rivela particolarmente utile per l'automazione dei processi e la manipolazione dei dati senza codice. Si tratta di un motore di trasformazione e preparazione dei dati, che utilizza un'interfaccia grafica per le operazioni di estrazione, trasformazione e caricamento ETL (Extract - Transform - Load).

Power Query viene utilizzato principalmente perché permette di risparmiare tempo nella la gestione e pulizia dei dati, di automatizzare le operazioni ripetitive, migliorare la qualità dei dati riducendo gli errori, consentire l'uso anche a chi non ha competenze di programmazione.

Per accedere a Power Query, è sufficiente andare nella sezione Dati > Recupera e trasforma dati (su Windows), selezionando poi le sorgenti che più ci interessano.

I motivi per utilizzare Power Query in Excel sono molteplici, fra i quali:

• Automatizzare processi che richiedono l'integrazione di vari file diversi fra di loro (PDF, JSON, XLSX...)
• Velocizzare processi che richiedono di accorpare file con una struttura simile, dato che Power Query permette di accodare tutti i file presenti in una certa cartella
• Ridurre notevolmente la dimensione dei file di uscita, selezionando solo ciò che ci interessa veramente
• Rendere più solidi i nostri file, evitando tonnellate di formule poco comprensibili e sistemando i file in Power Query, anzichè in Excel
• Rivedere e ottimizzare i flussi esistenti in Excel prima di passare a un tool dedicato, come Power BI (che nasce da Power Query)

Prima di continuare, c'è una precisazione da fare - purtroppo, l'esperienza di Power Query su Windows non è paragonabile a quella su MAC oppure online. Vediamo meglio come mai.

Versioni e compatibilità di Power Query

Power Query è compatibile con diversi sistemi operativi, con alcune differenze a seconda della piattaforma:

• Windows (365, 2021, 2019, 2016, 2013 e Web): Power Query è integrato nel menu Dati sotto la voce Recupera e trasforma dati
• iOS / MAC : Power Query è disponibile solo per importare dati da CSV, TXT e file Excel. La trasformazione e la modellazione avanzata non sono ancora supportate, anche se esistono dei workaround

L'esperienza MAC non è paragonabile dato che mancano alcune opzioni importanti come la visualizzazione delle dipendenze query, la colonna da selezione e altre più avanzate. In linea di massima, il suggerimento sarebbe quello, per gli utenti MAC, di installare Parallels e poi di utilizzare Excel nel suo complesso, includendo Power Query, da lì. Power Query rappresenta anche la base per Power BI, che è lo strumento principe di Microsoft per la parte di Business Intelligence. Purtroppo, Power BI non è disponibile su MAC (e neanche Power Pivot).

Per quanto riguarda le fonti dati, Power Query ne supporta una vasta gamma, tra i quali ne citiamo alcuni a titolo di esempio:

• File: Excel, CSV, TXT, JSON, XML, PDF
• Database: SQL Server, MySQL, Oracle, PostgreSQL, Access. Alcuni di questi tramite ODBC
• Servizi Web e API: dati da siti Web, OData
• Cloud, Big Data: Azure, SharePoint, Dataverse, PowerBI, OneDrive

Alcuni di questi connettori possono variare a seconda della tua licenza di Excel, dipende se sono stati abilitati dal reparto IT. La logica comunque è che si possano ottenere dati da diverse fonti per poi manovrarle a piacimento in Power Query.

Interfaccia di Power Query

Power Query può essere utilizzato con un editor che permette di scrivere il codice, oppure con una fantastica interfaccia che semplifica il lavoro e permette di eseguire agevolmente un gran numero di operazioni, interagendo con un set di barre multifunzione, menu, pulsanti e altri componenti interattivi. L'interfaccia di Power Query è l'esperienza di preparazione dei dati principale, che permette di connettersi a molte fonti di dati ed eseguire trasformazioni fino alla forma desiderata.

Prima di cominciare a descrivere le funzionalità di Power Query quindi, esploriamo quindi l'ambiente in cui ci muoveremo.

Ci sono varie sezioni nell'interfaccia in Power Query che andremo a rivedere, nello specifico:

1. Barra multifunzione: la barra multifunzione fornisce più schede per eseguire trasformazioni, selezionare opzioni per la query e completare varie attività. Questa è la sezione principe per effettuare modificare in Power Query, dato che i pulsanti sono estremamente parlanti e semplici da ricordare
2. Riquadro Query: visualizzazione di tutte le query disponibili, con possibilità di creare, replicare, modificare ed eliminare altre query. Questa sezione ci aiuta a tenere un certo ordine nei nostri file, mappando tutte le query di partenza e quelle poi generate all'interno dello stesso file
3. Editor comandi in linguaggio M: Il linguaggio M è il linguaggio di trasformazione dei dati di Power Query. Se si desidera eseguire trasformazioni avanzate usando il motore di Power Query, è possibile usare l'Editor avanzato per accedere allo script della query e modificarlo come si desidera. Per fortuna, questi casi sono tendenzialmente molto rari, dato che scrivere M non è esattamente intuitivo
4. Visualizzazione corrente: la visualizzazione di lavoro principale, che per impostazione predefinita visualizza un'anteprima dei dati per la query. È anche possibile abilitare la visualizzazione diagramma insieme alla vista anteprima dati, che ci aggiungono altre dimensioni per vedere i nostri dati, usando la sezione Visualizza. Questa parte è fondamentale per vedere se ciò che stiamo costruendo corrisponda esattamente a ciò che ci aspettiamo, prima di passare a Excel

L'altra sezione fondamentale è quella che trovi sulla destra quando apri un file in Excel, chiamata Impostazioni Query.

Impostazioni Query in Power Query

Sul lato destro dell'interfaccia di Power Query troviamo infatti il riquadro Impostazioni Query. Quando si esegue una trasformazione con l'interfaccia di Power Query, viene creato automaticamente il codice corrispondente, visualizzabile in questsa sezione e nell'editor dedicato. Questa sezione è divisa in due: nella parte alta troviamo Proprietà con il nome della query e nella parte sotto troviamo Passaggi Applicati.

Nei passaggi applicati troviamo l'elenco completo di tutte le lavorazioni eseguite sui dati di partenza. Il primo dell'elenco è sempre chiamato Origine ed esegue l'import dei dati. Se usi la versione inglese, troveresti Source. Spesso si trova Modifica Tipo che riguarda la modifica del tipo di dato oppure il semplice riconoscimento del tipo di dato nel caso della prima acquisizione.

Questa sezione è fondamentale perchè ci permette di tracciare ogni singolo passaggio effettuato sulla query. In questo caso vediamo che cosa è successo sul nostro file prima di modificarlo:

1. Step1, Origine > abbiamo ottenuto il database
2. Step2, Modificato tipo > Power Query ha modificato il tipo di dati delle singole colonne della query. Questo passaggio è automatico in Power Query, anche se può essere rimosso
3. Step3, Rimosse colonne > abbiamo rimosso delle colonne del database
4. Step4, Filtrate righe > abbiamo filtrato delle righe

Quando clicchi su uno degli step, vedrai l'anteprima dei dati in quello specifico step. Questo ci rende molto più semplice la parte di individuazione degli errori (debugging), dato che potremo vedere velocemente dove sono effettuate delle modifiche e agire di conseguenza.

Di default, ogni passaggio riprende quello precedente. Come noti dall'immagine, =Table.SelectRows(#"Rimosse colonne"... vuol dire che questo step sta riutilizzando le informazioni dello step precedente, Rimosse colonne, compiendo poi le operazioni che stiamo decidendo di eseguire.

Puoi anche effettuare altre modifiche relative agli step, in questa sezione:

• Con il tasto destro, si apre un menu contestuale per rinominare i singoli step e molto altro
• Cliccando sulla X che trovi a sinistra del nome, puoi cancellare uno step. Puoi anche cancellare gli step intermedi, ma in questo caso Power Query ti farebbe comparire un alert per accertarsi che cancellare lo step sia proprio l'azione che stai provando a compiere
• Puoi inserire step intermedi all'interno della query. Cliccando su Modificato tipo ed eseguendo un'azione, questa andrebbe a costituire un nuovo step fra Modificato tipo e Rimosse colonne. Anche in questo caso, ti apparirebbe un alert
• Puoi modificare l'ordine degli step semplicemente spostandoli in alto o in basso, ma bisogna fare attenzione perchè un diverso ordine potrebbe invalidare la query stessa

Rimane sempre utile visualizzare la parte di Impostazioni perchè è la bussola di un file che magari non conosciamo, dato che traccia, a differenza di Excel, tutti i passaggi necessari per passare dall'origine del dato al file finale.

Ora che sappiamo com'è composta l'interfaccia di Power Query, possiamo passare all'azione.

Le fasi di lavoro in Power Query

Power Query è un software ETL, come detto, quindi ha tre fasi che sono fondamentali per comprendere meglio il flusso generale:

• Estrazione: in questa fase, ci colleghiamo a sorgenti dati creando varie connessioni. Questi dati rimangono immutati nel loro percorso originale, ma saranno la base del nostro processo
• Trasformazione: successivamente, i dati saranno trasformati in vari modi per arrivare a diventare la base dati delle nostre analisi e considerazioni, utilizzando gli strumenti propri di Power Query
• Caricamento: i dati saranno poi caricati altrove, tendenzialmente all'interno di un file Excel, in diverse modalità

Vediamole nello specifico.

Estrazione dati in Power Query: come connettersi alle fonti

Il primo passo per cominciare ad utilizzare Power Query è realizzare una connessione a una fonte dati.

È possibile usare Power Query per importare una singola origine dati, ad esempio una tabella, un database, un feed o servizi sparsi nel cloud. Le origini dati includono dati del Web, file, database, Azure o persino tabelle di Excel nella cartella di lavoro corrente. Con Power Query, è quindi possibile riunire tutte queste origini dati usando trasformazioni e combinazioni univoche.Dopo l'importazione, è possibile aggiornare i dati per aggiungere, modificare o eliminare dati dall'origine esterna.

Come connettersi a una tabella Excel con Power Query

Nel caso più semplice di utilizzo per Power Query si potrebbe importare una tabella presente nello stesso file Excel.

Basterà andare nella scheda Dati, nella sezione Recupera e trasforma dati e cercare il simbolo della Tabella.

La tabella viene immediatamente importata nell'ambiente di lavoro di Power Query dove viene mostrata un'anteprima del dataset.

Oltre all'import dei valori della tabella, Power Query ha già riconosciuto l'intestazione e soprattutto ha già identificato il tipo di dato contenuto all'interno di ciascun campo: Int64, text, number, ecc...

Nel riquadro Query sulla sinistra è comparso anche il nome di questa prima operazione di import identificata col nome della tabella (TBL_ORSI).

Come connettersi a un file esterno con Power Query

Più in generale però, usando Power Query, potremmo avere bisogno di realizzare una connessione dati a un'origine esterna, che può essere scelta in un'ampia gamma di possibilità che includono file Excel, PDF, database, addirittura un'intera cartella di file. In questo caso la cosa migliore da fare è selezionare Recupera dati nella solita sezione e poi selezionare l'opportuna tipologia di origine. Noi supporremo per semplicità di connetterci a un file Excel.

Una volta individuato il file bisogna selezionare l'elemento da importare (nel nostro caso una tabella).

A questo punto bisogna cliccare Trasforma dati per ritrovarsi nell'ambiente di Power Query pronti per lavorare. In linea di massima, non è consigliabile cliccare su Carica perchè questo comando non fa altro che importare i dati in Power Query, senza però effettuare alcun tipo di modifica.

Le sorgenti esterne disponibili e i connettori variano a seconda delle licenze, ma puoi sperimentare in automatico anche solo usando un file Excel, come in questa guida. Considera che comunque, al netto del file che sceglierai, l'interfaccia in Power Query sarà sempre la stessa. Quindi, che tu abbia un CSV, un PDF o un JSON come partenza, potrai eseguire le operazioni che desideri sfruttando gli stessi pulsanti in Power Query.

Le trasformazioni principali di Power Query in Excel

In Power Query puoi effettuare numerose trasformazioni semplicemente cliccando col tasto destro su una delle intestazioni o su un singolo record. In questo caso viene mostrato un elenco delle principali operazioni eseguibili, come Sostituisci valori, Rimuovi duplicati, Rinomina e molte altre.

Per un approccio strutturato alle trasformazioni, facciamo una panoramica delle singoli sezioni in Power Query che trovi in alto non appena accedi a questa nuova interfaccia.

Il menu Home in Power Query

Vediamo quali sono le principali operazioni che possono essere eseguite nel menu Home, che è il primo che troviamo quando apriamo un file. In generale, il cuore del lavoro in Power Query è costituito dalle Trasformazioni, dato che la possibilità di filtrare e ordinare valori, creare, modificare ed eliminare colonne è proprio tutto quello che vogliamo realizzare.

Il motore di trasformazione in Power Query include molte funzioni di trasformazione predefinite che possono essere usate tramite l'interfaccia grafica del editor di Power Query. Alcune sono molto semplici, come la rimozione di una colonna o il filtro delle righe o l'uso della prima riga come intestazione di tabella. Sono disponibili anche opzioni di trasformazione avanzate, come Merge, Append, Raggruppa, Pivot e Unpivot. Le opzioni sono quasi sterminate, ma daremo una preview di ciò che puoi fare in Power Query.

L'anteprima dei dati viene aggiornata automaticamente ogni volta che si applicano delle modifiche al set di dati, ma se desideri forzare un aggiornamento manuale dell'anteprima è sufficiente cliccare su Aggiorna anteprima.

Trale operazioni principali troviamo sicuramente Filtra e Ordina. Queste operazioni possono essere eseguite semplicemente cliccando sulla freccina come mostrato nella figura. Ti troverai sicuramente a tuo agio con questi comandi perchè sono molto simili a quelli che troviamo in Excel.

Cliccando su questi comandi, vedrai che il database cambierà in automatico e, come anticipavamo, ci sarà un nuovo step nella parte di destra nella sezione chiamata Impostazioni query, che riassume tutti i passaggi effettuati sui nostri dati di partenza.

Le sezioni Gestisci colonne e Riduci righe sono fra le possibilità che troviamo nella scheda Home, dato che possiamo rimuovere colonne e righe in vari modi. Un elemento di attenzione, in questo caso, è la differenza fra Scegli colonne e Rimuovi colonne:

• Scegli colonne è preferibile e ti permette di indicare le colonne che vuoi tenere (Anno, Specie, Dimensione), che dichiari in modo esplicito. In questo caso, se il database iniziale avrà nuove colonne (ad esempio Carattere e Dieta), queste non saranno caricate all'aggiornamento dei dati
• Rimuovi colonne elimina delle colonne che non ti interessano (ad esempio Peso e Numero). Riprendendo l'esempio precedente, se il database iniziale dovesse presentare nuove colonne (Carattere e Dieta), queste sarebbero adesso parte del tuo database

La sezione Trasforma riassume numerose operazioni abbastanza tipiche in Excel, quali la divisione dei dati in diverse colonna (Dividi colonna), la sostituzione di valori e non solo. Ti capiterà abbastanza spesso di usare il comando Usa la prima riga come intestazione, parecchio utile se la prima riga ha le intestazioni della tabella che ti interessa analizzare.

Un altro comando sottovalutato in Power Query è Raggruppa per, che permette di riassumere interi database in un solo passaggio - qui trovi una guida dedicata proprio a come usare Raggruppa per in Power Query in Excel, con un esempio pratico da scaricare.

La schermata Home ha anche due fra i comandi più utili in assoluto in Power Query, ovvero Merge e Append (Accoda). In entrambi i casi puoi decidere se creare una nuova query tramite il menu contestuale oppure se proseguire con la stessa query - io ti consiglio di creare una nuova query per avere più chiarezza nel file (Unisci query come nuova oppure Accoda query come nuove). Vediamole nello specifico:

• Merge permette di combinare due tabelle in base a una o più colonne chiave comuni e crea una nuova query partendo da quelle esistenti. Questa nuova query contiene tutte le colonne di una tabella principale, con una colonna che funge da collegamento di spostamento a una tabella correlata. La tabella correlata contiene tutte le righe corrispondenti a ogni riga di un valore di colonna comune nella tabella principale. Inoltre, è possibile espandere o aggiungere colonne da una tabella correlata in una tabella principale. Puoi usare diversi tipi di join, mutuati da quanto vedresti in SQL. Questo comando è un po' la versione moderna di CERCA.VERT o CERCA.X in Excel, dato che ci permette di collegare una tabella (Fatturato 2024 che ha un ID per cliente) con un'altra (Clienti, dal quale possiamo recuperare nuove informazioni avendo l'ID in comune). Qui trovi una guida ampia dedicata proprio a come usare al meglio merge di query in Power Query su Excel
• Accoda permette di accodare delle sorgenti dato che abbiano delle intestazioni uguali. Questo comando è provvidenziale se abbiamo dati che vogliamo consolidare, come dati finanziari di diversi anni oppure dati di fatturato per regione che vogliamo riassumere in una sola visualizzazione. Qui trovi una guida dedicata a un caso pratico su come accodare le query in Power Query in Excel, se vuoi approfondire

Le operazioni eseguibili sono moltissime e vanno sicuramente esplorate, anche se molte di queste ricordano le operazioni disponibili in Excel. Il consiglio è quello di esplorare questa sezione approfonditamente, prima di passare alla successiva.

Il menu Trasforma in Power Query

La seconda scheda che incontriamo è Trasforma, dove è possibile eseguire un gran numero di operazioni direttamente sulle colonne che abbiamo importato nel nostro database. Il consiglio è sempre quello di prima selezionare la colonna che ci interessa, per poi effettuare la modifica che desideriamo.

All'interno di Trasforma sono presenti molti comandi tra cui:

• Raggruppa per: viene utilizzato per aggregare dati in base a una o più colonne chiave. È pensato per eseguire delle operazioni di aggregazione (somma, media, conteggio, ecc.). Questo comando è replicato da Home e, come detto, è molto potente dato che permette di riassumere i dati velocemente, evitando di importare database enormi
• Sostituisci valori: viene utilizzato per modificare i valori di una colonna, sostituendo uno o più valori specifici con altri valori. È particolarmente utile quando è necessario correggere errori nei dati, standardizzare valori o rimuovere valori non desiderati (lo trovi anche in Home)
• Colonna pivot: viene utilizzato per trasformare i dati in un formato più utile e strutturato, spostando i valori di una colonna nelle intestazioni delle nuove colonne. Trasforma le righe in colonne in base ai valori di una colonna chiave, facilitando l'analisi e la visualizzazione dei dati
• Unpivot: viene utilizzato per trasformare i dati con formato wide (dati orizzontali, con molte colonne) in un formato long (dati verticali, con meno colonne), ossia per trasformare le colonne in righe. Può tornare utile con una tabella che ha molte colonne con variabili correlate (ad esempio, valori di diverse categorie o periodi temporali) e bisogna riorganizzare i dati per un'analisi - un caso tipico sono dati finanziari con intestazioni pari a un singolo mese, che sono particolarmente problematici da utilizzare

Il menu Trasforma non crea, di per sè, nuove colonne in Power Query. Per compiere operazioni di questo tipo, possiamo andare nella sezione Aggiungi Colonna.

Il menu Aggiungi Colonna in Power Query

Successivamente incontriamo la scheda Aggiungi Colonna. Questa contiene molti comandi utili tra cui i più importanti riguardano la possibilità di aggiungere delle colonne. La colonna aggiunta può contenere un indice, un'operazione matematica (per es. una somma), una condizione loigca, oppure può essere qualcosa di più complesso che può essere realizzato grazie alla colonna personalizzata.

I comandi che vediamo nello specifico sono colonna da esempi, la colonna condizionale e la colonna personalizzata:

• Colonna da esempi permette di selezionare delle colonne che ci interessano per poi inserire un risultato che ci aspettiamo. Ad esempio, se selezioni la colonna ANNO e inserisci in una cella, nella nuova colonna, 11 anzichè 2011, Power Query creerà uno snippet di codice M per ottenere il risultato. Rispetto a creare da zero la formula, è sicuramente molto più semplice e veloce

• Colonna personalizzata consente di creare nuove colonne personalizzate utilizzando espressioni e formule scritte in M Language, il linguaggio di scripting di Power Query. È uno strumento molto potente che permette di calcolare e derivare nuovi valori in base ai dati esistenti, applicando logiche più complesse o personalizzate. Torna molto utile quando hai bisogno di creare calcoli che non sono facilmente realizzabili tramite altre trasformazioni predefinite, o quando desideri eseguire operazioni che richiedono una logica complessa, come concatenare stringhe, calcolare differenze tra date, condurre calcoli matematici o applicare funzioni personalizzate.
• Colonna condizionale è uno strumento che permette di aggiungere una nuova colonna basata su condizioni logiche. È simile a una formula SE in Excel: è possibile definire delle condizioni in base alle quali inserire determinati valori

Sul lato destro trovi altri comandi utili che permettono di aggiungere nuove colonne a seconda di alcune condizioni. Se hai delle colonne con delle date, puoi cliccare su Da data e ora e recuperare, ad esempio, i dati del trimestre.

Il menu Visualizza in Power Query

La scheda Visualizza consente di cambiare l'impostazione di visualizzazione dei dati e soprattutto di visualizzare le dipendenze query, cioè lo schema delle trasformazioni eseguite sui dati.

I comandi possibili come al solito sono molti ma noi focalizzeremo la nostra attenzione solo su alcuni:

• Colonna qualità è uno strumento utilizzato per analizzare e monitorare la qualità dei dati all'interno di una colonna, assegnando etichette che aiutano a identificare potenziali problemi nei dati, come valori mancanti, duplicati, fuori intervallo o altri errori, facilitando il processo di debug. La colonna distribuzione permette inoltre di creare un grafico intuitivo per visualizzare la distribuzione
• Editor Avanzato è uno strumento che consente di visualizzare e modificare direttamente il codice scritto nel linguaggio M di Power Query. Utilizzando l'Editore Avanzato, puoi avere un controllo completo sulle trasformazioni dei dati e scrivere o modificare manualmente le query, offrendo maggiore flessibilità e potenza rispetto alle operazioni disponibili tramite l'interfaccia grafica.
• Dipendenza Query viene utilizzato per visualizzare e gestire le relazioni tra le diverse query in un progetto di Power Query. Questo comando ti aiuta a comprendere come le query sono collegate tra loro, mostrando le dipendenze tra le query sorgente e quelle derivate, e quindi come i cambiamenti in una query possano influire sulle altre. Se lavori con i modelli dei dati, questo strumento è particolarmente utile

A questo punto, dovremmo aver estratto, trasformato e rivisto i dati prima di caricarli. Vediamo come funziona il caricamento dei dati in Power Query.

Come caricare i dati da Power Query a Excel

Quando siamo soddisfatti delle nostre operazioni di pulizia e trasformazione, possiamo finalmente caricare i nostri dati da Power Query al nostro file Excel.

Nella sezione Home troveremo il menu Chiudi e ci potrebbe venire la tentazione di cliccare direttamente su Chiudi e carica. In questo caso, Power Query carica direttamente le nostre query in Excel creando nuovi fogli con tabelle che abbiano lo stesso nome della nostra query. Per chiarire meglio - se una query si chiama TBL_SALES e clicchiamo su Chiudi e carica, avremo sul nostro file un nuovo foglio chiamato TBL_SALES che contiene una tabella chiamata TBL_SALES.

Il menu contestuale Chiudi e carica in... è decisamente più interessante:

Vediamo le opzioni disponibili:

• In una tabella replica esattamente i passaggi di Chiudi e carica. Avremo una nuova tabella in un nuovo foglio ed entrambi avranno il nome della nostra query
• In un rapporto di tabella pivot carica direttamente i nostri dati in una tabella pivot. L'aspetto fondamentale di questa possibilità è che possiamo caricare anche dati che vanno oltre al limite canonico di righe in Excel (1048576), quindi è un'opzione perfetta se i nostri dataset di partenza sono enormi
• Grafico pivot è un'opzione un po' strana che ho visto utilizzare davvero di rado, dato che carica i dati non in pivot ma direttamente in un grafico
• Crea solo connessione non carica direttamente i dati in Excel, ma li tiene sul file solo come connessione. Questa opzione è importante per avere sul nostro file delle sorgenti che non ci interessa caricare, ad esempio tabelle di trasformazione oppure step intermedi. Se, ad esempio, carichi in Power Query i dati per 2023, 2024, 2025 e 2026, non ti interesserà vederli singolarmente ma solo un MASTER che riassuma i 4 anni. In questo caso, caricheresti solo MASTER, tenendo le altre 4 tabelle annuali solo come connessione
• Aggiungi questi dati al modello di dati ti permette di caricare i dati all'interno di un modello di dati in Power Pivot. Questa funzionalità è fondamentale se hai database molto grandi, dato che puoi creare un modello dei dati e non una tabella "master" troppo grande da gestire. Se lavori abitualmente con Power Pivot, puoi passare abbastanza facilmente a traslare gli stessi concetti anche su PowerBI

Puoi anche scegliere se caricare i dati in un foglio di lavoro esistente, ma te lo sconsiglio, oppure in un nuovo foglio di lavoro.

I dati, come detto, possono essere caricati direttamente in un foglio dedicato oppure caricati solo come connessione, cioè non caricati in un foglio ma solo elaborati in background. Questa funzionalità può essere comoda per collegare fra di loro diverse tabelle all'interno dello stesso file Excel, senza però caricare tutte le tabelle singolarmente.

Esistono due modi principali per caricare le query nella cartella di lavoro:

• Da Editor di Power Query è possibile usare i comandi Chiudi e Carica del gruppo Chiudi della scheda Home.
• Nel riquadro Query della cartella di lavoro di Excel, selezionare Query & Connessioni, è possibile fare clic con il pulsante destro del mouse su una query e scegliere Carica in. Ne parliamo fra poco

Vediamo che cosa succede una volta che abbiamo caricato la nostra query in Excel.

Come modificare una query di Power Query in Excel

Abbiamo cariato la nostra query da Power Query in Excel, ma ora vogliamo modificarla. Come possiamo fare?

Come notiamo, aprendo la sezione Dati > Query e connessioni vedremo un menu contestuale che ci indica tutte le query del nostro file, in questo caso TBL_ORSI che ha 44 righe caricate sulla nostra tabella. Questo menu può essere spostato dove preferisci e puoi anche variarne la dimensione, nel caso ti servisse.

Se clicchi con il tasto destro, vedrai un menu contestuale con numerose operazioni possibili quali, fra le altre:

• Copiare e incollare una query
• Modificare la query, tornando in Power Query per sistemarla come preferisci. La stessa operazione è possibile cliccando semplicemente due volte sul nome della query - anche in questo caso, andrai di nuovo in Power Query
• Rinominare la query
• Aggiornare la query
• Modificare la destinazione della query, utilizzando Carica in...

C'è una specifica tecnica che voglio anticiparti, nel caso volessi utilizzare i comandi Duplica e Riferimento:

• Duplica creerà una nuova query che sarà una copia esatta della query precedente, ripetendo tutti i singoli passaggi dall'inizio alla fine. Il rischio è quello di rendere il file molto pesante, ma potrebbe essere comunque necessario
• Riferimento creerà una nuova query che sarà uguale all'ultimo step della query precedente. In questo caso, la query sarà molto rapida ma bisognerà fare attenzione per evitare errori, perchè ci portiamo dietro tutti i processi di trasformazione precedenti

Come aggiornare una query di Power Query in Excel

L'unico tasto visibile nella sezione Query e connessioni sono le due frecce che ci permettono di aggiornare la query di Power Query. Se clicchiamo sul pulsante, la nostra query partirà di nuovo e farà le stesse operazioni che abbiamo definito prima all'interno di Power Query. Questo tipo di operazione velocizza enormemente i nostri processi di lavoro, dato che considererà anche tutti i nostri nuovi dati, se le sorgenti dati presentano nuove informazioni.

Se volessimo aggiornare tutte le query in un colpo solo, dovremmo andare in Dati > Query e connessioni > Aggiorna tutti.

Per comprendere l'importanza dell'aggiornamento in Power Query, assumiamo per esempio di avere un file di vendita dove effettuiamo le seguenti operazioni:

• Prendiamo i dati di vendita da uno scarico CSV e li colleghiamo con un file Excel relativo alla produzione
• Modifichiamo i dati per renderli coerenti negli anni
• Creiamo statistiche personalizzate per 5 persone in azienda, che vogliono vedere diversi KPI
• Mettiamo insieme grafici da varie pivot

Se non usi Power Query, dovrai continuamente lavorare con dei copia / incolla faticosi, nonchè pericolosi. Se usi Power Query, l'intero processo si ridurrà a:

• Scaricare i dati aggiornati di vendita e della produzione
• Aggiornare la query

Con Power Query, impiegherai molto meno tempo e avrai anche la possibilità di rivedere tutte le operazioni a codice, risparmiando tempo prezioso.

Torniamo però a un tema importante, cioè il Riquadro query in Power Query, che ti tornerà utile quando inizierai a dare forma e struttura ai tuoi file.

Riquadro Query in Power Query

Come anticipato, sul lato sinistro dell'interfaccia di Power Query troviamo lo strumento che ci permette di manipolare le diverse query create, chiamato Riquadro Query. Qui troverai tutte le query, i parametri, le costanti, i gruppi e le funzioni personalizzate del tuo file, aiutandoti a districarti sugli elementi più importanti per il file attualmente utilizzato.

Selezionando la singola query, vedrai i dati sulla destra relativi alla query stessa. Questa sezione è estremamente intuitiva e ha vari comandi che possono tornare utili, quali Duplica, Riferimento, Elimina e Proprietà. Un dettaglio interessante è la possibilità di creare gruppi di query tramite il comando Sposta nel gruppo, utile per riorganizzare le query in gruppi tematici evitando eccessiva confusione sul tuo file.

M, il linguaggio utilizzato in Power Query

Power Query funziona con un linguaggio di programmazione chiamato M. Il motore di Power Query usa questo linguaggio di scripting in background per tutte le trasformazioni di Power Query ed è il motivo per il quale vedrai sempre, sopra ai tuoi dati, del codice generato in automatico da Power Query stesso.

Se si desidera eseguire trasformazioni avanzate usando il motore di Power Query, è possibile usare l'Editor avanzato per accedere allo script della query e modificarlo come si desidera. Se pensi che le funzioni e le trasformazioni dell'interfaccia utente non eseguano le modifiche necessarie, si può usare l'Editor avanzato e il linguaggio M per ottimizzare funzioni e trasformazioni. Non è fondamentale impararlo, ma bisogna sapere come ragiona il motore di trasformazione per poter apportare qualche modifica.

Ti faccio un esempio di possibile query:

let
Source = Excel.CurrentWorkbook(){[Name = "TBL_ORSI"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Anno", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type", {{"Dimensione", Order.Ascending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Specie] = "Grizzly"))
in
#"Filtered Rows"

Trovo che M sia decisamente più difficile da scrivere che da leggere, dato che:

• Ogni passaggio è indicato dal nome che trovi sul lato destro in Impostazioni query
• Se conosci l'inglese, Table.TransformColumnTypes trasforma il tipo di dato della colonna, Table.Sort la ordina, Table.SelectRows seleziona solo alcune righe a seconda di alcune condizioni. Magari non sarà intuitivo, all'inizio, ma con l'esperienza ti verrà sicuramente più semplice capire i singoli passaggi
• Il meccanismo a step aiuta la comprensione, dato che in query semplici come questa ogni passaggio riprende il precedente

L'utilizzo di M diventa importante per comandi complessi, mentre la maggior parte delle operazioni eseguite in Power Query vengono eseguite tramite l'interfaccia grafica, quindi non ti preoccupare se vedi il codice generato - è molto improbabile che tu debba modificarlo in solitaria!

Conclusioni

Saper utilizzare bene Power Query è una skill fondamentale per chi lavora con Excel, ma questo strumento è ancora poco conosciuto all'interno delle aziende ed è un vero peccato. Power Query è anche in PowerBI, fra l'altro, quindi la gran parte del lavoro svolto in Power Query in Excel può anche essere replicato, se necessario, anche su PowerBI, ottimizzando i passaggi tecnici e permettendo di creare visualizzazioni stupende.

Spero che la guida possa ispirarti a modificare i tuoi file attuali e i tuoi processi di lavoro - non ti resta che sperimentare e avere un po' di pazienza, soprattutto all'inizio, ma ti assicuro che l'investimento fatto su questo strumento ti porterà tantissimi benefici!