Sfrutta Excel Power Query per migliorare la performance aziendale
Autore: Andrea Pacchiarotti
Ultimo aggiornamento: 24 Gennaio 2023
Categoria: Microsoft Office Excel Power Query
Microsoft Power Query per Excel consente di recuperare, estrarre e modellare visivamente i dati provenienti da una vasta gamma di fonti ed è utile per integrare sistemi di business intelligence, insieme a Power Pivot, un'evoluzione delle tabelle pivot.
Se ci si vuole esercitare con le nozioni successive è possibile scaricare il seguente file compresso trovato in Rete e leggermente modificato. Partiamo ora con Excel: Power Query e Business Intelligence.
Sommario Power Query
- Importa più file Excel in un singolo file con Power Query
- CERCA.VERT con corrispondenza esatta con Power Query
- Unpivot con Power Query
- Query di accodamento con Power Query
- Conclusioni
Importa più file Excel in un singolo file con Power Query
È molto semplice importare più file Excel in un file Excel usando Power Query, quando i campi e il nome del foglio sono i medesimi (i file d’esempio si chiamano A.xlsx e B.xlsx).
Il processo inizia dalla scheda Dati icona Recupera dati Da file e quindi Da cartella.
Una finestra chiederà di cercare la cartella da cui importare. Successivamente verrà mostrata una finestra di anteprima che visualizzerà i file da quella cartella.
Facendo clic su Trasforma dati si accederà all’editor di Power Query e si potrà fare qualche cambiamento per garantire che vengano importati solo i file Excel, in caso nella cartella ci fossero anche altri tipi di file.
Per farlo bisogna cliccare sulla freccia del filtro per la colonna Estensione, scegliere .xlsx e poi fare Ok.
Si possono fare ulteriori filtri, ad esempio in base al Nome per escludere determinati file Excel.
Ora è possibile combinare i file e aggiungere i dati di ciascuno in un unico file Excel.
Cliccare sull’icona Combina file, la doppia freccia verso il basso nell'intestazione della colonna Contenuto, o sull’icona nel ribbon, accertandosi che i file siano selezionati (se non lo fossero basta cliccare l’intestazione della prima colonna).
Compare la finestra Combina file che mostra i fogli delle cartelle di lavoro (in alto c’è un elenco a discesa che di default mostra Primo file, da lì scegliere il file da cui prelevare il foglio, che si visualizzerà nella finestra, poi ripetere l’operazione per gli altri file); se desiderato, selezionare in basso a sinistra la casella di controllo Ignora i file con errori per evitare eventuali errori che incidono sul processo di importazione (ad esempio un file è aperto o il nome del foglio è diverso per i vari file). Cliccare Ok (attendere un tempo più o meno lungo a seconda di quanti file si è chiesto di importare).
I dati dei file sono impilati uno sopra l'altro in un unico elenco.
Facendo tasto destro su un’intestazione di colonna sono possibili ulteriori comandi.
È quindi possibile cliccare sulla scheda Home l’icona Chiudi e carica per copiare i dati in una cartella di lavoro nuova o esistente.
Quando vengono modificati i dati nei file di origine, il file chiuso e caricato si aggiorna automaticamente (se così non fosse cliccare sulla scheda Dati, di quest’ultimo file, l’icona Aggiorna tutti).
CERCA.VERT con corrispondenza esatta con Power Query
Ecco come simulare il classico CERCA.VERT per restituire i dati da una tabella di ricerca (Prezzi) in una tabella principale (Membri). Il file si chiama Unione.xlsx.
Stando sulla tabella Membri, andare su Dati Da Tabella Intervallo, si apre l’editor di Power Query, cliccare l’icona Chiudi e Carica, e poi Chiudi e carica in…, scegliere l’opzione, Crea solo connessione e cliccare Ok. Fare la stessa cosa per la tabella Prezzi.
Avvio la query di unione cliccando sulla scheda Dati Recupera dati Combina query Unisci per aprire la finestra Unisci. Nel primo elenco a discesa selezionare la tabella Membri e nel secondo elenco la tabella Prezzi.
Ho quindi bisogno di specificare le colonne da ogni tabella che voglio abbinare. Per farlo cliccare sulla colonna in ciascuna tabella, iniziando dalla prima tabella (cliccando sull’intestazione Membership Type), per finire con la seconda (cliccando sull’intestazione Membership).
Nota che nella tabella Prezzi, la colonna che cerco è la prima, ma potrebbe essere qualsiasi colonna della tabella, a differenza di quando si utilizza CERCA.VERT.
Lascia selezionato Left Outer (cioè il tipo di join esterno sinistro). Il tipico CERCA.VERT restituirà tutti i valori dalla tabella principale e solo quelli corrispondenti alla seconda.
Se non trova una corrispondenza, restituisce un valore nullo.
Quando si fa clic su OK, si viene indirizzati a Power Query Editor il quale dice che trova 92 righe su 92.
Nelle Proprietà in alto a destra immettere un nome significativo per la query (ad esempio Combinato); questo nome verrà utilizzato come nome della tabella quando verrà caricato nuovamente nella cartella di lavoro.
Clicca sull’icona con 2 frecce nell'intestazione della colonna Prezzi per selezionare le colonne da importare nella tabella principale (Membri).
Desidero solo la colonna Prezzo (anche se potrei selezionare tutte le colonne desiderate senza ulteriori CERCA.VERT) sulla quale metto quindi il segno di spunta.
Deseleziono l'opzione Usa il nome della colonna originale come prefisso e clicco Ok per importare la colonna.
Cliccare Chiudi e carica per caricare la tabella in un foglio di lavoro.
Quando i dati in una delle due tabelle cambiano in futuro, la query può essere aggiornata cliccando sul pulsante Aggiorna nella scheda Dati.
Unpivot con Power Query
Lo scopo è convertire la tabella di sinistra in quella di destra per analizzare meglio i dati.
Clicca una cella della tabella e formatta l'intervallo di celle come una tabella dalla scheda Dati, icona Da Tabella/Intervallo Ok.
La tabella viene caricata in Power Query dove le celle vuote vengono visualizzate come valori null e i nomi delle città come intestazioni.
In alto a destra nominare la query come Vendite.
Seleziono le colonne per le quali voglio annullare la rotazione (da Boston a Paris) e clicco sulla scheda Trasforma Trasforma colonne tramite Unpivot (converte tutte le colonne tranne quelle attualmente deselezionate in coppie attributo-valore), le 4 colonne vengono annullate. Ora abbiamo una tabella con ogni riga come vendita. I valori null sono stati omessi.
Adesso è possibile rinominare le intestazioni di colonna facendo doppio clic sull'intestazione dell'attributo e rinominandolo Città.
Cliccare Chiudi e carica dalla scheda Home per caricare la tabella in un foglio di lavoro.
Quando i dati in una delle due tabelle cambiano in futuro, la query potrà essere aggiornata cliccando sul pulsante Aggiorna nella scheda Dati .
Quindi la vera bellezza di questa funzione non sta solo nella sua semplicità, ma anche nel fatto che possiamo farlo di nuovo con il clic di un pulsante in futuro.
Query di accodamento con Power Query
Supponiamo di avere un file con 4 fogli denominati Francia, Regno Unito, Spagna e Italia. Ognuno contiene dati di vendita e si desidera combinarli in un unico foglio di lavoro per l'analisi. Si immagini che questo sia un processo regolare, ad esempio settimanale: questi dati vengono scaricati o ricevuti in qualche modo da una fonte esterna e devono essere combinati in un file velocemente e semplicemente.
I dati su ogni file sono formattati come tabella (Home Formatta come tabella) in modo che quando si aggiungono nuovi dati ai fogli di lavoro, la tabella si espanderà automaticamente.
Per creare le query cliccare nella tabella e poi sulla scheda Dati Da tabella intervallo. È possibile assegnare un nome alla query nelle impostazioni a destra: per default prende il nome della tabella. Poi cliccare su Chiudi e carica Chiudi e carica in. Selezionare Crea solo connessione e infine OK.
Ripetere questi passaggi per ciascun foglio di lavoro tabella.
Al termine ogni query (Francia, Spagna, Regno Unito e Italia) sarà visibile nel riquadro Query e connessioni sulla destra.
Per aggiungerli tutti all’interno di un file bisogna cliccare Dati Recupera dati Combina query Aggiungi. Viene visualizzata la finestra Aggiungi. Selezionare l’opzione (Tre o più tabelle) e poi le 4 tabelle, dopo cliccare Aggiungi e OK.
Comparirà la finestra dell'Editor di query che mostra le tabelle aggiunte. Modifica il nome della query nella finestra Impostazioni query a destra. In questo esempio, è stato nominato tutti i paesi.
Cliccare Chiudi e carica per caricare la tabella in un foglio di lavoro.
Quando i dati in una delle due tabelle cambiano in futuro, la query può essere aggiornata cliccando sul pulsante Aggiorna nella scheda Dati .
Conclusioni
Insieme a Power BI, Power Pivot e Power Map, Power Query offre un ulteriore strumento della Microsoft da usare da solo o insieme ai citati tool di business intelligence per facilitare la manipolazione dei dati negli ambienti lavorativi. Se ti è piaciuto Excel: Power Query e Business Intelligence, condividilo!
Per saperne di più su Microsoft Excel potrebbero interessarti i video corsi Excel o questi libri: