Come raggruppare i dati su Power Query in Excel

In questa guida vediamo come raggruppare i dati su Power Query in Excel, in particolare utilizzando la funzionalità chiamata Raggruppa Per.

Questa funzionalità è fenomenale per ridurre la dimensione dei nostri database, dato che ci permette di creare dei "riassunti" del dato che minimizzano il numero di record necessari per creare le nostre analisi.

Quante volte ti è capitato di avere dei database enormi, magari con un singolo record per vendita, quando invece la domanda era relativa al numero totale di vendite? Oppure al numero di ordini di un certo periodo? Oppure ancora alla media di fatturato per cliente, sommandone i relativi ordini? In questi casi non hai bisogno di vedere tutte le singole righe, dato che non ti interessano. A te interessa un modo veloce per ridurre il numero totale di righe e pescare esclusivamente le riduzioni di questo database che ti possono aiutare per l'analisi - tutto ciò si fa direttamente sfruttando l'interfaccia di Power Query, grazie a Raggruppa per.

Qui trovi un esempio di utilizzo di Raggruppa per, richiamata per creare una top10 su Excel.

Faremo degli esempi con un database di vendite ipotizzando di vendere dei set Lego. Il database ha le seguenti colonne:

• Prodotto: ID del prodotto
• Cliente: ID del cliente
• Prezzo: prezzo del singolo set
• Data: data di vendita
• #_SET: numero di set venduti nell'ordine
• Totale: fatturato ottenuto da prezzo * set
• SET_LEGO: nome del set Lego
• Pezzi: numero di pezzi per singolo set
• Regione: regione del cliente
• AGE: età del cliente

Come raggruppare i dati su Power Query in Excel: caso semplice

Vediamo un caso tipico che richiede di raggruppare i dati su Excel - ad esempio, abbiamo un database di vendita e vogliamo raggruppare i dati per ridurre il numero di record, il file rischia di diventare enorme e poco gestibile per i nostri standard.

Domanda cui rispondiamo: "Quali sono i prodotti più venduti, ordinati dal più al meno venduto?"

Interroghiamoci sul nostro database di vendita di partenza. Quali sono le colonne che ci servono? In questo caso sono solo due, dato che avremo il nostro risultato utilizzando il prodotto e il fatturato.

Su Excel, come sai, ci sono spesso diversi modi per arrivare allo stesso risultato. In questo caso abbiamo almeno tre opzioni:

• Sfruttare le tabelle pivot
• Sfruttare le formule
• Sfruttare Power Query

In questa guida non parlo delle pivot ma puoi trovare qui una guida dedicata alla creazione di tabelle pivot su Excel. Vediamo il caso delle formule, almeno come logica:

• Prendiamo i singoli valori per ID di prodotto con UNICI
• Sommiamo il totale per ognuno di loro con SOMMA.PIÙ.SE
• Ordiniamo, con DATI.ORDINA, la nostra matrice partendo dal fatturato

In sintesi, una cosa simile a questa:

=LET(prodotti;TBL_COMPLESSIVA[PRODOTTO];
prodotti_univoci;UNICI(prodotti);
fatturato;TBL_COMPLESSIVA[TOTALE];
finale;DATI.ORDINA(
STACK.ORIZ(prodotti_univoci;
SOMMA.PIÙ.SE(fatturato;prodotti;prodotti_univoci));
2;-1);
finale)

Vediamola nel dettaglio:

1. LET: Questa è una funzione che consente di assegnare nomi a dei calcoli intermedi. È utile per semplificare le formule complesse, rendendole più leggibili e potenzialmente più efficienti.
2. prodotti;TBL_COMPLESSIVA[PRODOTTO]: Questa parte assegna il nome "prodotti" ai dati nella colonna "PRODOTTO" di una tabella chiamata "TBL_COMPLESSIVA". È il primo calcolo intermedio
3. prodotti_univoci;UNICI(prodotti): Qui, "prodotti_univoci" è assegnato alla lista di valori unici della colonna "PRODOTTO". La funzione UNICI rimuove i duplicati.
4. fatturato;TBL_COMPLESSIVA[TOTALE]: Questa parte assegna il nome "fatturato" ai dati nella colonna "TOTALE" della stessa tabella "TBL_COMPLESSIVA".
5. finale;DATI.ORDINA(...): Qui viene definito il nome "finale". Il calcolo associato usa la funzione DATI.ORDINA per ordinare i dati risultanti da una funzione STACK.ORIZ.
6. STACK.ORIZ(prodotti_univici; SOMMA.PIÙ.SE(fatturato; prodotti; prodotti_univoci)): La funzione STACK.ORIZ accosta orizzontalmente due serie di dati. Qui, combina "prodotti_univici" con il risultato della funzione SOMMA.PIÙ.SE, che calcola la somma totale del "fatturato" per ogni "prodotto" unico.
7. 2;-1: Questi sono i parametri della funzione DATI.ORDINA. "2" indica che i dati vengono ordinati in base alla seconda colonna del range (in questo caso, le somme calcolate dalla SOMMA.PIÙ.SE), mentre "-1" indica che l'ordinamento è in ordine decrescente.
8. finale: Dopo aver definito tutti i calcoli intermediari, la funzione LET restituisce il valore di "finale", che è il risultato dell'ordinamento.

Come dicevamo, ci sono diversi modi per arrivare allo stesso risultato. La formula precedente potrà sembrare intuitiva a qualcuno, ma è più probabile che ti sia venuto il mal di testa a seguire i vari passaggi - a me capita se non sono io in prima persona a creare la formula, dato che devo entrare nel ragionamento di qualcun altro e non sempre è assolutamente in linea con quello che avrei seguito io.

Vediamo come arrivare allo stesso risultato con Power Query, semplificando enormemente il processo.

Prima di tutto, dobbiamo portare i valori della nostra tabella su Power Query. In questo caso, possiamo usare (visto che la tabella è sullo stesso file) il comando Dati > Recupera dati da > Da altre origini > Da tabella/intervallo. Se i dati fossero altrove, il ragionamento sarebbe uguale ma cambierebbe il connettore: ad esempio potresti dover selezionare Dati > Recupera dati > Da file > Da testo/CSV o simili.

A questo punto, vediamo che la nostra tabella di partenza si chiama TBL_COMPLESSIVA. Non vogliamo toccarla, quindi clicchiamo sulla query > tasto destro > Riferimento. Riferimento creerà una nuova query cui corrisponderà l'ultimo step della query di partenza, quindi prenderà tutte le modifiche dei dati che avremo fatto nella query chiamata TBL_COMPLESSIVA. Questo è il modo migliore per pescare i dati, dato che riduce il numero di passaggi - se avessimo selezionato Duplica, Power Query avrebbe creato una nuova query identica all'originale e questo potrebbe aumentare di molto il peso dei calcoli, se lavorassimo con database di grandi dimensioni.

A questo punto, rinominiamo la nuova query come preferiamo - in questo caso la nuova tabella si chiamerà TBL_VENDITE_PRODOTTI. Visto che ora abbiamo un nuovo ambiente per lavorare con i nostri dati , possiamo cliccare su Home > Raggruppa per. Si aprirà un menu contestuale per definire come vogliamo creare questo raggruppamento.

Parliamo delle opzioni di base. Puoi selezionare una colonna, in questo caso PRODOTTO, che sarà la nostra chiave per riassumere i dati. Successivamente, puoi definire tre diversi valori:

• Nome nuova colonna: quale sia l'intestazione della nuova colonna
• Operazione: quali sia l'operazione che ti serve
• Colonna: quale sia la colonna sulla quale debba intervenire Power Query per i calcoli

Avevamo visto prima la formula in Excel che comprendeva la parte chiamata "SOMMA.PIÙ.SE(fatturato;prodotti;prodotti_univoci))". Qui il procedimento è simile perchè stiamo dicendo a Power Query che:

• Colonna: vogliamo che la colonna sia PRODOTTO, dato che l'analisi è relativa a questi valori e vogliamo che non vi siano duplicati
• Nome nuova colonna: la chiamiamo TOTALE_FATTURATO visto che parliamo di somma dei totali di fatturato per prodotto
• Operazione: parliamo di somme, quindi selezioniamo Somma
• Colonna: dato che i valori di fatturato sono nella colonna TOTALE, la selezioniamo

Il ragionamento è piuttosto logico e, credo, ben più semplice rispetto alla costruzione della formula vista in precedenza.

Power Query ci restituirà tutti i prodotti con il relativo totale di fatturato. Questo con una velocità non paragonabile alle formule, oltre al vantaggio di essere assolutamente scalabile - se i dati di partenza cambiassero, ci basterebbe aggiornare la query per ottenere i nuovi calcoli senza rischiare di sovraccaricare il nostro PC.

A questo punto, arrotondo i dati finali, giusto per precauzione - ti basterà cliccare sulla colonna TOTALE_FATTURATO e poi andare su Trasforma > Arrotondamento > Arrotonda... per poi scegliere il numero di decimali che ti interessa. Non è un passaggio fondamentale ma può aiutare la parte finale di analisi, riducendo eventuali imprecisioni.

NB: Come giustamente indicato dal mio amico Enrico de Crescenzo, questa procedura va benissimo se ci servono dei dati approssimati - ma se i dati devono tornare al centesimo, se confrontati con i dati iniziali, è meglio evitarla e andare direttamente con le somme senza arrotondare!

A questo punto possiamo ordinare il totale di fatturato cliccando sull'intestazione e selezionando l'opzione Ordinamento decrescente. Avremo quindi tutti i nostri valori ordinati dal più al meno venduto, proprio come volevamo.

Non ci resta che cliccare su Chiudi e carica > Chiudi e carica in... e scegliere come vogliamo caricare questa nuova tabella sul nostro file Excel!

Se lo carichiamo come tabella, avremo una tabella estremamente ridotta con soli 17 prodotti e il loro fatturato, evitando di caricare tutti i dati sul nostro Excel e riducendo la dimensione del file in maniera sostanziale!

Come funziona il codice di Power Query quando raggruppiamo i dati

Come sai, Power Query crea del codice in M per riassumere tutti questi passaggi e puoi vederlo andando su Home > Editor avanzato. Vediamolo nello specifico, prendendo il caso semplice precedente:

let
Origine = TBL_COMPLESSIVA,
#"Raggruppate righe" = Table.Group(Origine, {"PRODOTTO"}, {{"TOTALE_FATTURATO", each List.Sum([TOTALE]), type nullable number}}),
Arrotondato = Table.TransformColumns(#"Raggruppate righe",{{"TOTALE_FATTURATO", each Number.Round(_, 2), type number}}),
#"Ordinate righe" = Table.Sort(Arrotondato,{{"TOTALE_FATTURATO", Order.Descending}})
in
#"Ordinate righe"

Ti lascio anche una breve spiegazione per ciascuno dei passaggi, nel caso volessi scoprire di più su che cosa succeda quando clicchiamo sui singoli pulsanti di Power Query:

1. Definizione dell'origine dei dati: Origine = TBL_COMPLESSIVA: Qui si sta definendo una variabile Origine che è un riferimento alla tabella TBL_COMPLESSIVA. Questa sarà la tabella di partenza per le trasformazioni successive.
2. Raggruppamento delle righe: #"Raggruppate righe" = Table.Group(...): In questo passaggio, la query raggruppa le righe della tabella Origine in base al valore nella colonna "PRODOTTO". Per ogni gruppo unico di "PRODOTTO", calcola la somma dei valori nella colonna "TOTALE". Il risultato è una nuova tabella con due colonne: "PRODOTTO" e "TOTALE_FATTURATO" (quest'ultima contenente la somma di "TOTALE" per ogni prodotto).
3. Arrotondamento dei valori: Arrotondato = Table.TransformColumns(...): Questo passaggio trasforma la colonna "TOTALE_FATTURATO" della tabella risultante dal raggruppamento. Utilizza la funzione Number.Round(_, 2) per arrotondare ogni valore nella colonna "TOTALE_FATTURATO" a due cifre decimali. La colonna mantiene il suo tipo come numero.
4. Ordinamento delle righe: #"Ordinate righe" = Table.Sort(...): Infine, la tabella viene ordinata in base alla colonna "TOTALE_FATTURATO" in ordine decrescente. Questo significa che i prodotti con il fatturato totale più alto saranno elencati per primi.
5. Risultato finale: in #"Ordinate righe": Il risultato finale di questa query è la tabella ordinata, identificata come #"Ordinate righe".

Come raggruppare i dati su Power Query in Excel: caso complesso

Vediamo ora come raggruppare i dati su Power Query con un caso complesso. Stavolta non dobbiamo trovare il totale di fatturato per singolo prodotto ma la domanda è: "Per singolo trimestre, quanto è, per singolo cliente, il totale di fatturato, il totale di set venduti, il numero totale di ordini, la media di fatturato per ordine e la mediana di fatturato per ordine?".

Queste sembrano tantissime informazioni da ridurre in una sola dimensione e farlo con le formule potrebbe essere decisamente faticoso, quindi vediamo direttamente come agire su Power Query.

Anche in questo caso, cominciamo facendo un riferimento della TBL_COMPLESSIVA e la chiamiamo TBL_VENDITE_CLIENTI_QUARTER (visto che quarter è trimestre). Notiamo che il trimestre nella nostra tabella iniziale non esiste, quindi dobbiamo aggiungerlo.

Power Query ha molte opzioni per aiutarci a ottenere valori dal calendario - in questo caso ci basta andare sulla colonna DATA per poi cliccare su Aggiungi colonna > Data > Trimestre > Trimestre dell'anno.

Come vedi, Power Query creerà una nuova colonna, chiamata Trimestre, che avrà un valore numerico da 1 a 4 a seconda del mese di riferimento della singola data.

Ora possiamo andare nuovamente su Home > Raggruppa per e selezionare i valori che ci interessano. Nella sezione Avanzate possiamo ampliare notevolmente le nostre opzioni, dato che:

• Possiamo selezionare più colonne da raggruppare. Qui c'è un ordine gerarchico, quindi quella in cima comanda quelle successive
• Possiamo selezionare più colonne nella parte di calcoli, cliccando su Aggiungi aggregazione

Come vedi, selezioniamo CLIENTE e Trimestre come base dato che vogliamo che questi dati siano riassunti per cliente e per trimestre. Successivamente, selezioniamo le colonne che ci interessano mettendo un nome personalizzato, definendo un'operazione e richiamando la colonna che ci interessa. L'unico caso nel quale non ci sia una colonna è il caso di "Conteggio righe", dato che questa operazione conterà semplicemente il numero di record corrispondenti alla combinazione Cliente + Trimestre selezionata.

A questo punto abbiamo accorpato tutte le informazioni che ci interessano. Come vedi, abbiamo CLIENTE, poi Trimestre e poi tutte le colonne che ci interessano.

Se volessimo ordinare correttamente i dati, dovremmo fare un passaggio aggiuntivo dato che gli ID sono testi. L'ordine per Excel sarebbe quindi ID1, ID10, ID11 e così via. In questo caso potresti aggiungere una colonna da Aggiungi colonna con Colonna da esempi e crearla da CLIENTE, ricavando i vari numeri e poi ordinando tutto l'intervallo prima per questa nuova colonna (che poi cancellerai) e poi per singolo trimestre.

In questo modo avrai un ordine tipo ID1 | 1 / ID1 | 2 / ID1 | 3 / ID1 | 4 / ID2 | 1 e così via, anzichè avere un ordine strano dettato solo dall'ID.

A questo punto abbiamo la nostra tabella riassuntiva con tutti i dati che ci servono e non ci resta che caricarla nuovamente su Excel.

La versione finale è quella che vedi qui nell'immagine! In un colpo solo, possiamo riassumere numerose operazioni velocizzando in maniera esponenziale la creazione dei nostri file.

Come funziona il codice di Power Query quando raggruppiamo i dati in maniera complessa

Vediamo ora che cosa succede lato codice quando facciamo numerose operazioni come in questo caso complesso su Power Query:

let
Origine = TBL_COMPLESSIVA,
#"Inserito trimestre" = Table.AddColumn(Origine, "Trimestre", each Date.QuarterOfYear([DATA]), Int64.Type),
#"Raggruppate righe" = Table.Group(#"Inserito trimestre", {"CLIENTE", "Trimestre"}, {{"TOTALE_FATTURATO", each List.Sum([TOTALE]), type nullable number}, {"TOTALE_SET", each List.Sum([#"#SET"]), type nullable number}, {"MEDIA_ORDINE", each List.Average([TOTALE]), type nullable number}, {"MEDIANA_ORDINE", each List.Median([TOTALE]), type nullable number}, {"NUMERO_ORDINI", each Table.RowCount(), Int64.Type}}),
#"Testo inserito dopo il delimitatore" = Table.AddColumn(#"Raggruppate righe", "Testo dopo il delimitatore", each Text.AfterDelimiter([CLIENTE], "D"), type text),
#"Modificato tipo" = Table.TransformColumnTypes(#"Testo inserito dopo il delimitatore",{{"Testo dopo il delimitatore", Int64.Type}}),
#"Ordinate righe" = Table.Sort(#"Modificato tipo",{{"Testo dopo il delimitatore", Order.Ascending}, {"Trimestre", Order.Ascending}}),
#"Rimosse colonne" = Table.RemoveColumns(#"Ordinate righe",{"Testo dopo il delimitatore"})
in
#"Rimosse colonne"

Più nello specifico, vediamo qui i singoli passaggi:

1. Definizione dell'Origine dei Dati: Origine = TBL_COMPLESSIVA: La query inizia definendo Origine come riferimento alla tabella TBL_COMPLESSIVA. Questa tabella sarà la base per le trasformazioni successive.
2. Inserimento del Trimestre: #"Inserito trimestre" = Table.AddColumn(...): Qui, viene aggiunta una nuova colonna chiamata "Trimestre" alla tabella Origine. Questa colonna è calcolata utilizzando la funzione Date.QuarterOfYear([DATA]), che determina il trimestre dell'anno per ciascuna data nella colonna "DATA".
3. Raggruppamento delle Righe: #"Raggruppate righe" = Table.Group(...): Questo passaggio raggruppa le righe della tabella in base alle colonne "CLIENTE" e "Trimestre". Per ogni combinazione unica di cliente e trimestre, calcola vari aggregati: somma totale di "TOTALE" e "#_SET", media e mediana di "TOTALE", e conta il numero di ordini.
4. Inserimento Testo dopo Delimitatore: #"Testo inserito dopo il delimitatore" = Table.AddColumn(...): Aggiunge una nuova colonna che estrae il testo che segue il carattere "D" nel nome del cliente (usando Text.AfterDelimiter([CLIENTE], "D")).
5. Modifica del Tipo di Dati: #"Modificato tipo" = Table.TransformColumnTypes(...): Cambia il tipo di dati della nuova colonna "Testo dopo il delimitatore" in Int64.Type, suggerendo che questo testo è interpretato come un numero.
6. Ordinamento delle Righe: #"Ordinate righe" = Table.Sort(...): Ordina la tabella prima in base alla nuova colonna "Testo dopo il delimitatore" in ordine ascendente, e poi in base al trimestre, anch'esso in ordine ascendente.
7. Rimozione di Colonne: #"Rimosse colonne" = Table.RemoveColumns(...): Rimuove la colonna "Testo dopo il delimitatore" dalla tabella ordinata.
8. Risultato Finale: in #"Rimosse colonne": Il risultato finale della query è la tabella con le colonne rimosse.

Non è necessario che tu conosca tutti questi comandi, ma potrebbe esserti utile nel caso volessi imparare meglio come lavorare con M in Power Query!

Conclusioni

Come hai visto, raggruppare i dati con Power Query su Excel ha numerosi vantaggi. Prima di tutto, riduce notevolmente la dimensione dei file. Poi, ti permette di creare delle strutture scalabili facilmente. Infine, è facile da verificare, dato che tutto il percorso è tracciato sulle query!

Ti consiglio caldamente di utilizzare questa funzionalità quando hai database grandi, dato che ti aiuterà notevolmente a evitare errori e a arrivare velocemente al risultato delle tue analisi.