Come funziona append query in Power Query su Excel

In questa guida vediamo come utilizzare l'append di query in Power Query e come mai sia così importante per sistemare i dati prima di portarli sui nostri file Excel. Nello specifico, l'append, o accodamento dei dati, ci permette di riassumere in una sola tabella numerose tabelle che arrivano da origini diverse.

Questa funzionalità è fondamentale se abbiamo dati che arrivano da sorgenti diverse ma che sono comparabili. Ad esempio, assumiamo che tu gestisca 3 cinema diversi (Milano, Siena, Palermo) che mostrano gli stessi film. Il sistema utilizzato per tenere traccia degli incassi è lo stesso, ma chiaramente ogni negozio ha il suo conto e i suoi dati.

Ho selezionato i 200 film più visti nel 2023 e poi ho creato i database fittizi, chiaramente i dati non sono reali ma verosimili, puoi sperimentare la stessa identica procedura anche con i tuoi file di lavoro!

Se vuoi seguire la guida passo passo hai due opzioni, potendo scaricare sia il file iniziale sia quello finale:

Perchè utilizzare append di query su Power Query in Excel

Per unire insieme diverse fonti dati e analizzarle complessivamente, in Power Query puoi fare un append di query - accodare, inserendo una sotto l'altra, tutte le tabelle e le origini dato che vuoi. Questo è importante in una fase preliminare, quando stai ancora lavorando sulla pulizia del dato prima di poi fare delle analisi.

Facciamo l'esempio dei tre punti vendita e come il 95% delle persone impila questi dati, cioè:

• Prima di tutto, prendi i dati del cinema numero 1, Milano
• Sotto i dati di Milano inserisci i dati del cinema numero 2, Siena
• Sotto i dati di Milano e di Siena impilati inserisci i dati del cinema numero 3, Palermo

Questo è un metodo assolutamente sconsigliato per lavorare con i dati, dato che rischi di fare degli errori evidenti in fase poi di aggregazione dei dati. Un collega non riuscirebbe mai a sapere se i dati siano veramente quelli aggiornati, dove siano i file di origine e non solo. Come processo, è sicuramente da evitare.

Come si può fare, allora? Risulta molto più efficace, scalabile e studiabile il seguente procedimento:

• Definisco, una volta per tutte, dove siano i dati di Milano, Siena e Palermo. Magari sono in cloud, magari sono su una cartella in locale, magari c'è un'estrazione automatica dal nostro gestionale. L'importante è definire una procedura
• Una volta definita la procedura, i dati dei 3 esercizi commerciali vengono pescati direttamente da Power Query. Facciamo l'append che restituisce una sola tabella complessiva dalla quale partire con l'analisi dei dati

I benefici sono molteplici: sappiamo dove sono le origini, la procedura è molto più veloce e, soprattutto, riduciamo all'osso la possibilità di errori umani.

Come preparare i dati per l'append di query su Power Query in Excel

I dati che vogliamo unire con l'append di query su Power Query devono essere coerenti, vediamo come fare con un caso pratico. Abbiamo un database simile per tutte e tre le città:

Le colonne sono:

• Film: indichiamo il nome del film
• Prezzo: il prezzo medio di ingresso per persona
• Data: la data della proiezione
• Persone: il numero totale di persone paganti in sala
• Totale: Prezzo * Persone

I 3 database sono identici, almeno come struttura. Milano ha 1000 righe, Siena ha 781 righe mentre Palermo ne ha 860:

Qui bisogna fare molta attenzione - i dati devono avere esattamente le stesse intestazioni. Se avessi dei dati con delle intestazioni diverse, dovresti modificarle prima di fare l'append altrimenti accodarle sarebbe un disastro. La posizione invece non è importante, se Prezzo fosse la prima o la quarta colonna, a Power Query non interesserebbe.

Facciamo l'esempio dove, anzichè Film, in una tabella i film abbiano una colonna chiamata Movie:

Come noti, Power Query non può capire che per noi MOVIE e FILM sono la stessa cosa e quindi crea due colonne separate. Fare analisi su un dataset simile sarebbe un incubo. Se ti capita un caso simile, puoi sempre cliccare sull'intestazione delle singole colonne e inserire un valore comune per ognuna di loro!

I dati utilizzati per l'append di query, che vengono quindi accodati tramite Power Query su Excel, devono avere colonne con intestazione uguale per essere utilizzate. Bisogna quindi fare molta attenzione perchè, come già anticipato, può capitare di avere file con intestazioni diverse e questo crea dei problemi in fase di analisi.

In questo caso noi abbiamo tre diverse tabelle di vendita, corrispondenti a 3 cinema: Milano, Siena e Palermo. Possiamo quindi rispondere a domande quali:

• Quante persone sono andate al cinema a Milano in un giorno specifico?
• Quanto è stato l'incasso per un film specifico a Siena?
• Qual è il prezzo medio per un certo film a Palermo?

Tuttavia, queste analisi rimangono su una sola dimensione - fondamentali se stiamo giudicando un singolo cinema, ma poco interessanti se vogliamo avere una visione maggiormente d'insieme.

Vediamo quindi come prendere tutte e tre le tabelle per creare un'analisi complessiva.

Prima di tutto, dobbiamo caricare le nostre tabelle come query su Power Query. Cliccando all'interno della singola tabella, vai su Dati > Recupera Dati > Da altre origini > Da tabella/intervallo. Se tu avessi dei dati diversi da questi, non ti preoccupare - dovrai solo prendere i tuoi dati e caricarli sempre da Dati > Recupera Dati scegliendo poi l'opzione più in linea con il tuo caso.

Dato che abbiamo già le tabelle nel nostro file, non vogliamo caricarle due volte. Quindi, quando ti trovi su Power Query, dovrai selezionare la voce Chiudi e carica in... e poi selezionare Crea solo connessione. In questo modo le tabelle saranno sì caricate su Power Query, ma ci eviteremo di averle due volte nello stesso foglio.

Ripeti la procedura per le tre tabelle e portale, come query, su Power Query.

Come fare l'append di query su Power Query in Excel

Una volta caricate le tre tabelle su Power Query per poi fare l'append, dovresti vedere un qualcosa di simile. Le tre tabelle sono state caricate e ora possiamo cliccare su Home > Accoda query > Accoda query come nuove per inserirle tutte su una sola tabella, anzichè averne tre. Suggerisco sempre di avere "Accoda query come nuove" perchè in questo modo avremo una nuova tabella. Se invece selezioni solamente Accoda query, andrai a impattare sulla tabella di partenza - per chiarirci - se iniziassimo dalla TBL_VENDITE_MILANO, sarebbe questa tabella a comprendere poi anche Siena e Palermo. Trovo che sia più confusionario rispetto a tenere separate fonti e analisi, quindi ti consiglio di seguire questa procedura.

A questo punto, puoi selezionare quante tabelle "impilare" o accodare insieme. Dato che ne abbiamo tre, le selezioniamo tutte e le inseriamo come tabelle da accodare. L'ordine non è importante, ma in questo caso prima ci saranno i dati di Milano, poi di Palermo e infine quelli di Siena.

A questo punto, come vedi, c'è una nuova query chiamata Accoda1 che racchiude TBL_VENDITE_MILANO, TBL_VENDITE_PALERMO e TBL_VENDITE_SIENA. Come noti Power Query fa questa operazione con Table.Combine, quindi se cambiassi il nome della tabella, potresti modificarlo anche da codice direttamente.

Prima di caricarla sul file Excel, ti consiglio caldamente di mettere un nome definito alla tabella. Questo è importante perchè lo stesso nome sarà il nome del foglio, il nome della query e anche il nome della tabella che caricheremo su Excel. Se lo cambi successivamente, dovrai fare la stessa operazione tre volte. Qui il nome è TBL_COMPLESSIVA, modificato semplicemente cliccando sotto Nome in Proprietà - puoi chiaramente inserire il valore che preferisci.

A questo punto, abbiamo la nostra tabella complessiva - che include le tre tabelle (Milano 1000 righe, Siena 781 righe, Palermo 860 righe). La nostra tabella finale avrà quindi 2641 righe e ci permetterà di fare tutte le analisi del caso sui nostri database senza difficoltà. Se le tabelle di origine avessero dei nuovi risultati, ci basterebbe aggiornare la query andando su Dati > Aggiorna tutti per avere i dati sempre aggiornati.

A questo punto, abbiamo finito il nostro append e i dati sono stati correttamente caricati sul file!

Come migliorare l'append di query su Power Query in Excel

Ci sono alcuni elementi da tenere in considerazione se vogliamo proprio essere precisi quando facciamo l'append di query su Power Query. Ad esempio, nell'accodamento precedente, vediamo come sia impossibile risalire alle vendite per singola città e che le date presentano anche l'orario (00:00:00). Il primo caso succede perchè non abbiamo indicato quali siano i database di partenza prima di unirli, mentre il secondo caso succede perchè Power Query modifica in autonomia alcuni tipi di dati per aiutare la parte di analisi.

Vediamo come sistemare.

Troverai questo esempio anche nel file collegato alla guida ma ho semplicemente duplicato le query cliccando con il tasto destro su ognuna di loro per poi selezionare Duplica. Tutte queste nuove query le ho rinominate inserendo _FIX alla fine.

Prendendo come riferimento la tabella di Milano, clicco su Aggiungi colonna > Colonna personalizzata. In questo modo potrò creare una colonna personalizzata che abbia il nome della città, in modo da semplificare la parte successiva di analisi.

Qui non ci serve conoscere alcun tipo di codice, dato che ci basterà inserire un nome per la nuova colonna e ="Milano" per far sì che, per ogni riga del nostro database, ci sia anche una colonna con l'indicazione della città.

Come vedi, in questo modo possiamo attribuire a ogni record la sua città corrispondente. Una volta replicato questo procedimento inserendo una colonna personalizzata anche per Palermo e Siena, ci sarà sufficiente tenere la stessa intestazione e modificare solamente il nome della città. Attenzione anche in questo caso a mantenere i dati coerenti, altrimenti l'append non funzionerà.

Facciamo nuovamente l'accoda query come nuove e inseriamo le tre tabelle con FIX. Avremo ora una nuova tabella, chiamata sempre Accoda1, con tutte le nuove informazioni impilate - a differenza del caso precedente, possiamo però evidenziare facilmente quale sia l'origine di ognuna delle transazioni

Dato che i tipi di dati sono sbagliati, possiamo cliccare su Data e poi selezionare il formato Data per tornare al caso precedente e uniformare così i dati.

A questo punto, abbiamo la nostra tabella finale - tutti i dati sono stati impilati correttamente e possiamo finalmente fare analisi complesse, senza perdere di vista le varie dimensioni dei database iniziali!