Come utilizzare Power Query e Power Pivot in Excel come un professionista

Se vuoi essere un vero esperto di fogli di calcolo, devi imparare a conoscere le funzionalità Power Query e Power Pivot in Excel. Anche se puoi fare molto con Excel da solo, utilizzare queste funzionalità “Power” integrate ti trasformerà in un utente avanzato di Excel. In questa guida, imparerai a utilizzare Power Query per importare potenzialmente centinaia di file diversi con milioni di righe e utilizzare Power Pivot per generare analisi complesse sui massicci modelli di dati che importi.

Indice

• 1. Importazione di dati da più file
• 2. Filtrare i file e le righe importate
• 3. Caricare i dati filtrati in un modello di dati
• 4. Analizzare i dati con PivotTable
• 5. Presentare i tuoi dati con PowerChart
• Domande frequenti

Vuoi proteggere il tuo lavoro da occhi curiosi? Scopri come aggiungere una password al tuo file Excel.

1. Importazione di dati da più file

Utilizzando Power Query, puoi importare dati da file diversi in una singola cartella. Fai attenzione, però, poiché dovresti comunque assicurarti che tutti i dati seguano lo stesso formato. Mantieni lo stesso numero di colonne e i nomi delle intestazioni delle colonne e assicurati che il tipo di dati per i valori in ciascuna colonna sia coerente.

Per questa guida, stiamo utilizzando cinque file che comprendono ~100 milioni di righe di dati da Kaggle. Scaricali se vuoi seguire o utilizza i tuoi dati. Tenendo presente ciò, ecco i passaggi per importare i dati:

1. Fai clic sulla scheda “Dati” nella barra multifunzione.
2. Seleziona “Ottieni dati -> Da file -> Da cartella”, quindi sfoglia la tua cartella e selezionala.

1. Dovresti vedere apparire una nuova finestra. Nel nostro caso, abbiamo cinque file di test che sono denominati in base alla loro dimensione: “adult10m” ha 10 milioni di righe di dati, “adult1m” ha 1 milione di righe, “adult100k” ha 100.000 righe e così via. Fai clic su “Trasforma dati” per aprire l’Editor di Power Query.

2. Filtrare i file e le righe importate

Nell’Editor di Power Query, puoi applicare filtri per importare solo i file che corrispondono ai criteri che hai impostato, il che è molto utile per eliminare le fonti di dati che non desideri.

1. Abbiamo quattro file senza estensione, ma un file con l’estensione .DATA. Rimuoviamo quello strano facendo clic sulla freccia accanto a “Estensione”.

1. Possiamo vedere un elenco di tutte le estensioni dei file. In questo esempio, queste sono solo (vuoto) e .data. Deseleziona le estensioni che non desideri.

1. Ogni filtro che abbiamo applicato apparirà nella sezione “Passaggi applicati”.
2. Possiamo anche filtrare per nome del file facendo clic sulla freccia accanto a “Nome”.

1. Supponiamo di voler filtrare eventuali file con “k” in essi, come “adult100k”, perché non vogliamo lavorare con file piccoli contenenti meno di un milione di righe. Possiamo applicare un filtro di testo. Ci sono molte opzioni, ma per i nostri scopi, il filtro “Non contiene” è quello giusto.

1. Digita il testo che non vuoi vedere nel nome del file.

1. Scorri un po’ a destra finché non puoi vedere le colonne “Data modificata” e “Data creata”. Filtriamo per selezionare solo i file creati in un certo intervallo di tempo. Fai clic sulla freccia accanto a “Data creata”.

1. Scegli “Filtri Data/Ora -> Tra”.

1. Digita i valori di tempo per le due condizioni e conferma premendo “OK”.

1. Abbiamo completato il filtraggio dei nostri dati a livello di file.
2. Combiniamo i file rimanenti in modo da poter filtrare i dati stessi. Fai clic sulle doppie frecce accanto all’intestazione “Contenuto”.

1. Ora che puoi vedere tutti i valori per le righe individuali, ogni riga ha una colonna aggiuntiva, “Source.Name”, che indica il file da cui proviene.

1. Per migliorare ulteriormente i nostri dati: poiché i nostri file CSV originariamente non avevano nomi di intestazione delle colonne, puoi fare clic con il tasto destro su alcune delle intestazioni delle colonne e fare clic su “Rinomina” per dare loro un nome appropriato.

1. Anche i filtri funzionano qui. Possiamo applicare un filtro di intervallo numerico. Ad esempio, fai clic sulla freccia accanto all’intestazione della colonna “Età”, quindi scegli “Filtri numerici -> Maggiore di”.

1. Inserisci un numero come 30, quindi fai clic su “OK”. Tutte le righe con età inferiori al numero specificato saranno escluse.

Suggerimento: quando lavori con fogli di calcolo in Microsoft Excel, potrebbe essere utile sapere come spostare una colonna.

3. Caricare i dati filtrati in un modello di dati

Puoi continuare a pulire e filtrare i dati, ma passiamo a caricarli in un modello di dati per iniziare ad analizzarli.

1. Completa con Power Query facendo clic su “Chiudi e carica -> Chiudi e carica in”.
2. Nella finestra pop-up che si apre, seleziona “Crea solo connessione” e seleziona “Aggiungi questi dati al modello di dati”.

1. Aspetta che i dati vengano caricati. Questo può richiedere alcuni minuti se hai una grande quantità di dati.

Utilizzare il modello di dati Power Pivot in Excel

1. Dopo che il caricamento dei dati è completato, possiamo apportare modifiche (come aggiungere nuove colonne) al modello di dati facendo clic su “Dati -> Strumenti dati -> Gestisci modello di dati”.

1. Dovresti vedere la finestra di Power Pivot. Aggiungiamo una colonna con una formula di Data Analysis Expressions (DAX). Le formule DAX sono molto simili alle formule di Excel con cui probabilmente hai già familiarità. Scorri a destra finché non vedi l’ultima colonna dei tuoi dati, quindi fai clic su “Aggiungi colonna”.
2. Scriviamo una formula per questa colonna che applica un calcolo semplice a ogni riga. Rispetto alle formule di Excel, la sintassi DAX è leggermente diversa. Ad esempio, possiamo aggiungere una colonna chiamata “Descrizione” e inserire la formula =CONCATENATE([Column9],[Column10]). Nota l’uso delle parentesi quadre ([ ]) e l’accesso tramite i nomi delle intestazioni delle colonne.

1. Se lo desideri, puoi anche eseguire ulteriori modifiche come rinominare o rimuovere colonne facendo clic con il tasto destro sull’intestazione della colonna.

4. Analizzare i dati con PivotTable

Dopo aver terminato di apportare modifiche al modello di dati, è tempo di analizzare i dati in un foglio di lavoro Excel utilizzando PivotTable.

1. Fai clic su “Home -> PivotTable” sulla barra multifunzione della finestra di Power Pivot per aprire la finestra “Crea PivotTable”.

1. Seleziona “Nuovo foglio di lavoro” e fai clic su “OK”.

1. Il pannello dei campi PivotTable si aprirà in un nuovo foglio nella finestra principale di Excel. Nella casella sotto la barra di ricerca, dovresti vedere elencate le tue fonti di dati, “kaggle_adult_csv” in questo esempio. Fai clic su di essa per espanderla e visualizzare tutte le sue colonne.

1. Facciamo un’analisi in cui contiamo il numero di ciascuna “Descrizione” nei nostri dati. Possiamo selezionare le colonne con cui vogliamo lavorare facendo clic sulla casella di controllo accanto ai loro nomi. Seleziona “Descrizione” e nota come il focus si sposta automaticamente nell’area “Righelli” sottostante. Questo è Power Pivot che dimostra la sua intelligenza: può indovinare accuratamente l’area appropriata per le selezioni. Poiché stiamo partendo da una PivotTable vuota, ha senso che la nostra prima selezione sia righe.

1. Trascina la colonna “Descrizione” selezionata nello spazio vuoto in “Valori”. Nota che Power Pivot assume nuovamente correttamente che vogliamo un “Conteggio di Descrizione” a causa del tipo di dati che è testo. Inoltre, la nostra tabella è stata aggiornata con una colonna che fornisce i conteggi di ciascuna occorrenza di ciascun valore di “Descrizione”.

1. Possiamo aggiungere un’altra dimensione al nostro conteggio. Ad esempio, trascina la colonna “Età” nell’area “Colonne” vuota per aggiungere più dettagli alla tabella contando il numero di descrizioni per ogni età.

1. Puoi anche aggiungere la possibilità di selezionare un filtro alla tabella trascinando la colonna “WorkClass” nell’area “Filtri”. Nota la nuova cella interattiva che appare sopra la nostra tabella.

1. Fai clic sulla freccia accanto a “Tutti”, quindi seleziona “Mai lavorato”. Questo cambierà la tabella per includere solo le persone con un WorkClass di “Mai lavorato” nei calcoli. Puoi cambiare il filtro in qualsiasi momento per visualizzare la tabella attraverso una lente diversa.

Hai a che fare con molti duplicati nel tuo file Excel? Scopri come trovare e rimuovere duplicati e pulire i tuoi fogli di calcolo.

5. Presentare i tuoi dati con PowerChart

Puoi creare più fogli di lavoro per analizzare i tuoi dati in modi diversi. Questa volta, creiamo un risultato più visivamente impressionante utilizzando PowerChart.

1. Torna alla finestra “Power Pivot per Excel”. Se è chiusa, puoi aprirla dalla finestra di Excel tramite “Dati -> Strumenti dati -> Gestisci modello di dati”.
2. Fai clic su “PivotTable -> PivotChart -> Nuovo foglio di lavoro”.

1. Nel pannello a destra, fai clic sulla fonte di dati (“kaggle_adult_csv”) per espanderla e visualizzare tutte le sue colonne.
2. Trascina e rilascia le colonne dalla fonte di dati in una delle quattro aree sottostanti per costruire il grafico. Sposta la colonna “OccupationType” nell’area “Asse (Categorie)”.

1. Trascina di nuovo “OccupationType” e questa volta rilascialo nell’area “Valori”. Noterai immediatamente che viene generato automaticamente un grafico a barre. Mostra il conteggio di ciascun tipo di occupazione.

1. Puoi anche trascinare la colonna “Ethnicity” nell’area “Legenda (Serie)”. Vedrai immediatamente un grafico più dettagliato che confronta quanti di ciascuna etnia compongono ciascuna occupazione.

1. Successivamente, possiamo applicare filtri alla nostra Legenda per semplificare il nostro grafico e guardare solo determinati valori. Sposta il mouse su “Ethnicity” in modo che appaia una freccia sul lato destro. Fai clic sulla freccia.

1. Fai clic sulle caselle di controllo accanto ai valori che desideri rimuovere, lasciando solo due.

1. Proviamo qualcosa di nuovo. Nella sezione “Valori”, fai clic su “Conteggio di OccupationType”, quindi fai clic su “Rimuovi campo”. Trascina e rilascia la colonna “Età” per sostituirla.

1. Noterai che il valore viene automaticamente definito come “Somma di Età”, ma questo non è molto utile nel mondo reale. Cambiamo questo. Fai clic su di esso e seleziona “Impostazioni campo valore”.

1. Poiché l’età è un numero, ci sono molti calcoli che possiamo applicare a questa sezione. Prova a selezionare “Media” ad esempio.

1. La visualizzazione verrà aggiornata per mostrare le età medie per ciascuna occupazione per etnie specifiche (che abbiamo filtrato).

Vuoi rinfrescarti sulle nozioni di base di Excel? Abbiamo una guida completa su tutte le scorciatoie da tastiera per Excel.

Domande frequenti

Quali sono le origini di Power Pivot?

Microsoft ha introdotto Power Pivot come un componente aggiuntivo separato fornito dal SQL Server 2008 R2 di Microsoft, rilasciato nel 2010. All’epoca, era chiamato “PowerPivot” senza spazi. Nel 2013 è stato rinominato “Power Pivot”. È diventato una funzionalità integrata di Excel solo a partire dal 2016.

Cosa sono le espressioni di analisi dei dati e come si differenziano dalle formule di Excel?

Le espressioni di analisi dei dati sono un linguaggio di formula utilizzato da Power Pivot in Excel e da Power BI. In sostanza, è il modo in cui scrivi formule per calcoli nelle applicazioni sopra menzionate: le dichiarazioni, la sintassi e le funzioni che utilizzi.

Il principale vantaggio di DAX rispetto alle formule di Excel è che è progettato per operare su dati aggregati. Le formule standard di Excel possono eseguire calcoli su base riga per riga (come calcolare la somma di più valori in una riga), ma le formule DAX possono operare su base colonna per colonna (come contare il numero di un valore specifico in una colonna), o anche su un’intera tabella.

Come posso risolvere l’errore “Questa tabella è stata creata da una query. Per modificare questa tabella, modifica invece la query”?

Questo errore in Excel Power Pivot si verifica quando provi a modificare un modello di dati all’interno di Power Pivot quando quel modello di dati è stato originariamente caricato tramite Power Query. Non puoi farlo, quindi devi modificarlo tramite il metodo originale di caricamento: Power Query.

Sul lato destro di Excel, fai clic sull’icona “Query e connessioni”, quindi trova e fai doppio clic sulla tua fonte di dati della query per aprire la finestra in cui puoi modificare la tabella.

Crediti immagine: Pexels. Tutti gli screenshot di Brandon Li.