Come creare una funzione personalizzata in Power Query su Excel

In questa guida vediamo come creare una funzione personalizzata in Power Query su Excel.

Power Query, come sappiamo, è lo strumento perfetto, interno a Excel, per modulare la preparazione dei dati, permettendoci di velocizzare numerose operazioni di pulizia prima di passare alla parte di analisi vera e propria. In questo contesto, agiscono le cosiddette funzioni personalizzate, che hanno diversi vantaggi per automatizzare varie procedure dato che:

• racchiudono una sequenza di passaggi che può essere richiamata su tabelle con la stessa struttura
• abbrevianno le modifiche future, poichè cambi il codice in un solo punto, evitando di fare debugging estesi
• sfruttano al massimo il query folding (se usi Power BI), riducendo tempi di refresh quando la fonte lo consente
• rendono documentabile e riutilizzabile il tuo processo e "pensiero", aiutando la comprensione di file utilizzati da varie persone, come in template aziendali

In questo caso, faremo un test utilizzando un boxscore di ESPN dedicato a una partita di basket NBA, dove i Denver Nuggets hanno battuto i Los Angeles Clippers 120 a 101, in gara 7 (playoff 2024-205). Chiaramente puoi utilizzare qualsiasi sorgente dati, ma questo è un buon punto di partenza per fare qualche test.

L'idea è la seguente:

• Ottenere i nomi dei singoli giocatori di ogni squadra
• Ottenere i dati per ognuno dei giocatori
• Agganciare i nomi delle squadre coinvolte
• Unire le informazioni di giocatori + dati in una tabella finale

Utilizzeremo le funzioni personalizzate in due modi diversi:

• Nel primo caso, prenderemo i dati per i Los Angeles Clippers e creeremo una funzione personalizzata per seguire gli stessi step anche per i Denver Nuggets
• Nel secondo caso, creeremo una funzione che prende due tabelle diverse (nomi dei giocatori e dati collegati) e le accoda orizzontalmente, similarmente a come agisce la funzione STACK.ORIZ

Per chiarire ancora meglio il processo, ogni team avrà:

• TBL_TEAM: con i nomi dei giocatori
• SCORE_TEAM: con i dati sui giocatori
• TEAM_#: con il nome della squadra
• TBL_TEAM_COMPLETE: con nomi dei giocatori, dati sui giocatori e nome della squadra

Le due tabelle TBL_TEAM_COMPLETE si uniranno poi nella tabella finale, TBL_COMPLETE. La differenza è che alcuni dei passaggi di trasformazione dei Denver Nuggets, come vedi dall'immagine, saranno realizzati con funzioni personalizzate che si basano sullle query relative ai Los Angeles Clippers.

Cominciamo!

Come creare una funzione personalizzata su Power Query in Excel

Vediamo come creare una funzione personalizzata in Power Query, partendo dalla tabella relativa ai Los Angeles Clippers. Dobbiamo sistemare la visualizzazione iniziale per arrivare a un elenco utilizzabile di giocatori e il codice M utilizzato è il seguente:

let
Source = Web.BrowserContents("https://www.espn.com/nba/boxscore/_/gameId/401768066"),

#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "DIV.Wrapper:nth-child(1) > DIV.Boxscore.flex.flex-column > DIV.ResponsiveTable.ResponsiveTable--fixed-left.Boxscore.flex.flex-column > DIV.flex > TABLE.Table.Table--align-right.Table--fixed.Table--fixed-left > * > TR > :nth-child(1)"}}, [RowSelector="DIV.Wrapper:nth-child(1) > DIV.Boxscore.flex.flex-column > DIV.ResponsiveTable.ResponsiveTable--fixed-left.Boxscore.flex.flex-column > DIV.flex > TABLE.Table.Table--align-right.Table--fixed.Table--fixed-left > * > TR"]),

#"Filtered Rows" = Table.SelectRows(#"Extracted Table From Html", each ([Column1] <> "" and [Column1] <> "bench" and [Column1] <> "starters" and [Column1] <> "team")),

#"Colonna personalizzata aggiunta" = Table.AddColumn(#"Filtered Rows", "Player", each let splitColumn1 = Splitter.SplitTextByDelimiter(". ", QuoteStyle.None)([Column1]), splitsplitColumn11 = Splitter.SplitTextByDelimiter("#", QuoteStyle.None)(splitColumn1{1}?) in Text.Combine({Text.Start([Column1], 1), ". ", Text.Combine(List.Alternate(splitsplitColumn11, 1, 1, 1), ".")}), type text),

#"Removed Other Columns" = Table.SelectColumns(#"Colonna personalizzata aggiunta",{"Player"})

in

#"Removed Other Columns"

La parte che ci interessa riutilizzare per la nostra funzione sarà la seguente:

#"Filtered Rows" = Table.SelectRows(#"Extracted Table From Html", each ([Column1] <> "" and [Column1] <> "bench" and [Column1] <> "starters" and [Column1] <> "team")),

#"Colonna personalizzata aggiunta" = Table.AddColumn(#"Filtered Rows", "Player", each let splitColumn1 = Splitter.SplitTextByDelimiter(". ", QuoteStyle.None)([Column1]), splitsplitColumn11 = Splitter.SplitTextByDelimiter("#", QuoteStyle.None)(splitColumn1{1}?) in Text.Combine({Text.Start([Column1], 1), ". ", Text.Combine(List.Alternate(splitsplitColumn11, 1, 1, 1), ".")}), type text),

#"Removed Other Columns" = Table.SelectColumns(#"Colonna personalizzata aggiunta",{"Player"})

in

#"Removed Other Columns"

In sostanza, la query compie i seguenti passaggi:

• Prende il valore della pagina come se fosse un vero e proprio browser
• Ci permette di pescare la tabella che ci interessa, navigando tramite l'editor integrato
• Recupera solo alcune righe, filtrando ed evitando i valori vuoti o poco interessanti. La nostra funzione comincia qui
• Crea una nuova colonna con il nome dei giocatori sistemato, dato che è poco corretto nella visualizzazione iniziale (ad esempio Derrick Jones Jr.D. Jones Jr.#55 diventa D. Jones Jr.)
• Prende esclusivamente i nomi sistemati dei giocatori

Per migliorare ancora di più la comprensione, avremmo potuto creare un parametro in Power Query, ma non è il focus della guida.

Chiameremo questa funzione GetPlayersFromTable, per la quale recuperiamo il codice precedente. Vogliamo, infatti, creare una funzione personalizzata che ci permetta di effettuare le stesse, identiche operazioni, anche per i Denver Nuggets.

Il problema è - come passiamo dagli step precedenti alla nuova funzione?

Come passare da query a funzione personalizzata in Power Query su Excel

Creare una funzione personalizzata significa, spesso, passare da una query in Power Query a una vera e propria funzione dedicata. In questo caso, dobbiamo pensare a come sono strutturati i dati che stiamo passando a Power Query per replicare il processo con i Denver Nuggets, cioè una tabella. Possiamo chiamare la tabella inputTable.

Per creare una nuova funzione, dovremo cliccare con il tasto destro nel pannello query > Nuova Query > Altre origini > Query vuota. Chiameremo la query proprio GetPlayersFromTable, come detto. A questo punto, dovremo:

• Definire l'input che l'utente dovrà inserire per replicare il processo
• Rivedere il codice precedente, riprendendo l'input per riuscire a replicare i singoli passaggi
• Verificare il risultato

Separeremo la parte di input da quella di output e la nuova query sarà un po' diversa, dato che inizierà proprio con l'indicazione del nostro input:

= (inputTable as table) as table =>

let
// Step 1: Filtra righe non utili
FilteredRows = Table.SelectRows(inputTable, each ([Column1] <> "" and [Column1] <> "bench" and [Column1] <> "starters" and [Column1] <> "team")),

// Step 2: Crea colonna "Player" estraendo e ricombinando parti di testo
AggiuntaColonna = Table.AddColumn( FilteredRows, "Player", each let splitColumn1 = Splitter.SplitTextByDelimiter(". ", QuoteStyle.None)([Column1]), splitsplitColumn11 = Splitter.SplitTextByDelimiter("#", QuoteStyle.None)(splitColumn1{1}?) in Text.Combine({Text.Start([Column1], 1), ". ", Text.Combine(List.Alternate(splitsplitColumn11, 1, 1, 1), ".")}), type text ),

// Step 3: Tieni solo la colonna "Player"
SoloPlayer = Table.SelectColumns(AggiuntaColonna, {"Player"})

in SoloPlayer

Iniziamo con = (inputTable as table) as table => che significa: l'utente inserirà una inputTable che definisce in autonomia ed è una tabella. L'output sarà una tabella, mentre => indica che, da quel punto in poi, cominciano i calcoli e finisce la parte di input, ricalcando la creazione delle funzioni LAMBDA in Excel, che puoi utilizzare per evitare di dover ripetere tante volte gli stessi calcoli.

In pratica, stiamo dicendo che avremo una nuova funzione che prenderà il valore inputTable (inserito dall'utente), che è una tabella e che va trattata come tale. Come noti, infatti, il primo comando sarà Table.SelectRows, che funziona esclusivamente se stiamo lavorando con una tabella. Il passaggio decisivo, in questo caso, è definire quali sono gli input che l'utente dovrà inserire e modificare poi gli step al fine di incorporare gli input dell'utente, proprio come fossero dei parametri.

Da lì, si tratterà solo di copiare e incollare i passaggi già effettuati a sistema, magari aggiungendo un commento, come in questo caso, per semplificare la comprensione. Se hai dimestichezza, oltre che con LAMBDA, anche con la funzione LET in Excel, il processo logico è simile: stiamo dando un nome a un valore variabile, semplificando poi i calcoli successivi.

Come noti, nel pannello Query, le funzioni hanno un'icona dedicata con fx, diversamente da tabelle o parametri.

Come detto, stiamo semplificando il processo per far partire il flusso non dall'inizio (dalla pagina web) bensì da quando abbiamo la prima tabella già "quasi" pronta all'uso, benchè ancora da risistemare. Come noti, infatti, la tabella relativa ai Denver Nuggets, TBL_NUGGETS, non è ancora pulita, avendo dati che si ripetono.

Anzichè replicare tutti i passaggi precedenti, possiamo velocizzare la procedura di pulizia riutilizzando la funzione precedente, GetPlayersFromTable.

Vediamo l'ouput della nostra nuova funzione utilizzando proprio la TBL_NUGGETS. Quando creiamo una funzione personalizzata, avremo infatti una nuova visualizzazione dove ci sarà chiesto il parametro che vogliamo inserire (inputTable) e poi, cliccando su Richiama, vedremo la nostra funzione in azione.

Così facendo, come noti, si creerà una nuova query chiamata Richiamata funzione, dove il codice è piuttosto semplice da interpretare, essendo in un solo step:

= GetPlayersFromTable(TBL_NUGGETS)

In pratica, Power Query prenderà tutti i passaggi che abbiamo definito nella nostra funzione personalizzata e li ripeterà anche per la nostra TBL_NUGGETS, non più TBL_CLIPPERS. Come noti, i nomi sono ora corretti e possiamo riutilizzarli come meglio preferiamo!

Come creare una funzione personalizzata in Power Query su Excel (caso 2)

Vediamo un altro caso dove può avere senso creare una funzione personalizzata in Power Query su Excel. Siamo infatti arrivati a un punto dove abbiamo sì recuperato i nomi dei giocatori, ma dobbiamo inserire anche i dati per ogni giocatore, che recuperiamo con una query simile alla precedente. Ho rimosso tutto il percorso di Html.Table perchè molto lungo e poco rilevante:

let
Source = Web.BrowserContents("https://www.espn.com/nba/boxscore/_/gameId/401768066"),
#"Extracted Table From Html" = Html.Table(Source, ....),
#"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
#"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"MIN", "FG", "3PT", "FT", "OREB", "DREB", "REB", "AST", "STL", "BLK", "TO", "PF", "+/-", "PTS"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([MIN] <> "" and [MIN] <> "MIN"))
in
#"Filtered Rows"

In questo caso, prendiamo la tabella che desideriamo, usando la prima riga come intestazione (Table.PromoteHeaders), selezionando solo alcune colonne (Table.SelectColumns) e alcune righe (Table.SelectRows). Usiamo lo stesso procedimento per entrambe le squadre - sopra, puoi vedere lo SCORE_NUGGETS.

Come facciamo, ora, a collegare i dati per i giocatori? Sappiamo che la prima riga è A. Gordon, che ha giocato per 36 minuti (prima riga, prima colonna della seconda tabella), ma i due dati sono scollegati. Qui, una funzione personalizzata è ancora più interessante del caso precedente - perchè è ancora più generale, dato che si tratta di accodare orizzontalmente dei dati in Power Query, evitando però di fare merge di query con colonne indice e simili passaggi macchinosi. Vediamo come fare.

Vediamo come possiamo, in un solo passaggio, integrare le due tabelle per i Los Angeles Clippers:

= Table.PromoteHeaders(Table.FromColumns(
Table.ToColumns(Table.DemoteHeaders(TBL_CLIPPERS))&
Table.ToColumns(Table.DemoteHeaders(SCORE_CLIPPERS))))

Questa soluzione è presa da un video Youtube di Bhavya Gupta, che ha trovato un modo semplice ed elegante per replicare il funzionamento di STACK.ORIZ in Power Query. Il funzionamento è il seguente:

• Sappiamo che l'ordine dei dati è lo stesso e che il numero di righe è uguale per entrambe le tabelle
• Le intestazioni ci danno fastidio, in questo caso, quindi utilizzeremo Table.DemoteHeaders per spostare le intestazioni nella prima riga. Le recupereremo, in seguito, per ricreare le intestazioni
• Table.ToColumns crea un elenco (List) dove ogni riga contiene tutti i valori della colonna corrispondente
• Table.FromColumns crea una tabella a partire da una lista di colonne e relativi valori, che sono quelli creati da Table.ToColumns. Utilizzando &, ci assicuriamo che l'ordine rimanga quello che desideriamo: prima i nomi dei giocatori, poi i dati relativi agli stessi giocatori
• A questo punto, non ci resta che ottenere nuovamente le intestazioni, utilizzando Table.PromoteHeaders

Questo è proprio un caso nel quale potrebbe interessarci creare una funzione personalizzata, dato che abbiamo:

• Un processo ripetibile in numerose circostanze
• Una indicazione chiara degli input
• Un'idea precisa del risultato che desideriamo ottenere

Come nel caso precedente, dobbiamo decidere le informazioni inserite dall'utente: in questo caso, sono TBL_CLIPPERS (una tabella) e SCORE_CLIPPERS (un'altra tabella). Vediamo come sarà la nostra funzione personalizzata, dove chiameremo le tabella tbl1 e tbl2:

let StackTablesHorizontally = (tbl1 as table, tbl2 as table) as table =>
let

// Step1: Rimuove le intestazioni temporaneamente per unire le colonne
tbl1_demoted = Table.DemoteHeaders(tbl1),
tbl2_demoted = Table.DemoteHeaders(tbl2),

// Step2: Converte entrambe le tabelle in colonne (liste di colonne)
cols1 = Table.ToColumns(tbl1_demoted),
cols2 = Table.ToColumns(tbl2_demoted),

// Step3: Unisce tutte le colonne
allColumns = List.Combine({cols1, cols2}),

// Step4: Ricrea una tabella da tutte le colonne combinate
tbl_combined = Table.FromColumns(allColumns),

// Step5: Promuove la prima riga a intestazione
tbl_final = Table.PromoteHeaders(tbl_combined)

in
tbl_final
in
StackTablesHorizontally

Vediamo come il processo sia praticamente lo stesso del precedente, anche se spiegato in più dettagli. L'unico punto che varia è lo Step3, dove utilizziamo List.Combine per chiarire meglio la parte con & precedente per accodare le informazioni.

A questo punto, non ci resta che testarla riprendendo i dati per i Denver Nuggets. Possiamo quindi richiamare la funzione inserendo TBL_NUGGETS_FX (che era stata creata con GetPlayersFromTable) e SCORE_NUGGETS.

Come noti, abbiamo una nuova tabella che si chiama TBL_NUGGETS_COMPLETE:

= StackTablesHorizontally(TBL_NUGGETS_FX, SCORE_NUGGETS)

In un colpo solo, avremo i nomi dei giocatori e i relativi dati, anche per la seconda squadra!

Chiaramente, avremmo potuto duplicare la query e poi modificare i dati di partenza, ma con la funzione personalizzata i passaggi sono molto più chiari e definiti, riducendo anche la possibilità di fare errori.

Come accodare due query in Power Query e ultimare la pulizia dei dati

A questo punto, non ci resta che ultimare il nostro processo in Power Query, facendo un accodamento di query e sistemando gli ultimi dettagli per visualizzare i dati finali relativi alle due squadre.

Per ottenere i nomi delle squadre, faremo ulteriori passaggi in Power Query - prendendo i dati da una tabella dedicata. In questo modo, potremo aggiungere la squadra anche alla tabella finale.

Facendo tasto destro sul valore LAC, cioè la squadra #1 (Los Angeles Clippers), potremo riutilizzare questo valore. Nota come abbiamo un {1}, che in Power Query vuol dire il secondo valore della tabella precedente, dove avevamo valore vuoto | LAC | DEN. Chiameremo LAC con il valore TEAM_1, che diventa infatti un testo, come puoi notare dall'icona ABC.

Replichiamo il processo per entrambe le squadre, inserendo poi una colonna personalizzata:

= Table.AddColumn(Source, "TEAM", each TEAM_2)

In questo modo, avremo sempre il valore per il TEAM_2 (DEN) oppure TEAM_1 (LAC), a seconda dei casi.

Possiamo poi unire le query facendo un accodamento di query:

= Table.Combine({TBL_CLIPPERS_COMPLETE, TBL_NUGGETS_COMPLETE})

Notiamo che il valore DNP-COACH'S DECISION è decisamente scomodo da mantenere, poichè occupa parecchio spazio. Vogliamo quindi sostituire questo valore in tutte le colonne della nostra tabella in un colpo solo. Il processo è abbastanza immediato.

Creando un nuovo step ulteriore, possiamo ottenere tutti i nomi delle nostre colonne:

=Table.ColumnNames(Source)

Come noti, questo passaggio restituisce una lista delle intestazioni di colonna, utile anche per un recap finale. Questo step si chiama Custom1 e lo riutilizzeremo fra poco.

A questo punto, possiamo sostituire i valori in un colpo solo:

= Table.ReplaceValue(Source,"DNP-COACH'S DECISION","DNP",Replacer.ReplaceText,Custom1)

Stiamo dicendo a Power Query che, in tutti i dati dello step chiamato Source, vogliamo sostituire DNP rispetto a DNP-COACH'S DECISION per tutte le intestazioni di colonna dello step Custom1. Possiamo quindi minimizzare i nostri passaggi di pulizia e caricare la tabella finale, chiamata TBL_COMPLETE.

Il risultato finale sarà una tabella pulita e sistemata, con tutti i nomi dei giocatori, relativi dati e nome della squadra!

Conclusione

Le funzioni personalizzate sono un argomento poco battuto in Power Query, ma possono tornare utili per numerosi motivi. Passando da una dimensione di query a una di funzione, possiamo capire meglio come sono fatti i singoli passaggi e leggere il codice M, utile anche per astrarre il nostro ragionamento da "cliccare dei pulsanti" a ragionare sul vero e proprio lavoro fatto da Power Query in sottofondo.

In linea di massima, una funzione personalizzata può tornare utile quando sappiamo che un certo processo sarà ripetuto e questo trarrà grande vantaggio dalla nostra esperienza con Power Query. Tanta più pratica faremo, tanto più ci verranno in mente possibilità di ottimizzazione da poi applicare sui nostri file di lavoro.

Un caso non affrontato in questa guida è quello relativo ad aggiungere nuove colonne in Power Query con una funzione personalizzata - se ti interessa, scrivilo nei commenti!