Come misurare il tempo di aggiornamento delle query Power Query in Excel

Quando un file Excel impiega diversi minuti per aggiornare le query quando clicchiamo su Aggiorna tutto, sapere genericamente che Power Query è lento ci aiuta poco: quello che ci interessa davvero è capire quali query stanno incidendo maggiormente sul tempo complessivo di aggiornamento.

Nei miei progetti di consulenza Power Query è un elemento imprescindibile, ma quando ci sono molte query non è facile capire come mai il file sia rallentato. Sarà il codice? Sarà il connettore? Sarà il nuovo file? Excel non ci fornisce un riepilogo immediato con la durata di ogni singola query, ma possiamo costruirlo con una macro VBA. In questa guida vediamo quindi come scegliere una o più query, ripetere lo stesso test più volte e salvare i risultati in un foglio dedicato, così da avere finalmente dei dati su cui ragionare.

La macro ci permetterà di:

• scegliere una singola query oppure un gruppo di query;
• testare tutte le query presenti nel file;
• ripetere lo stesso aggiornamento più volte;
• registrare data e ora di inizio e fine;
• calcolare la durata del refresh;
• salvare i risultati nel foglio LOG_QUERY;
• misurare anche il tempo complessivo di un gruppo di query.

Alla fine avremo un piccolo strumento di analisi che potremo riutilizzare anche in altri file Excel, se lo desideri.

Perché misurare il tempo di aggiornamento delle query Power Query

Quando un file contiene molte query, la prima tentazione è spesso quella di aprire l’Editor di Power Query e iniziare a ottimizzare ciò che sembra più complesso. Non è una cattiva idea, ma se alcuni suggerimenti possono essere sempre utili (ridurre gli step di filtro e accorparli, ordinare solo alla fine, cambiare l'ordine delle colonne alla fine...) non è così scontato andare poi nello specifico, soprattutto quando le query sono molte e diversificate. In questi casi, misurare i tempi reali può essere utilissimo per capire su quali query concentrarci.

Questo perchè, talvolta, query che restituisce 300 righe può essere molto più lenta di una query che ne restituisce 100.000, perché il numero di righe finali ci dice poco sul lavoro effettuato per arrivare a quel risultato. La query potrebbe aver letto centinaia di file, interrogato SharePoint, eseguito diversi merge o filtrato milioni di record prima di restituire poche righe. Senza una misurazione rischiamo quindi di dedicare tempo a una query che impiega 3 secondi, ignorandone un’altra che ne richiede 40. Il log serve proprio a stabilire una priorità e a capire da dove conviene iniziare.

La macro misura il tempo necessario a Excel per completare il refresh della connessione. La durata può quindi comprendere il collegamento alla sorgente, il recupero dei dati, le trasformazioni Power Query, eventuali dipendenze e il caricamento finale sul foglio oppure nel modello dati.

La domanda a cui rispondiamo è molto concreta:

Quanto tempo impiega Excel ad aggiornare questa query?

Per capire invece quale singolo passaggio del codice M stia rallentando il processo dovremo entrare nel dettaglio della query e, se necessario, utilizzare gli strumenti di diagnostica disponibili in Power Query - oppure, usare un qualsiasi LLM per farci dare una mano e visualizzare più rapidamente i punti critici.

Il file Excel utilizzato per misurare le query

Nel file utilizzato per questa guida sono presenti quattro query originate da una pagina di Wikipedia dedicata agli atleti più vittoriosi nella storia delle Olimpiadi. Chiaramente puoi provare su qualsiasi file tu voglia, ma in questo caso puoi tranquillamente scaricare il file e fare una prova in solitaria.

L'output sono quattro query con logiche diverse, giusto per sperimentare non su una sola query ma su una serie (solo Italia, top 50 donne etc):

La macro recupera automaticamente le query presenti nella cartella di lavoro, quindi possiamo utilizzare la stessa procedura anche in un altro file senza modificare manualmente l’elenco. Questo è un requisito importante a livello di scalabilità, perché una macro costruita sui nomi delle quattro query dell’esempio avrebbe avuto poca utilità al di fuori del file utilizzato nella guida.

Come sarà il foglio LOG_QUERY

Al termine del test la macro crea, se necessario, un foglio chiamato LOG_QUERY.

Le colonne sono queste:

Un risultato può essere simile a questo:

Il foglio viene aperto automaticamente alla fine del test. Ho anche nascosto la griglia e bloccato la prima riga, così il log rimane leggibile anche quando iniziamo ad accumulare molte prove.

Come inserire la macro VBA per misurare le query Power Query

Prima di inserire il codice, se non usi il file della guida, ti ricordo che il file deve essere salvato in un formato che supporti le macro, ad esempio un .xlsm:

1. Apriamo File.
2. Selezioniamo Salva con nome.
3. Scegliamo Cartella di lavoro con attivazione macro di Excel (.xlsm).
4. Premiamo ALT + F11.
5. Nell’Editor VBA selezioniamo Inserisci > Modulo.
6. Incolliamo tutto il codice nello stesso modulo.

Alcune specifiche sui file .xlsm, necessarie se non conosci questo formato:

• XLSM vuol dire che il file ha delle macro. Le macro possono essere malevole, quindi alcune organizzazioni le bloccano di default. In File > Opzioni > Centro protezione puoi controllare le opzioni di sicurezza relative alla macro
• Scaricare un file con macro potrebbe indicare che alcune funzionalità sono bloccate, quindi ti consiglio di selezionare il file > tasto destro > proprietà e controllare che non ci siano blocchi specifici
• I file con macro non funzionano nella versione web di Excel

Se non hai la tranquillità necessaria per aprire il file, non ti preoccupare - puoi sempre copiare il codice e inserirlo in un nuovo file su locale per fare qualche test, nel caso.

Option Explicit

Sub MisuraQueryConRipetizioniELog()

    Dim cn As WorkbookConnection
    Dim tutteConnessioni() As String
    Dim selezionate As Collection
    Dim elenco As String
    Dim inputScelta As Variant
    Dim inputRipetizioni As Variant
    Dim ripetizioni As Long
    Dim i As Long
    Dim r As Long
    Dim n As Long
    Dim nomeConnessione As String
    Dim nomeQuery As String
    Dim t0 As Double
    Dim tBlocco As Double
    Dim durata As Double
    Dim durataBlocco As Double
    Dim ws As Worksheet
    Dim inizioQuery As Date
    Dim fineQuery As Date
    Dim inizioBlocco As Date
    Dim fineBlocco As Date
    
    ' Recupera elenco connessioni Power Query
    n = 0
    
    For Each cn In ThisWorkbook.Connections
        
        If Left$(cn.Name, 8) = "Query - " Then
            n = n + 1
            ReDim Preserve tutteConnessioni(1 To n)
            tutteConnessioni(n) = cn.Name
            
            elenco = elenco & n & " - " & Replace(cn.Name, "Query - ", "") & vbCrLf
        End If
        
    Next cn
    
    If n = 0 Then
        MsgBox "Nessuna query Power Query trovata.", vbExclamation
        Exit Sub
    End If
    
    ' Scelta query
    inputScelta = Application.InputBox( _
        Prompt:="Seleziona la query o le query da testare." & vbCrLf & vbCrLf & _
                "Esempi:" & vbCrLf & _
                "1 = una query" & vbCrLf & _
                "1,3,5 = più query" & vbCrLf & _
                "ALL = tutte le query" & vbCrLf & vbCrLf & _
                elenco, _
        Title:="Selezione query", _
        Type:=2)
    
    If inputScelta = False Then Exit Sub
    
    Set selezionate = EstraiConnessioniSelezionate(CStr(inputScelta), tutteConnessioni, n)
    
    If selezionate.Count = 0 Then
        MsgBox "Nessuna query valida selezionata.", vbExclamation
        Exit Sub
    End If
    
    ' Numero ripetizioni
    inputRipetizioni = Application.InputBox( _
        Prompt:="Quante volte vuoi ripetere il test?", _
        Title:="Numero test", _
        Default:=5, _
        Type:=1)
    
    If inputRipetizioni = False Then Exit Sub
    
    ripetizioni = CLng(inputRipetizioni)
    
    If ripetizioni < 1 Then
        MsgBox "Il numero di test deve essere almeno 1.", vbExclamation
        Exit Sub
    End If
    
    ' Prepara foglio log
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Set ws = PreparaFoglioLog()
    
    On Error GoTo GestioneErrore
    
    ' Esegue test
    For r = 1 To ripetizioni
        
        Application.StatusBar = "Test " & r & " di " & ripetizioni & " in corso..."
        
        Application.CalculateUntilAsyncQueriesDone
        
        inizioBlocco = Now
        tBlocco = Timer
        
        For i = 1 To selezionate.Count
            
            nomeConnessione = selezionate(i)
            nomeQuery = Replace(nomeConnessione, "Query - ", "")
            
            Set cn = ThisWorkbook.Connections(nomeConnessione)
            
            DisattivaRefreshBackground cn
            
            Application.CalculateUntilAsyncQueriesDone
            
            inizioQuery = Now
            t0 = Timer
            
            cn.Refresh
            
            Application.CalculateUntilAsyncQueriesDone
            
            fineQuery = Now
            
            durata = Timer - t0
            If durata < 0 Then durata = durata + 86400
            
            ScriviLog ws, inizioQuery, fineQuery, r, nomeQuery, durata, "SINGOLA QUERY"
            
        Next i
        
        fineBlocco = Now
        
        durataBlocco = Timer - tBlocco
        If durataBlocco < 0 Then durataBlocco = durataBlocco + 86400
        
        If selezionate.Count > 1 Then
            ScriviLog ws, inizioBlocco, fineBlocco, r, "TOTALE QUERY SELEZIONATE", durataBlocco, "TOTALE BLOCCO"
        End If
                
    Next r
    
        ws.Columns("A:F").AutoFit
        
        Application.StatusBar = False
        
        VaiAFoglioLog ws
        
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        
        MsgBox "Test completato." & vbCrLf & _
               "Query selezionate: " & selezionate.Count & vbCrLf & _
               "Ripetizioni: " & ripetizioni & vbCrLf & _
               "Il foglio LOG_QUERY è stato aggiornato.", _
               vbInformation, "Misurazione completata"
    
    Exit Sub

GestioneErrore:
    Application.StatusBar = False
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    MsgBox "Errore durante il test:" & vbCrLf & _
           Err.Description, _
           vbCritical, "Errore"

End Sub

Il codice completo deve partire da:

Option Explicit

e comprendere la macro principale e tutte le procedure private di supporto.

La procedura da eseguire è:

MisuraQueryConRipetizioniELog

Le altre procedure vengono richiamate automaticamente durante l’esecuzione.

Come avviare il test e scegliere le query Power Query

Per avviare la macro:

1. torniamo in Excel;
2. apriamo la scheda Sviluppo;
3. clicchiamo su Macro;
4. selezioniamo MisuraQueryConRipetizioniELog;
5. clicchiamo su Esegui.

Come alternativa, per semplificare l'uso della macro, ho inserito anche la macro in una forma dedicata che trovi nel foglio MACRO. In questo caso ti basterà cliccare sul pulsante per far partire la macro e scegliere poi le varie opzioni dedicate.

La prima finestra mostra l’elenco delle query trovate nel file. Nel nostro esempio vedremo:

1 - TBL_ITALY
2 - TBL_TOP50_WOMEN
3 - TBL_ALPINE_SKIING
4 - TBL_RANKING

Possiamo inserire:

Possiamo scrivere anche:

1, 3, 4

perché la macro elimina automaticamente gli spazi. Se inseriamo due volte lo stesso numero, per esempio 1,1,3, la prima query viene comunque selezionata una sola volta.

Dopo la scelta delle query, la macro chiede quante volte vogliamo ripetere il test.

Nel nostro caso, le selezioniamo tutte.

Scegliamo poi quante prove di aggiornamento fare, in questo caso manteniamo lo standard che è 5. Nella maggior parte dei casi, valuterei sempre di fare almeno 3 prove. Una singola prova può essere poco rappresentativa, perché il tempo di aggiornamento può cambiare in funzione della cache, della velocità della rete, della sorgente dati o semplicemente del carico del computer in quel momento. Con cinque test iniziamo già ad avere un quadro abbastanza utile; con dieci o più possiamo analizzare meglio anche la variabilità.

Il primo aggiornamento merita spesso un controllo separato, perché può comportarsi diversamente dai successivi. Excel potrebbe dover inizializzare il motore Power Query, aprire la connessione o recuperare dati che nei test successivi risultano già disponibili.

Man mano, le query si aggiorneranno in automatico senza che sia necessario un nostro intervento. Notiamo che la macro sta lavorando perchè appare Caricamento dei dati in corso.

Al termine della procedura, un comodo pop-up ci segnalerà che abbiamo terminato le nostre misurazioni.

Il foglio LOG_QUERY, se non presente, sarà creato in automatico dalla macro e ci permetterà di visualizzare le singole durate sia come singoli sia come blocco totale.

Da qui, nella guida parleremo della parte tecnica di VBA - ma come noti, anche senza conoscere VBA, il flusso è piuttosto semplice perchè ti basterà:

• Cliccare sulla forma in MACRO
• Scegliere la query o le query di riferimento
• Decidere il numero di ripetizioni
• Attendere la creazione del foglio LOG_QUERY e poi analizzarlo

Se vuoi vedere come ottenere gli stessi dati tramite un plugin, puoi saltare alla sezione dedicata a TimeSleuth di Monkey Tools!

Come la macro trova automaticamente le query Power Query

Excel registra le query anche come connessioni della cartella di lavoro. La macro scorre quindi:

ThisWorkbook.Connections

e mantiene le connessioni il cui nome inizia con:

Query -

Qui il dettaglio:

For Each cn In ThisWorkbook.Connections

    If Left$(cn.Name, 8) = "Query - " Then
        n = n + 1
        ReDim Preserve tutteConnessioni(1 To n)
        tutteConnessioni(n) = cn.Name

        elenco = elenco & n & " - " & _
                 Replace(cn.Name, "Query - ", "") & vbCrLf
    End If

Next cn

Una connessione potrebbe chiamarsi internamente:

Query - TBL_ITALY

mentre nell’elenco mostrato all’utente vedremo soltanto:

TBL_ITALY

La parte Query - ci serve per identificare la connessione, ma non avrebbe alcuna utilità nel messaggio mostrato all’utente.

Le connessioni trovate vengono salvate nell’array:

tutteConnessioni()

Possiamo immaginarlo come un elenco temporaneo:

La riga:

ReDim Preserve tutteConnessioni(1 To n)

allarga l’array ogni volta che viene trovata una nuova query, mentre Preserve mantiene gli elementi già presenti. Quando scegliamo il numero 3, la macro può quindi recuperare la terza connessione dell’elenco.

Se hai utilizzato in passato funzioni come SUBTOTALE o AGGREGA, la logica è simile: =SUBTOTALE(9 significa che vogliamo fare una somma, ad esempio.

Come viene interpretata la scelta delle query

La selezione viene passata alla funzione:

EstraiConnessioniSelezionate

La funzione elimina gli spazi, converte il testo in maiuscolo, controlla se abbiamo scritto ALL, TUTTE oppure * e, in caso di una selezione come 1,3,4, divide il testo e recupera le connessioni corrispondenti.

Questa parte:

sceltaPulita = UCase$(Trim$(testoScelta))
sceltaPulita = Replace(sceltaPulita, " ", "")

trasforma:

1, 3, 4

in:

1,3,4

La funzione:

ConnessioneGiaPresente

controlla invece che la stessa query non venga aggiunta due volte, dato che non avrebbe molto senso.

Questa parte del codice può sembrare un po’ più lunga rispetto a una semplice InputBox, ma rende la macro decisamente più comoda da usare. Possiamo selezionare una query, un gruppo o tutte le query senza dover modificare il codice ogni volta. Se non hai molta dimestichezza con VBA, è sicuramente più semplice rispetto a toccare il codice.

Come misurare il tempo di aggiornamento di una query Power Query

Il blocco centrale della macro è questo:

inizioQuery = Now
t0 = Timer

cn.Refresh

Application.CalculateUntilAsyncQueriesDone

fineQuery = Now

durata = Timer - t0

Now e Timer svolgono due ruoli diversi. Now registra data e ora e viene utilizzato per compilare le colonne Inizio refresh e Fine refresh, mentre Timer serve per calcolare il tempo trascorso.

La sequenza è questa:

• registriamo l’inizio
• salviamo il valore iniziale del timer
• aggiorniamo la connessione
• aspettiamo la conclusione dell’aggiornamento
• calcoliamo la differenza

Potremmo farlo a mano, certo, però in questo modo è decisamente più preciso e agevole - immaginati sempre questo processo non su 4 query, ma magari su una dozzina o anche di più.

Prima del refresh viene inoltre richiamata:

DisattivaRefreshBackground cn

La procedura prova a impostare:

BackgroundQuery = False

sulle connessioni OLEDB e ODBC. Per una macro che misura i tempi questo passaggio è importante, perché vogliamo che VBA aspetti il completamento del refresh prima di continuare. Se la query lavorasse in background, il codice potrebbe proseguire e registrare un tempo inferiore rispetto alla durata effettiva. Questo è un caso limite che va testato, ma dovrebbe funzionare - al momento ho testato solo con connessioni da locale e web.

La procedura utilizza:

On Error Resume Next

perché non tutte le connessioni espongono la proprietà nello stesso modo. Qui l’uso è limitato a poche righe e ha senso; eviterei invece di utilizzarlo su blocchi molto più ampi, perché rischierebbe di nascondere errori che dovremmo vedere.

Nel codice troviamo anche:

Application.CalculateUntilAsyncQueriesDone

che viene utilizzato per attendere la conclusione degli aggiornamenti asincroni. Lo richiamiamo prima del test e dopo il refresh, così riduciamo il rischio di sovrapporre attività diverse e rendiamo i risultati più confrontabili.

Come viene gestito il passaggio della mezzanotte

Timer conta i secondi trascorsi dalla mezzanotte. Se una query iniziasse alle 23:59:58 e terminasse dopo le 00:00, la differenza potrebbe risultare negativa.

Il codice gestisce questa eventualità con:

If durata < 0 Then durata = durata + 86400

86400 corrisponde ai secondi presenti in una giornata.

È un caso raro, ma la gestione richiede una sola riga e rende il calcolo più solido, quindi tanto vale mantenerla.

Come scrivere la durata delle query nel foglio LOG_QUERY

Alla fine del refresh viene richiamata:

ScriviLog ws, inizioQuery, fineQuery, r, _
           nomeQuery, durata, "SINGOLA QUERY"

La procedura individua la prima riga libera:

nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

e scrive i risultati nelle colonne da A a F.

La durata viene convertita con:

secondi / 86400

perché Excel memorizza date e orari come numeri (frazioni di giornata). 24 ore * 60 minuti * 60 secondi = 86400.

La colonna viene poi formattata con:

[h]:mm:ss.00

In Excel in italiano potremo vedere, per esempio:

0:00:02,53

Ho preferito mantenere anche i centesimi di secondo. Su query che richiedono diversi minuti cambieranno poco, mentre sulle query più rapide possono essere utili per confrontare le varie ripetizioni.

Come misurare il tempo totale di più query Power Query

Come visto, possiamo selezionare anche più query insieme. Se, per esempio, inseriamo:

1,3,4

nel log troveremo una riga per:

• TBL_ITALY;
• TBL_ALPINE_SKIING;
• TBL_RANKING;
• TOTALE QUERY SELEZIONATE.

La riga finale avrà come tipo:

TOTALE BLOCCO

Questa misurazione è utile quando le query fanno parte dello stesso processo e vogliamo capire quanto tempo richiede l’intero gruppo.

Farei però attenzione alle dipendenze, perché se una query richiama un’altra l’ordine di aggiornamento può influenzare i risultati. Per confrontare due serie di test utilizzerei quindi sempre le stesse query, nello stesso ordine e con lo stesso numero di ripetizioni.

Come si crea automaticamente il foglio LOG_QUERY

La funzione:

PreparaFoglioLog

gestisce il foglio dei risultati.

Se LOG_QUERY non esiste, la funzione crea un nuovo foglio, lo inserisce alla fine della cartella di lavoro, assegna il nome corretto e aggiunge le intestazioni, modificandone anche la visualizzazione.

Le colonne sono definite qui:

headers = Array( _
    "Inizio refresh", _
    "Fine refresh", _
    "Test", _
    "Query", _
    "Durata", _
    "Tipo misurazione")

Se il foglio esiste già, la funzione controlla la prima riga. In presenza di intestazioni diverse, inserisce una nuova riga senza cancellare i dati esistenti.

Preferisco mantenere i vecchi log piuttosto che eliminarli automaticamente. Se vogliamo ripartire da zero possiamo sempre cancellare manualmente il foglio o i dati, mentre recuperare risultati eliminati sarebbe decisamente più scomodo.

Perché il codice utilizza Private Function e Private Sub

La macro è divisa in una procedura principale e più procedure di supporto.

La parola Private impedisce a queste procedure di comparire nell’elenco delle macro. L’utente deve eseguire soltanto:

MisuraQueryConRipetizioniELog

Le altre vengono richiamate quando servono e ricevono informazioni dalla procedura principale.

Se utilizzi già LET e LAMBDA, possiamo fare un confronto utile con le formule Excel.

Una Function VBA ricorda una LAMBDA nominata, perché riceve informazioni e restituisce un risultato. Per esempio:

Private Function PreparaFoglioLog() As Worksheet

restituisce un oggetto Worksheet, che possiamo poi assegnare con:

Set ws = PreparaFoglioLog()

Le variabili dichiarate con Dim ricordano invece i nomi definiti all’interno di LET, perché assegnano un nome a valori che utilizzeremo in seguito:

Dim durata As Double
Dim nomeQuery As String

Una Sub, infine, esegue un’azione sul file. Può scrivere in una cella, aggiornare una query, creare un foglio oppure applicare una formattazione.

Il confronto può essere riassunto così:

Come aprire automaticamente LOG_QUERY alla fine del test

Alla fine viene eseguita:

VaiAFoglioLog ws

La procedura attiva LOG_QUERY, nasconde la griglia, blocca la prima riga e seleziona A1. Sono piccoli dettagli, ma utilizzeremo questo foglio per leggere i risultati e preferisco quindi trovarlo già sistemato.

La macro disattiva inoltre temporaneamente:

Application.ScreenUpdating = False
Application.EnableEvents = False

per evitare aggiornamenti visivi inutili e l’avvio di eventuali procedure collegate agli eventi del file.

In caso di errore, la sezione:

GestioneErrore:

ripristina la barra di stato, gli eventi e l’aggiornamento dello schermo prima di mostrare la descrizione del problema.

Quando disattiviamo impostazioni globali di Excel in una macro, conviene sempre prevedere un punto in cui ripristinarle anche in caso di errore.

Come analizzare le query Power Query lente con i risultati del log

Una volta raccolti i test, possiamo iniziare a confrontarli.

Nel file di esempio TBL_ITALY ha restituito:

I risultati sono abbastanza vicini, quindi la query sembra avere un comportamento stabile nelle condizioni del test.

Una situazione come questa sarebbe invece più interessante:

Qui la variabilità è elevata e inizierei a controllare la sorgente dati, la rete, le dipendenze tra query, gli altri processi in esecuzione e la destinazione di caricamento.

La media da sola può nascondere informazioni importanti. Se una query impiega in media 20 secondi passando continuamente da 10 a 30, voglio vedere anche i singoli test.

In linea generale, il numero di righe non indica quanto sia lenta una query Power Query: nel file di esempio TBL_RANKING carica 330 righe, mentre TBL_ALPINE_SKIING ne carica 4. Questo dato non ci permette di stabilire quale delle due sia più lenta - anche perchè, proprio in questo caso, le tempistiche di aggiornamento sono praticamente uguali.

Una query con quattro righe finali potrebbe aver letto migliaia di file o milioni di record, quindi guarderei sempre insieme durata, origine dati, passaggi applicati, dipendenze e destinazione di caricamento.

Il numero di righe finali, preso da solo, può ingannare parecchio.

Come ottenere test più attendibili sulle query Power Query

Per confrontare le durate cercherei di mantenere condizioni simili, chiudendo gli altri file Excel pesanti, evitando aggiornamenti contemporanei e mantenendo invariata la sorgente dati.

Eseguirei almeno cinque ripetizioni e terrei separato il primo test dai successivi, soprattutto quando la sorgente è remota o il modello è complesso. Se confronto due gruppi di query, manterrei anche lo stesso ordine di esecuzione.

Non cercherei una precisione assoluta al centesimo di secondo. Se una query impiega 3,1 oppure 3,4 secondi, probabilmente la differenza non cambia le decisioni che dobbiamo prendere; se invece passa da 12 a 30 secondi, abbiamo qualcosa che vale la pena approfondire.

Come trovare le query Power Query più lente in Excel

La macro registra il tempo richiesto da Excel per completare il refresh della connessione, quindi non identifica automaticamente il singolo passaggio M che rallenta la query. Dobbiamo inoltre considerare che le dipendenze possono influenzare il risultato, la cache può cambiare i test successivi, una sorgente remota può avere prestazioni variabili e il caricamento finale può incidere sul tempo complessivo.

Userei quindi il log per individuare le query da analizzare per prime. A quel punto entrerei nell'Editor di Power Query per capire cosa stanno facendo e dove possiamo intervenire.

Con questa macro possiamo passare da una valutazione generica come:

Il file ci mette troppo ad aggiornarsi.

a qualcosa di più utile:

TBL_X impiega mediamente 45 secondi, mentre le altre query rimangono sotto i 10.

Da qui possiamo iniziare a lavorare con un criterio.

Io guarderei per prime le query con la durata media più alta, quelle con la maggiore variabilità tra un test e l'altro e le query richiamate da molte altre query.

Il foglio LOG_QUERY mantiene lo storico di:

• inizio del refresh;
• fine del refresh;
• numero del test;
• query aggiornata;
• durata;
• tipo di misurazione.

Possiamo poi utilizzare formule, tabelle pivot o Power Query stesso per creare un riepilogo con media, minimo e massimo per ogni query.

Come misurare le query Power Query con TimeSleuth di Monkey Tools

A questo punto vale la pena citare una soluzione che parte dallo stesso problema e offre strumenti decisamente più avanzati, ovvero TimeSleuth. TimeSleuth e fa parte di Monkey Tools, il componente aggiuntivo per Excel sviluppato da Ken Puls, un caro amico, MVP Excel ormai da 20 anni nonchè uno dei massimi esperti al mondo di Power Query.

Monkey Tools raccoglie diversi strumenti dedicati a Power Query e ai modelli dati, mentre TimeSleuth è quello pensato per analizzare i tempi di refresh delle query. Lo puoi selezionare, se installi Monkey Tools, cliccando proprio sull'icona con il cronometro che trovi sulla barra multifunzione.

A questo punto puoi scegliere se:

• Aggiornare la lista di query presenti sul file
• Spostare in alto o in basso le query, anche per incidere sull'ordine di aggiornamento
• Selezionare tutte le query
• Rimuovere la selezione già effettuata

Inoltre puoi:

• Ignorare la privacy
• Scegliere se fare un aggiornamento dettagliato
• Scegliere se fare un aggiornamento standard

Vedremo le varie opzioni in dettaglio.

Selezionando tutte le query e ignorando la privacy con detailed refresh, come noti, l'output è simile a quello precedente. Per ogni query avremo un tempo associato, oltre al tempo totale di aggiornamento.

Ancora più interessante è l'opzione chiamata Chart Variation, dato che ci permette di effettuare anche in questo caso vari cicli di aggiornamento, qui chiamati Trials. Decidiamo anche in questo caso di fare 5 prove, come nel caso precedente con la macro.

Come vedi, possiamo quindi analizzare media, mediana, valori minimi e massimi, distribuzione dei risultati ed eventuali valori anomali. Il grafico è molto comodo proprio per valutare con attenzione i punti critici - questa parte mi piace particolarmente, perché una query che impiega sempre 10 secondi e una query che alterna 5 e 25 secondi possono avere una media simile, ma richiedono analisi molto diverse.

Come detto, TimeSleuth permette anche di confrontare i tempi con i controlli dei livelli di privacy attivi oppure ignorati. Questo può evidenziare differenze importanti quando Power Query combina più sorgenti, ma la scelta va fatta con attenzione perché i livelli di privacy servono a evitare trasferimenti indesiderati di dati tra sorgenti.

TimeSleuth offre strumenti decisamente più completi e permette di passare più velocemente dalla misurazione all'analisi. Diciamo che per un utilizzo professionale, è un ottimo strumento che ci evita di dover impostare tutto il progetto precedente - ha il solo limite di essere a pagamento, ma può essere un investimento da valutare se è un caso comune per i tuoi processi di lavoro. Io lo utilizzo piuttosto spesso, la comodità è chiaramente quella di dover solo cliccare dei pulsanti con un'interfaccia dedicata anzichè dover impostare il processo da zero, come indicato nella guida.

Conclusioni sulla misurazione dei tempi delle query Power Query

Quando Aggiorna tutto richiede diversi minuti, sapere soltanto il tempo complessivo non ci aiuta molto a decidere dove intervenire. Con questa macro possiamo misurare una singola query, un gruppo oppure tutte le query presenti nel file, ripetendo i test e mantenendo uno storico nel foglio LOG_QUERY.

Io partirei sempre da una sequenza molto semplice: misurare, individuare le query più lente, controllare la variabilità, analizzare le dipendenze e intervenire dove il tempo speso può produrre un miglioramento reale.

Per un'analisi occasionale, la macro è più che sufficiente. Per un'attività ricorrente di ottimizzazione, TimeSleuth di Monkey Tools offre strumenti più avanzati per confrontare refresh, selezionare query e analizzare la variabilità.

A quel punto, la sola azione di Aggiornare tutto smette di essere una scatola nera: abbiamo dei numeri e possiamo decidere da dove iniziare per poi ottimizzarle!

Marco Filocamo

Sono un formatore e consulente esperto nell’uso e nell’insegnamento di Microsoft Excel. Microsoft MVP in Excel dal 2024.

Negli ultimi 3 anni ho tenuto corsi presso realtà in multinazionali come Aruba, Bridor, IMI Orton, Primadonna e SISAL, oltre a PMI e startup di diverso genere.

Realizzo corsi di formazione Excel dedicati per aziende, supporto professionisti 1:1 a distanza con call mirate e collaboro con aziende offrendo servizi di consulenza quali creazione di business plan, dashboard di vendita e non solo.

Vuoi saperne di più? Prenota una call gratuita di 15′!