Come eseguire merge di query su Power Query in Excel

In questa guida vediamo come utilizzare il merge di query in Power Query e come mai sia così importante per sistemare i dati prima di portarli sui nostri file Excel. Se hai mai utilizzato CERCA.VERT o CERCA.X, il concetto è molto simile - dati diversi vengono collegati tramite delle chiavi comuni, ma in maniera più scalabile. Vediamo come.

In sostanza, il merge di query è l'operazione che ci serve combinare informazioni da diverse fonti di dati - come dicevamo, il ragionamento è simile a fare un CERCA.X o CERCA.VERT su Excel (ex ho due database che si parlano perchè alcuni dati sono comuni, li metto assieme con una funzione). Se hai mai utilizzato SQL il concetto analogo è quello delle JOIN (che infatti vedremo più avanti nella guida). Useremo il merge di query per unire fra di loro informazioni che sono presenti su diverse tabelle, evitando di utilizzare formule e migliorando il flusso di analisi dei nostri file Excel.

In Power Query, questa operazione permette di unire due tabelle o più, basandosi su colonne comuni con delle "chiavi", ovvero dati uguali presenti nelle due diverse tabelle. A differenza dell'operazione di append (o accodamento di query), che accoda i dati di una tabella sotto un'altra, il merge li affianca, creando un set di dati più ricco e dettagliato.

Perchè utilizzare il merge di query su Power Query in Excel

Il merge di query di Power Query permette di avere dataset efficienti e completi. Quante volte hai dovuto fare un CERCA.X per poi collegare dei dati di vendita con i dati dei clienti? Quante volte hai dovuto poi ripetere la stessa operazione per prendere delle informazioni sui prodotti venduti? Ecco, i merge riducono in maniera drastica queste operazioni e rendono i tuoi file molto più semplici da usare anche per una persona terza.

Come logica, il merge di query dovrebbe arrivare in una fase preliminare di creazione del file. Mettiamo di avere i dati di vendita e di clienti in due tabelle diverse:

• Carichiamo i dati, con Power Query, per averli connessi allo stesso file di lavoro
• Facciamo il merge di query collegando le due fonti dato
• Carichiamo i dati poi su Excel

Questa è una metodologia completamente diversa rispetto a una logica del tipo:

• Copio i dati di vendita aggiornati e li incollo su Excel
• Copio i dati degli utenti aggiornati e li incollo su Excel
• Creo una nuova colonna, dove ho inserito i dati di vendita, con un CERCA.X o CERCA.VERT per prendere i dati dei singoli utenti

Il primo metodo è quello corretto per lavorare in modo scalabile con Excel. Prendiamo i dati con Power Query, li colleghiamo, li carichiamo e poi iniziamo a lavorare con i dati correttamente.

Il secondo metodo è estremamente rischioso. Non usare le tabelle nè Power Query, fidandosi esclusivamente di infiniti CERCA.X, rende i file pesanti, difficili da usare e con una probabilità di errore enorme.

Se usi spesso CERCA.X, il merge di query cambierà il tuo modo di lavorare con i dati. Vediamo come.

Come preparare i dati per il merge di query su Power Query in Excel

Vediamo come preparare i dati per creare il merge di query su Excel sfruttando tre diverse tabelle:

• Tabella delle vendite
• Tabella dei clienti. I clienti sono univoci, altrimenti avremmo dei problemi in fase di merge
• Tabella dei prodotti. I prodotti sono univoci, altrimenti (vedi sopra) avremmo dei problemi in fase di merge

In questo caso stiamo fingendo di dover analizzare i dati di vendita di LEGO. Abbiamo dei prodotti LEGO inseriti nella tabella vendite, delle informazioni sui singoli set inseriti nella tabella dei prodotti e alcune informazioni sui clienti inserite nella tabella dei clienti. Ovviamente i dati sono fittizi e le informazioni sui set sono facilmente reperibili sul loro sito ufficiale.

In particolare, vediamo le singole colonne che compongono ogni database. Iniziamo dalla tabella delle vendite (TBL_VENDITE).

TBL_VENDITE presenta poche colonne: Prodotto, Cliente, Prezzo, Data, #_Set e Totale. Ottime per spiegare velocemente il database con strumenti quali array o pivot, ma non riusciamo ad andare molto in profondità.

Vediamo ora la tabella dei prodotti (TBL_PRODOTTI).

TBL_PRODOTTI potrebbe essere la nostra tabella di anagrafica dei singoli prodotti, in genere queste informazioni sono molto più dettagliate e comprendono anche informazioni su magazzino, anno di produzione e non solo. Nel nostro caso, abbiamo Set_Lego, Prezzo, Codice, Age, Pezzi e Media. Se non collegassimo questi dati con altri database, potremmo solo fare qualche analisi sulla media di prezzo, sul numero di set in quale range di età si trovi, sul numero di pezzi per set e sulla media di voti. Sicuramente informazioni utili, ma poco legate al vero andamento aziendale.

Vediamo ora i clienti (TBL_CLIENTI).

Anche in questo caso le informazioni sono parecchio limitate. Abbiamo solo ID, Regione e Age. Possiamo quindi pensare a quanti clienti siano in una regione specifica e la loro età, ma non andiamo molto avanti come analisi. In genere, anche in questo caso, troverai molte più informazioni sui clienti (nome, cognome, se ricevono la newsletter, se c'è il numero di telefono, da dove sono arrivati etc), ma la logica è sempre la stessa anche con database più ampi.

Merge di query: come ragionare prima di creare il merge su Power Query

Abbiamo visto le tre tabelle, ma al momento non sono collegate fra di loro in nessun modo. Ci risulta quindi impossibile rispondere a domande quali:

• "Quanti set ho venduto in Sicilia?"
• "Qual è il fatturato totale generato da set con almeno 500 pezzi?"
• "Qual è l'età media di chi ha comprato il set chiamato Baita Alpina?"

Se vuoi fare un esperimento, prima di leggere il resto della guida, prova a guardare i database precedenti e pensa a quali potrebbero essere i campi comuni per collegare fra di loro i vari database, come se facessi un CERCA.X

Il ragionamento principale è capire quali siano le chiavi, in questo ragionamento - ovvero, quali siano i campi che possiamo riutilizzare per collegare fra di loro i diversi dataset. In questo caso, le vendite sono molto stringate ma ci permettono di collegare velocemente anche le altre due tabelle tramite due chiavi:

• Chiave vendite - clienti: la colonna Cliente della tabella delle vendite (TBL_VENDITE) contiene gli stessi valori della colonna ID della tabella dei clienti (TBL_CLIENTI)
• Chiave vendite - prodotti: la colonna Prodotto di TBL_VENDITE contiene gli stessi valori della colonna Codice della tabella dei prodotti (TBL_PRODOTTI)

Questa visualizzazione richiama lo "star schema", ovvero una struttura definita dove vediamo le vendite (i facts, gli avvenimenti che stiamo studiando, cioè le vendite) e come questi si colleghino con le nostre dimensioni (in questo caso i dati aggiuntivi per prodotti e clienti). Se vuoi saperne di più, dai un'occhiata qui.

A questo punto, le strade per collegare i dati sono varie:

• Potremmo creare un modello dei dati per poi utilizzare Power Pivot. Questo tipo di operazione è molto comune su Power BI
• Potremmo voler creare la nostra merge di query su Power Query
• Potremmo voler collegare i dati con CERCA.X o CERCA.VERT (soluzione, come dicevamo, assolutamente subottimale rispetto alle altre due)

In questa guida, parliamo del secondo caso: vediamo quindi come creare il merge di query su Power Query in Excel, creando una singola tabella che riassuma i dati che ci interessano anzichè avere i dati su tre tabelle distinte.

Come creare un merge di query in Power Query su Excel

Vediamo ora come fare per collegare i dati tramite Power Query e creare il nostro merge di query. I dati della guida sono tutti nello stesso file, che puoi scaricare, quindi possiamo direttamente pescare i dati cliccando su ogni singola tabella e andando poi su Power Query. Più nello specifico:

• Clicca all'interno di TBL_VENDITE in una cella qualsiasi
• Clicca sul menu Dati > Recupera Dati > Da altre origini > Da tabella/intervallo
• Chiudi e Carica > Chiudi e Carica in... > Crea solo connessione

Ripeti lo stesso procedimento per TBL_PRODOTTI e TBL_CLIENTI. Se i tuoi dati di origine a lavoro sono CSV, altri file XLSX o dati diversi da quelli indicati, non ti preoccupare. Il procedimento è sostanzialmente lo stesso: pesca i dati con Power Query e caricali solo come connessione. Per il momento non vedrai niente, ma i passaggi successivi sono molto più immediati, una volta compreso il meccanismo.

Se hai scaricato il file collegato alla guida, i dati sono già stati caricati come Solo connessione, come puoi vedere andando su Query > Query e connessioni.

Cliccando su Dati > Query e Connessioni dovresti vedere una sezione come questa: 3 connessioni, ma nessun collegamento nè nuovi dati caricati sul tuo file. A questo punto torniamo su Power Query. Seleziona una delle query e poi, con il tasto destro, scegli Modifica dal menu contestuale. Non abbiamo ancora fatto niente, ma ci siamo quasi.

Ti consiglio di partire dalla tabella di vendite, ovvero TBL_VENDITE, per poi cliccare su Home > Merge di query > Unisci query come nuova. Trovo sia molto importante cominciare inserendo "Unisci query come nuova" perchè in questo modo noi stiamo creando una nuova query, che avrà tutti i nostri dati, anzichè intervenire sulla nostra query di partenza. Questo sembra un concetto banale ma vedrai che decisamente più semplice avere le nostre origini (sorgenti) separate e una query finale, piuttosto che pasticciare intervenendo direttamente sulle fonti. Se ad esempio ti servisse collegare la TBL_VENDITE con altre informazioni e facessi solo Merge di query senza Unisci query come nuova, ti ritroveresti con una TBL_VENDITE enorme dove probabilmente tutti i dati inseriti su clienti e prodotti non ti interessano. Fai attenzione.

A questo punto, vedrai una visualizzazione come questa. Puoi scegliere in autonomia le tabelle da collegare, ma in questo caso ho considerato la TBL_VENDITE e la TBL_PRODOTTI. Da qui, il ragionamento è abbastanza semplice: dobbiamo ripensare a quali siano le dimensioni che ci permettono di collegare le due tabelle, similarmente a quanto abbiamo visto prima. Vediamo come la colonna PRODOTTO e la colonna CODICE siano, in effetti, composte dagli stessi elementi.

Ci basterà cliccare su PRODOTTO di TBL_VENDITE e su CODICE della TBL_PRODOTTI. Power Query capisce che stiamo provando a collegare due fonti dati diverse, quindi segnala che "La selezione corrisponde a 1000 di 1000 della prima tabella". In questo caso tutti i codici sono in entrambe le tabelle, quindi abbiamo una corrispondenza perfetta fra le due origini. Talvolta potrebbe capitare di non avere un risultato così positivo, il che significa che probabilmente c'è un disallineamento fra le due sorgenti dato - è un segnale importante per poi intervenire sui dati iniziali, sistemandoli a dovere per avere delle query sempre aggiornate e con dati precisi.

Quali sono i tipi di join possibili in Power Query su Excel

Come vedi puoi scegliere fra diversi tipi di query, io ho selezionato Left Outer ma se hai dimestichezza con SQL puoi fare anche altre query quali Inner, Full Outer, Anti-join e così via. Se non hai dimestichezza con SQL non ti preoccupare, in sostanza le query si differenziano così:

• Left Outer: questo è il caso più comune. Vogliamo tutte le righe della prima tabella e le righe corrispondenti della seconda. In questo caso TBL_VENDITE è più importante dei prodotti, perchè vogliamo vedere tutte le vendite. Anche se un prodotto non avesse una corrispondenza di codice, non ci interesserebbe. Se hai mai utilizzato funzioni come CERCA.VERT o CERCA.X, il ragionamento è praticamente lo stesso
• Right Outer è il contrario: la seconda tabella (in questo caso TBL_PRODOTTI) comanda sulla prima. Il ragionamento è lo stesso, ma qui non avrebbe molto senso perchè ci troveremmo ad avere solo un numero di righe corrispondenti al numero di prodotti, anzichè alle vendite
• Full Outer prende tutte le righe da entrambe le tabelle. Se ci fossero dei dati che non si parlano, ci apparirebbero nell'estrazione finale con delle colonne vuote
• Inner considera unicamente le righe corrispondenti. Se un codice di un set Lego non ci fosse nella TBL_PRODOTTI, la query eliminerebbe tutte le vendite di quello specifico set dalla tabella successiva. A volte può essere utile per avere delle segmentazioni di prodotti già definite, ma attenzione perchè questa join modifica in maniera importante le informazioni
• Left Anti e Right Anti considera le righe che ci sono solo nella prima o solo nella seconda tabella. Queste sono join che uso spesso per controllare i dati, dato che, se restituiscono dei risultati, ciò vuol dire che i dati non si parlano al 100% e bisogna dare un'occhiata più approfondita. In questo caso, se facessimo una Left Anti e avessimo dei risultati, ciò vorrebbe dire che le informazioni del codice della vendita non trovano un riferimento nella TBL_PRODOTTI. Dovremmo quindi aggiornare la TBL_PRODOTTI inserendo anche le informazioni per i nuovi codici.

Come dicevamo - il caso Left Outer è quello più comune, ma in casi specifici gli altri tipi di join potrebbero aiutarti enormemente nella preparazione dei dati corretti. La corrispondenza fuzzy, che vedi fra le opzioni, può ad esempio essere d'aiuto quando i dati iniziali non sono sempre scritti nella maniera corretta - anche questo è un caso particolare, ma talvolta può essere di aiuto.

Come espandere i campi su Power Query dopo aver fatto il merge di query

Abbiamo fatto il merge di query e quindi abbiamo collegato la TBL_VENDITE con la TBL_PRODOTTI. Adesso vedrai una nuova query chiamata Merge1 che comprende tutte le righe della TBL_VENDITE e una nuova colonna chiamata TBL_PRODOTTI. Come noti, ci sono due frecce che puntano in direzione opposta - se clicchi sopra alle frecce, vedrai che hai la possibilità di espandere i campi della TBL_PRODOTTI a tuo piacimento. Qui dovrai decidere quali siano i campi che veramente ti interessano per poi creare le tue analisi personalizzate.

Di default, Power Query seleziona tutte le colonne della TBL_PRODOTTI e spunta la casella "Usa il nome della colonna originale come prefisso". Ti sconsiglio entrambe le opzioni, dato che probabilmente non ti serviranno tutte le colonne (CODICE è una mera ripetizione, ad esempio) e utilizzare i prefissi rende le colonne molto scomode da leggere. Per fortuna, puoi selezionare a mano quali siano le colonne che ti interessano e rimuovere la spunta, per poi cliccare su OK e portare i dati direttamente sulla nuova query. A titolo di esempio, abbiamo selezionato solo il nome del set e il numero di pezzi.

Come vedi, adesso abbiamo due colonne in più e questo renderà molto più semplice fare alcuni calcoli di analisi.

Possiamo ripetere il procedimento cliccando poi su Merge di query e stavolta possiamo mantenere l'opzione standard, dato che abbiamo la nostra nuova query chiamata Merge1. Collegheremo la nuova query con la TBL_CLIENTI, che ancora non è stata collegata con i dati.

Anche in questo caso, ci basterà selezionare la colonna CLIENTE e la colonna ID per collegare i due dataset. Avremo quindi maggiori informazioni sui nostri clienti, avendo la provenienza e l'età.

Ripetendo i procedimenti, possiamo quindi espandere i dati e modificare anche il nome della nostra query, che non sarà più Merge1 bensì TBL_COMPLESSIVA. Abbiamo, in una sola tabella, tutte le informazioni che ci servono per l'analisi.

Come vedi, adesso, per fare un'analisi approfondita delle vendite non avremo più bisogno di collegare i dati con CERCA.X o altri strumenti, dato che abbiamo le nostre query. Questo procedimento è fondamentale per avere dei file scalabili, dato che se variassimo le origini dei dati delle tre tabelle e cliccassimo poi su Query e connessioni per aggiornare le query, Power Query ripeterebbe tutti questi passaggi per poi restituirci i dati più aggiornati possibili.

Conclusioni

Abbiamo visto come fare un merge di query su Power Query in Excel con un esempio pratico. Siamo partiti da tre tabelle sconnesse che sono state collegate tramite due diversi merge di query, per poi ottenere una tabella univoca dalla quale possiamo creare velocemente delle analisi.

Questo procedimento è fondamentale per avere file veloci e facili da usare, ma bisogna fare attenzione nella parte iniziale di messa a terra del progetto - se le colonne dei dati non si parlano e non sono state pulite a sufficienza, non riusciremo ad avere un vero valore aggiunto. Se invece creiamo delle strutture scalabili per l'analisi, Power Query può farci dimenticare i vari CERCA.VERT e CERCA.X riducendo all'osso l'errore umano e lasciando spazio per poi sviscerare i dati senza preoccupazioni.