Come utilizzare i parametri in Power Query su Excel

In questa guida vediamo come utilizzare i parametri in Power Query su Excel per migliorare la leggibilità dei nostri file, utilizzando informazioni dinamiche per effettuare calcoli e recuperare dati.

Utilizzeremo un file CSV preso da Maven Analytics (il file si chiama Video Game Sales) ma puoi usare anche un tuo file per seguire passo passo, il ragionamento sarà esattamente lo stesso.

Questa guida è pensata per chi vuole creare file Excel più intelligenti, dinamici e interattivi grazie a Power Query. Vedremo insieme, passo dopo passo, come usare i parametri per:

• Gestire il percorso del file CSV in modo flessibile
• Filtrare i dati in base a una cella di Excel
• Automatizzare l’intero processo di aggiornamento

L'obiettivo è costruire una dashboard che si aggiorna automaticamente quando l’utente modifica una cella in Excel. Il tutto usando Power Query e due semplici parametri:

• Uno per indicare dove si trova il file dati CSV
• Uno per scegliere la console di gioco (es. PS2, PS3), dato che parliamo di videogiochi in questo caso

Vediamo come fare, partendo dal nostro file in CSV. Il file che trovi nella guida è preso da Maven Analytics, che ha una sezione estremamente fornita di database sui quali fare esperimenti. Chiaramente, puoi utilizzare anche un tuo file personale, seguendo i passaggi che più ti interessano.

---

Come importare un file CSV in Power Query

Prima di tutto, dobbiamo importare il nostro file CSV in Power Query. Apriamo un nuovo file Excel e andiamo su Dati > Recupera dati > Da file > Da testo/CSV per poi selezionare il file che ci interessa

Il file che stiamo importando si chiama vg-chartz-2024, quindi lo selezioniamo. Nel nostro caso, è sul Desktop.

Dopo l’importazione, viene mostrata una finestra di anteprima del contenuto. Qui è importante visualizzare i dati per essere certi che siano effettivamente quelli che ci interessano. Power Query ci offre già un'idea di come i dati saranno caricati in Excel, migliorando già estremamente la gestione di CSV rispetto ad aprirli in Excel per poi fare Testo in colonne.

In linea di massima, cliccheremo sempre su Trasforma dati in questa sezione, in modo da aprire Power Query e rivedere i dati prima di caricarli sul nostro file.

Fin qui, niente di particolarmente importante. Power Query aggiunge lo step di Modificato tipo (o Changed Type) e noi cambiamo il nome della query, che sarà TBL_VIDEOGAMES.

Clicchiamo su Chiudi e carica, dato che ci interessa la tabella (che è lo standard in Power Query).

Come notiamo, la tabella finale ha oltre 64.000 righe. Ma che cosa succede se i dati dovessero aumentare in maniera sostanziale, o se dovesse cambiare la posizione del nostro file di origine?

Andiamo a selezionare la nostra query da Dati > Query e connessioni, per poi modificarla.

L'obiettivo sarà quello di creare un parametro dinamico, in modo da evitare di toccare il codice M della query, per creare un percorso dedicato al file di estrazione che stiamo utilizzando.

Come creare un parametro in Power Query su Excel

Vogliamo creare un parametro in Power Query perchè il nostro file potrebbe trovarsi in una posizione diversa e vogliamo evitare che le persone debbano utilizzare il codice.

In particolare, qui parliamo di un caso semplice - ma immaginati di avere 30 query che partono tutte da questo file! Cambiarle a mano, una ad una, sarebbe uno spreco di tempo enorme.

Nella query precedente, troverai la sezione Origine o Source, dove ci sarà un certo percorso dal quale vengono pescati i dati. Se non hai usato un CSV, sarà comunque un codice simile, ma qui vediamo:

= Csv.Document(File.Contents("C:\Users\marco\Desktop\vgchartz-2024.csv"),[Delimiter=",", Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None])

Questa è la parte cruciale, che vogliamo rendere dinamica. Se il dato fosse in Download, o in un'altra cartella, dovremmo modificarla da qui - ma vogliamo velocizzare il nostro processo.

Ti consiglio di copiare il percorso del tuo file, per poi riutilizzarlo dopo nel parametro.

Per creare un nuovo parametro, dalla schermata Home puoi cliccare su Gestisci parametri > Nuovo parametro.

A questo punto possiamo scegliere il nome del parametro, in questo caso SORGENTE_DATI, oltre al valore corrente. Nel valore corrente, non dovrai fare altro che copiare e incollare il percorso del file precedente, senza le virgolette.

Power Query creerà il nuovo parametro, che troverai sotto la query attuale.

Il passaggio delicato è questo: dovrai andare nella query precedente e rimuovere il percorso file attuale. Attenzione in particolare alle virgolette ", dato che non ci serviranno per il parametro scelto.

L'autocomplete ci segnala che SORGENTE_DATI è stato riconosciuto e possiamo quindi inserirlo direttamente nella nostra query, che sarà così:

= Csv.Document(File.Contents(SORGENTE_DATI),[Delimiter=",", Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None])

Il passaggio diventa: Power Query riconosce che c'è il parametro SORGENTE_DATI > va a pescare il percorso dati che abbiamo inserito, non più impresso direttamente nel codice. Questo, come detto, aggiunge una dimensione dinamica alle nostre query.

A questo punto, noterai che la query funziona correttamente e che i dati caricati sono gli stessi precedenti.

La grande differenza, però, è che potrai modificare il percorso del file direttamente dal parametro SORGENTE_DATI, anzichè intervenire sul codice! Avrai quindi molto più controllo di quello che succede all'interno della tua query, permettendoti di cambiare velocemente il percorso del file.

A riprova di quanto abbiamo appena fatto, nota come anche nell'Editor avanzato, raggiungibile da Home > Editor avanzato, sia evidente la modifica. SORGENTE_DATI è il file utilizzato e non abbiamo alcuna indicazione sul percorso precedente, nel codice. Questa logica può essere utilizzata in numerosi contesti, anche recuperando file che non sono CSV oppure in ambienti cloud come Sharepoint.

Aggiungiamo un elemento di complessità, dato che vogliamo filtrare il nostro database utilizzando un valore inserito dall'utente sul file Excel. In questo caso, la console scelta dall'utente avrà un impatto sulla nostra query, restituendo esclusivamente le righe corrispondenti.

Come collegare una cella Excel a un filtro in Power Query

Mettiamo di voler creare un filtro che parte da una cella in Excel, da riutilizzare poi in Power Query. Benchè non sia propriamente un parametro, il ragionamento è simile dato che utilizzeremo questo valore per filtrare poi i valori della query, riducendo il numero di righe corrispondenti nella TBL_VIDEOGAMES che caricheremo sul nostro file.

Inserendo, nella cella B3 il valore PS2, andiamo poi nella casella nome a definire il nome CONSOLE_SCELTA. Ciò vuol dire che, ovunque andiamo sul nostro file Excel, potremo digitare CONSOLE_SCELTA per ottenere il valore corrispondente in B3 - in questo caso, PS2.

Selezionando la cella B3, possiamo cliccare su Dati > Da tabella/intervallo e portare in Power Query il nostro nome definito, CONSOLE_SCELTA. La struttura funziona perchè Power Query considera, come tabella/intervallo, diverse possibilità quali tabelle in Excel, dynamic array e nomi definiti, proprio come CONSOLE_SCELTA.

Vedrai una visualizzazione simile a questa, dove abbiamo Column1 e sotto il valore che vogliamo recuperare. Chiaramente, Column1 non è interessante per noi, quindi possiamo cliccare su PS2 e poi selezionare Drill-down. In questo modo, ridurremo il risultato al solo valore PS2.

Come noti, l'icona di riferimento cambia, perchè stiamo recuperando solamente un valore testuale (PS2) da CONSOLE_SCELTA.

Il codice M generato è il seguente:

let
Source = Excel.CurrentWorkbook(){[Name="CONSOLE_SCELTA"]}[Content],
Column1 = Source[Column1]{0}
in
Column1

Come nel passaggio precedente, dobbiamo poi legare questo valore (CONSOLE_SCELTA) alla query TBL_VIDEOGAMES.

Il modo più semplice è tornare sulla TBL_VIDEOGAMES per poi cliccare su console e selezionare esclusivamente PS2, giusto per visualizzare che cosa sta succedendo. Come noti, nella colonna console abbiamo solo dati per PS2:

= Table.SelectRows(#"Changed Type", each [console] = "PS2")

Tuttavia, per il momento, questo valore è fissato a sistema. Noi vogliamo che sia dinamico.

Come nel caso precedente, dobbiamo rimuovere la parte fissa e renderla dinamica, sostituendo, a PS2, CONSOLE_SCELTA:

= Table.SelectRows(#"Changed Type", each [console] = CONSOLE_SCELTA)

In questo modo, permettiamo all'utente di selezionare la console che desidera e di restituire unicamente i valori che vuole visualizzare. Se un utente inserisce PS3 e poi aggiorna le query, avrà solo i dati per PS3. Se inserisce 3DS, avrà solo i dati per 3DS, e così via.

Dato che non ci interessa caricare il nostro valore di CONSOLE_SCELTA, dovrai cliccare su Chiudi e carica in > Crea solo connessione, così da evitare doppioni.

Come noti, per PS2 avremo 3'565 righe caricate, riducendo notevolmente la dimensione del database complessivo.

A questo punto, il nostro file ha due elementi importanti per renderlo dinamico:

• Il parametro SORGENTE_DATI recupera un file velocemente, inserendo semplicemente il nuovo percorso file
• Il nome CONSOLE_SCELTA riduce la dimensione del file in maniera dinamica, aiutando l'utente

Cambiano il valore in PS3 e aggiornando le query, noteremo che il numero di righe saranno 1'905. Puoi usare questa strategia ogni volta che vuoi che un utente possa scegliere in autonomia un valore che impatta poi sulle query, mantenendo pieno controllo sulla query stessa.

Vediamo anche qualche potenziale grattacapo da verificare, in modo da implementare questa logica senza difficoltà sui tuoi file.

Come risolvere potenziali problemi con i parametri in Power Query

Dato che stiamo lavorando con variabili dinamiche, potrebbe capitarci di avere qualche problema lavorando con i parametri in Power Query. Vediamoli singolarmente:

DataSource.NotFound: File or Folder: Il file 'C:\Users\marco\Desktop\vgchartz-2024.csv' non è stato trovato.
Dettagli: C:\Users\marco\Desktop\vgchartz-2024.csv

Se colleghiamo il parametro a un percorso errato, dove non ci sono dati, la query non funzionerà. Dovrai quindi rivedere il percorso corretto per far funzionare correttamente la query.

Expression.Error: Non è stata trovata una tabella Excel denominata 'CONSOLE_SCELTA'.
Dettagli: CONSOLE_SCELTA

Se cambi il nome del riferimento, ad esempio da CONSOLE_SCELTA a CONSOLE_SELEZIONE, oppure se lo cancelli, troverai un errore come questo. Dovrai verificare che cosa sia successo nel tuo file di partenza

Se non hai nessun risultato, molto spesso il problema è dovuto al mancato Drill-down dei dati. In questo caso, come noti, CONSOLE_SCELTA è una tabella, anzichè essere un singolo valore indicato con ABC come icona sul lato sinistra nel pannello query.

Ti aggiungo anche un caso che ho trovato particolarmente utile in passato, pensando ai parametri - cioè come utilizzare Power Query e utilizzare un parametro quando lavoriamo con dati che sono su Sharepoint.

Come utilizzare un parametro di Power Query con Sharepoint

In scenari aziendali, è comune lavorare con dati archiviati su SharePoint o OneDrive e Power Query è un alleato prezioso per velocizzare il recupero dei dati. Tuttavia, bisogna impostare correttamente il lavoro dall'inizio per poi evitare problemi successivamente.

Supponiamo che tu voglia accedere a una libreria documenti da SharePoint usando Power Query. Invece di scrivere l’URL a mano, possiamo parametrizzarne una parte (es. il dominio aziendale).

STEP1 - Creiamo il parametro DOMINIO_SHAREPOINT andando in Power Query > Gestisci parametri > Nuovo parametro:

• Nome: DOMINIO_SHAREPOINT
• Tipo: Testo
• Valore: https://xyz.sharepoint.com

Chiaramente dovrai sostituire un valore corretto anzichè xyz nella sezione Valore, ma il senso è lo stesso. Attenzione anche al tema autenticazione, dato che in genere dovrai entrare con un account aziendal per poter poi agganciare Sharepoint.

STEP 2 - A questo punto, puoi creare una nuova query vuota cliccando sul pannello Query > Tasto destro in uno spazio vuoto > Nuova query > Altre Origini > Query vuota.

Nel codice in alto potrai inserire:

= SharePoint.Files(DOMINIO_SHAREPOINT, [ApiVersion = 15])

Vedrai quindi tutti i file inseriti nel dominio che hai indicato in precedenza. Puoi anche lavorare con Sharepoint.Contents, nel caso volessi andare più veloce o ti servisse una catalogazione più complessa prima di vedere i singoli file.

STEP 3 - Una volta ottenuta la lista dei file, puoi filtrare per percorso, estensione o nome file esattamente come faresti con Folder.Files o File.Contents.

Questo approccio è perfetto per:

• Ambienti multi-tenant (es. sviluppo vs produzione), soprattutto se lavori a stretto contatto con molte persone
• File salvati in SharePoint, Teams, OneDrive, dato che il processo è lo stesso
• Dashboard aziendali condivise, in particolare se ci sono molte persone coinvolte

Se riesci a gestire anche percorsi cloud con parametri, potrai creare file che usano Power Query in maniera sempre più efficiente.

Conclusione

Utilizzare parametri in Power Query in Excel diventa fondamentale quando lavoriamo con progetti più complessi. Dato che non possiamo dare per scontato che i dati sorgenti siano sempre nella stessa posizione, aiutarci con parametri di diverso tipo può aiutarci a spiegare meglio il nostro flusso di lavoro, oltre che aggiornare i nostri dati senza troppi intoppi, una volta chiuso il progetto.

Abbiamo visto come caricare un CSV in Excel in maniera dinamica, prendendolo come parametro, per poi filtrarlo con un valore inserito in una cella, inserendo anche alcuni problemi comuni riscontrati con i parametri e un breve accenno al mondo cloud, dove Sharepoint è ottimo per recuperare file sempre rimanendo in ambiente Microsoft.

Ti consiglio di rivedere uno dei tuoi file di lavoro, dove Power Query è un componente fondamentale, per poi creare un parametro e aggiornare le singole query - vedrai come migliorerà sia la velocità, sia la comprensione del tuo file!