Leggere in inglese

Condividi tramite


Trasformare le colonne tramite UnPivot

In Power Query è possibile trasformare le colonne in coppie attributo-valore, in cui le colonne diventano righe.

Diagramma delle colonne unpivot.

Diagramma che mostra la tabella a sinistra con una colonna e righe vuote e i valori attributi A1, A2 e A3 come intestazioni di colonna. In questa tabella la colonna A1 contiene i valori V1, V4 e V7. La colonna A2 contiene i valori V2, V5 e V8. La colonna A3 contiene i valori V3, V6 e V9. Con le colonne unpivot, la tabella destra del diagramma contiene una colonna e righe vuote, una colonna Attributes con nove righe con A1, A2 e A3 ripetute tre volte e una colonna Values con valori da V1 a V9.

Ad esempio, data una tabella come quella seguente, in cui le righe del paese e le colonne di data creano una matrice di valori, è difficile analizzare i dati in modo scalabile.

Tabella iniziale di esempio di colonne unpivot.

Screenshot di una tabella contenente un set di colonne Country nel tipo di dati Text e tre colonne con le date 1 giugno 2023, 1 luglio 2023 e 1° agosto 2023 impostate come tipo di dati Numero intero. La colonna Country contiene Stati Uniti nella riga 1, Canada nella riga 2 e Panama nella riga 3.

È invece possibile trasformare la tabella in una tabella con colonne senza pivot, come illustrato nell'immagine seguente. Nella tabella trasformata è più facile usare la data come attributo per filtrare.

Tabella degli obiettivi di esempio delle colonne unpivot.

Screenshot della tabella contenente un set di colonne Country come tipo di dati Text, una colonna Attribute impostata come tipo di dati Text e una colonna Valore impostata come tipo di dati Numero intero. La colonna Country contiene stati Uniti nelle prime tre righe, Canada nelle tre righe successive e Panama nelle ultime tre righe. La colonna Attribute contiene la data 1 giugno 2023 nella prima, nella quarta e nella settima riga, la data del 1° luglio 2023 nella seconda, quinta e ottava riga e la data del 1° agosto 2023 nella terza, sesta e nona riga.

La chiave di questa trasformazione è che nella tabella è presente un set di date che deve far parte di una singola colonna. Il rispettivo valore per ogni data e paese deve trovarsi in una colonna diversa, creando in modo efficace una coppia attributo-valore.

Power Query crea sempre la coppia attributo-valore usando due colonne:

  • Attributo: nome delle intestazioni di colonna senzapivot.
  • Valore: i valori sottostanti a ognuna delle intestazioni di colonna senzapivot.

Nell'interfaccia utente sono presenti più posizioni in cui è possibile trovare colonne Unpivot. È possibile fare clic con il pulsante destro del mouse sulle colonne da annullare oppure selezionare il comando nella scheda Trasforma della barra multifunzione.

Screenshot dell'operazione di clic con il pulsante destro del mouse per annullare ilpivot delle colonne.

Screenshot del comando Unpivot columns (Colonne unpivot) nella scheda Transform (Trasforma).

Esistono tre modi per annullare il pivot delle colonne da una tabella:

  • Trasformare le colonne tramite UnPivot
  • Annullare ilpivot di altre colonne
  • Deselezionare solo le colonne selezionate

Trasformare le colonne tramite UnPivot

Per lo scenario descritto in precedenza, è prima necessario selezionare le colonne da annullare. È possibile selezionare CTRL quando si seleziona il numero di colonne desiderate. Per questo scenario, si vogliono selezionare tutte le colonne ad eccezione di quella denominata Country. Dopo aver selezionato le colonne, fare clic con il pulsante destro del mouse su una delle colonne selezionate e quindi scegliere Unpivot columns (Annulla pivot).

Viene illustrato come annullare ilpivot delle colonne di data nella tabella.

Screenshot della tabella con le colonne selezionate il 1° giugno 2023, il 1° luglio 2023 e il 1° agosto 2023 e il comando Unpivot columns selezionato nel menu di scelta rapida".

Il risultato di tale operazione restituisce il risultato illustrato nell'immagine seguente.

Tabella finale delle colonne unpivot.

Considerazioni speciali

Dopo aver creato la query dai passaggi precedenti, si supponga che la tabella iniziale venga aggiornata in modo che sia simile allo screenshot seguente.

Tabella di origine aggiornata delle colonne unpivot.

Screenshot della tabella con le stesse colonne di data Country, 1 giugno 2023, 1 luglio 2023 e agosto 2023, con l'aggiunta di una colonna data 1 settembre 2023. La colonna Country contiene ancora i valori USA, Canada e Panama, ma ha anche aggiunto il Regno Unito alla quarta riga e al Messico alla quinta riga.

Si noti che si aggiunge una nuova colonna per la data 1° settembre 2023 (1/09/2023) e due nuove righe per i paesi/aree geografiche Regno Unito e Messico.

Se si aggiorna la query, si noti che l'operazione viene eseguita sulla colonna aggiornata, ma non influisce sulla colonna selezionata in origine (Country, in questo esempio). Ciò significa che anche qualsiasi nuova colonna aggiunta alla tabella di origine è unpivot.

L'immagine seguente mostra l'aspetto della query dopo l'aggiornamento con la nuova tabella di origine aggiornata.

Unpivot columns final updated table .Unpivot columns final updated table.Unpivot columns final updated table.

Screenshot della tabella con le colonne Country, Attribute e Value. Le prime quattro righe della colonna Country contengono Stati Uniti, le seconde quattro righe contengono il Canada, le terze quattro righe contengono Panama, le quattro righe contengono il Regno Unito e le cinque quattro righe contengono il Messico. La colonna Attribute contiene le date 1 giugno 2023, 1 luglio 2023 e agosto 2023 nelle prime quattro righe, che vengono ripetute per ogni paese.

Annullare ilpivot di altre colonne

È anche possibile selezionare le colonne che non si desidera annullare e annullare ilpivot delle altre colonne della tabella. Questa operazione è la posizione in cui entra in gioco altre colonne Unpivot.

Screenshot della tabella con il menu di scelta rapida della colonna Country selezionato e viene evidenziato il comando Unpivot other columns nel menu.

Il risultato di tale operazione restituisce esattamente lo stesso risultato di quello ottenuto dalle colonne Unpivot.

Annullare ilpivot di altre colonne della tabella degli obiettivi di esempio.

Screenshot della tabella contenente un set di colonne Country come tipo di dati Text, una colonna Attribute impostata come tipo di dati Text e una colonna Valore impostata come tipo di dati Numero intero. La colonna Country contiene stati Uniti nelle prime tre righe, Canada nelle tre righe successive e Panama nelle ultime tre righe. La colonna Attribute contiene la data 1 giugno 2023 nella prima, nella quarta e nella settima riga, la data del 1° luglio 2023 nella seconda, quinta e ottava riga e la data del 1° agosto 2023 nella terza, sesta e nona riga.

Nota

Questa trasformazione è fondamentale per le query con un numero sconosciuto di colonne. L'operazione annulla il pivot di tutte le colonne della tabella, ad eccezione di quelle selezionate. Si tratta di una soluzione ideale se l'origine dati dello scenario ha ottenuto nuove colonne di data in un aggiornamento, perché verranno prelevate e annullate le operazioni.

Considerazioni speciali

Analogamente all'operazione Unpivot columns , se la query viene aggiornata e vengono prelevati altri dati dall'origine dati, tutte le colonne vengono annullate, ad eccezione di quelle selezionate in precedenza.

Per illustrare questo processo, si supponga di avere una nuova tabella come quella nell'immagine seguente.

Tabella di origine unpivot di esempio.

Screenshot della tabella con le colonne Country, 1 giugno 2023, 1 luglio 2023, 1 agosto 2023 e 1° settembre 2023 con tutte le colonne impostate sul tipo di dati Text. La colonna Country contiene, dall'alto verso il basso, Stati Uniti, Canada, Panama, Regno Unito e Messico.

È possibile selezionare la colonna Country e quindi selezionare Unpivot other column (Unpivot other column), che restituisce il risultato seguente.

Esempio di unpivot di altre colonne aggiornate tabella finale.

Screenshot della tabella con le colonne Country, Attribute e Value. Le colonne Country e Attribute sono impostate sul tipo di dati Text. La colonna Valore è impostata sul tipo di dati Valore intero. Le prime quattro righe della colonna Country contengono Stati Uniti, le seconde quattro righe contengono il Canada, le terze quattro righe contengono Panama, le quattro righe contengono il Regno Unito e le cinque quattro righe contengono il Messico. La colonna Attribute contiene il 1° giugno 2023, il 1° luglio 2023, l'1 agosto 2023 e il 1° settembre 2023 nelle prime quattro righe, ripetute per ogni paese.

Deselezionare solo le colonne selezionate

Lo scopo di questa ultima opzione è annullare ilpivot solo di colonne specifiche della tabella. Questa opzione è importante per gli scenari in cui si sta gestendo un numero sconosciuto di colonne dall'origine dati e si vuole annullare ilpivot solo le colonne selezionate.

Per eseguire questa operazione, selezionare le colonne da annullare, che in questo esempio sono tutte le colonne ad eccezione della colonna Country . Fare quindi clic con il pulsante destro del mouse su una delle colonne selezionate e quindi selezionare Unpivot only selected columns (Annulla pivot solo colonne selezionate).

Screenshot della tabella con le colonne di data selezionate e le colonne selezionate solo unpivot evidenziate.

Si noti che questa operazione restituisce lo stesso output degli esempi precedenti.

Unpivot solo colonne selezionate tabella finale.

Screenshot della tabella contenente un set di colonne Country come tipo di dati Text, una colonna Attribute impostata come tipo di dati Text e una colonna Valore impostata come tipo di dati Numero intero. La colonna Country contiene stati Uniti nelle prime tre righe, Canada nelle tre righe successive e Panama nelle ultime tre righe. La colonna Attribute contiene la data del 1° giugno 2023 nella prima, nella quarta e nella settima riga, la data del 1° luglio 2023 nella seconda, quinta e ottava riga e il 1° agosto 2023 nella terza, sesta e nona riga.

Considerazioni speciali

Dopo aver eseguito un aggiornamento, se la tabella di origine cambia per avere una nuova colonna 9/1/2020 e nuove righe per Regno Unito e Messico, l'output della query è diverso dagli esempi precedenti. Si supponga che la tabella di origine, dopo un aggiornamento, cambi la tabella nell'immagine seguente.

Screenshot della tabella di origine aggiornata unpivot per le colonne selezionate solo unpivot.

L'output della query è simile all'immagine seguente.

Screenshot della tabella finale aggiornata solo delle colonne selezionate unpivot.

È simile a questo perché l'operazione unpivot è stata applicata solo alle colonne 1/06/2020, 7/1/2020 e 8/1/2020 , quindi la colonna con l'intestazione 9/1/2020 rimane invariata.