fbpx
.st0{fill:#FFFFFF;}

Excel Avanzato, Strumenti analisi dati

Power Query: come aggregare valori

 Novembre 21, 2020

By  Damiano Causale

  • Home
  • Power Query: come aggregare valori

In quest'articolo voglio mostrarti come utilizzare il comando Raggruppa per in Power Query.

L'utilizzo di questo comando può essere utile soprattutto nel caso che sto per descrivere.

Immagina di avere una situazione simile.

Valori da raggruppare in Excel

Nella prima colonna abbiamo il settore aziendale mentre nella seconda colonna sono presenti gli anni.

L’obbiettivo è riepilogare i dati, in modo che sia disponibile una sola riga per ogni Settore. Mentre nella seconda colonna dovrebbero essere indicati gli anni per ciascun Settore.

In altri termini, ciò che voglio ottenere è il seguente risultato:

Raggruppare valori in Excel con Power Query

Questo può essere fatto utilizzando l'interfaccia dell’Editor di Power Query.

Il comando Raggruppa per in Power Query

Il comando Raggruppa per, consente di riepilogare i dati e aggregare i valori sottostanti.

Le aggregazioni standard disponibili sono le seguenti:

  • Somma
  • Media
  • Mediana
  • Minimo
  • Massimo
  • Conteggio righe
  • Conteggio righe distinte
  • e Tutte le righe
Funzioni di aggregazione standard in Power Query

La maggior parte di queste modalità di aggregazione sono destinati ai numeri. Tuttavia, in Power Query è possibile eseguire diverse operazioni anche per il testo.

In questo articolo imparerai come raggruppare i tuoi dati e concatenare i valori di testo in una singola cella.

Bene, procediamo!

Innanzitutto ciò di cui abbiano bisogno è l’interfaccia dell’Editor di Power Query. Per richiamarla, facciamo un clic all’interno di una cella della tabella. Successivamente facciamo un clic sul comando Da tabella/intervallo presente nel gruppo Recupera e trasforma dati della scheda Dati.

Recuperare e trasformare dati da tabella Excel

A questo punto Excel aprirà la finestra di Power Query Excel.

Ora dobbiamo fare in modo che Excel tratti gli anni come del testo e non come numeri per poterli facilmente concatenare.

Quindi, selezioniamo la colonna Anno e premiamo il tasto destro del mouse. Infine, dal menu contestuale selezioniamo la voce Testo.

Modificare il formato di un dato in Power Query Excel

A questo punto Excel mostrerà un ulteriore avviso.

Modificare il formato di un dato in Power Query Excel

Facciamo clic sul comando Sostituisci corrente.

Il risultato sarà il seguente:

Modificare il formato di un dato in Power Query Excel

Ora, passiamo alla fase del raggruppamento dei dati.

Vai sulla scheda Home e fai clic sul comando Raggruppa per presente nel gruppo Trasforma dell’Editor di Power Query Excel.

Aggregare valori in Excel

Excel mostrerà la seguente finestra.

Comando Raggruppa per in Power Query Excel

A questo punto, seleziona l’opzione Avanzate. Andiamo ad apportare le seguenti modifiche:

Comando Raggruppa per in Power Query Excel
Comando Raggruppa per in Power Query Excel

I dati sono ora raggruppati per Settore e vengono aggiunti gli anni.

Tuttavia, come potrai notare, l'ultima colonna contiene un errore. La ragione è piuttosto semplice: è stato richiesto a Power Query di sommare dei valori di testo.

Chiaramente, l'operazione di somma funziona solo sui numeri.

Messaggio di errore in Power Query

Il codice M di Power Query Excel

Sino ad ora abbiamo proceduto attraverso l’utilizzo dall'interfaccia utente. Ora dovremmo intervenire attraverso una modifica del codice M presente sulla barra della formula.

Fortunatamente possiamo correggere la formula sbagliata senza dover scrivere tutto da zero.

Prima di procedere alla modifica soffermiamoci un attimo all’analisi del comando Table.Group presente nella barra della formula.

Questo comando consente raggruppare i dati.

All’interno di Table.Group abbiamo tre elementi.

  • Una tabella attraverso il riferimento #"Modificato Tipo"
  • La colonna in base alla quale raggruppare i dati. In questo caso la colonna Settore rappresenta la chiave per raggruppare i dati.
  • La colonna aggregata come elenco attraverso la formula. Nell'esempio la formula List.Sum viene utilizzata per sommare gli Anni.

Come abbiamo già accennato in precedenza non è possibile sommare dei valori di testo.

Pertanto occorre utilizzare una formula di aggregazione differente che permetta di combinare testo.

In questo caso faremo ricorso alla formula Text.Combine di Power Query.

Questa formula consente di combinare del testo ed accetta un elenco come input.

Il risultato sarà un valore di testo ottenuto dall'unione di tutti i valori di testo (gli anni nell’esempio). Ciascun valore concatenato può essere separato utilizzando un separatore.

Ora che conosciamo quale formula utilizzare per aggregare il testo possiamo procedere alla modifica del codice M presente nella barra della formula di Power Query.

Dobbiamo incorporare la funzione Text.Combine nella formula contenente l'errore. La parte della formula che restituisce un errore è la seguente:

Il codice M di Power Query Excel

Il codice precedente dovrà essere modificato nel modo seguente:

Il codice M di Power Query Excel

Il primo argomento indica la colonna da combinare (nell’esempio, la colonna [Anno]).

Il secondo argomento indica quale separatore aggiungere tra il testo (nel nostro esempio inseriremo un punto e virgola e uno spazio ("; ")).

Dopo aver premuto Invio, il risultato sarà che gli anni saranno tutti all'interno della stessa cella e non restituiranno più un errore.

La funzione Text.Combine in Power Query

Per completare il tutto ora andremo a importare i dati all’interno del foglio di lavoro selezionando il comando Chiudi e carica in.

Editor di Power Query

Dalla finestra di dialogo Importa dati scegliamo l’opzione In una tabella e specifichiamo a partire da quale cella inserire i dati (nell’esempio, la cella D1).

Importare dati  in un foglio di lavoro Excel

Ed ecco, infine, che il nostro obiettivo è stato raggiunto!

Aggregare valori attraverso il comando Raggruppa per in Power Query

Spero che questo articolo ti sia utile.

Se vuoi lascia un commento!

Vuoi saperne di più su Power Query Excel?

Allora potrebbero interessarti i seguenti articoli:

Damiano Causale


Mi chiamo Damiano Causale, istruttore Microsoft certificato, per lavoro aiuto aziende e privati nell'utilizzo di Excel.

Damiano Causale

Your email address will not be published. Required fields are marked

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}