Power Query è integrato nelle versioni Windows di Office 365, Excel 2016, Excel 2019 ed è disponibile come download gratuito nelle versioni Windows di Excel 2010 ed Excel 2013. Lo strumento è progettato per estrarre, trasformare e caricare dati in Excel da un varietà di fonti. La parte migliore: Power Query ricorda i tuoi passaggi e li riprodurrà quando desideri aggiornare i dati. Ciò significa che puoi pulire i dati il giorno 1 nell'80% del tempo normale e puoi pulire i dati dai giorni 2 al 400 semplicemente facendo clic su Aggiorna.
Dico questo su molte nuove funzionalità di Excel, ma questa è davvero la migliore funzionalità per colpire Excel in 20 anni.
Racconto una storia nei miei seminari dal vivo su come Power Query è stato inventato come stampella per i clienti di SQL Server Analysis Services che sono stati costretti a utilizzare Excel per accedere a Power Pivot. Ma Power Query ha continuato a migliorare e ogni persona che usa Excel dovrebbe dedicare del tempo all'apprendimento di Power Query.
Ottieni Power Query
Potresti già avere Power Query. Si trova nel gruppo Ottieni e trasforma nella scheda Dati.
Ma se sei in Excel 2010 o Excel 2013, vai su Internet e cerca Download Power Query. I comandi di Power Query verranno visualizzati in una scheda Power Query dedicata nella barra multifunzione.
Pulisci i dati la prima volta in Power Query
Per darti un esempio di alcune delle meraviglie di Power Query, supponi di ottenere il file mostrato di seguito ogni giorno. La colonna A non è stata compilata. I quarti vengono visualizzati anziché in basso nella pagina.
Per iniziare, salva quella cartella di lavoro sul tuo disco rigido. Mettilo in un posto prevedibile con un nome che utilizzerai per quel file ogni giorno.
In Excel selezionare Ottieni dati, Da file, Da cartella di lavoro.
Vai alla cartella di lavoro. Nel riquadro Anteprima, fare clic su Foglio1. Invece di fare clic su Carica, fai clic su Modifica. Ora la cartella di lavoro viene visualizzata in una griglia leggermente diversa, la griglia di Power Query.
Ora è necessario correggere tutte le celle vuote nella colonna A.Se dovessi farlo nell'interfaccia utente di Excel, la sequenza di comandi ingombrante è Home, Trova e seleziona, Vai a speciale, Spazi vuoti, Uguale a, Freccia su, Ctrl + Invio .
In Power Query selezionare Trasforma, Riempi, Giù.
Tutti i valori nulli vengono sostituiti con il valore sopra. Con Power Query, bastano tre clic invece di sette.
Prossimo problema: i trimestri stanno attraversando invece che in calo. In Excel, puoi risolvere questo problema con una tabella pivot Intervallo di consolidamento multiplo. Ciò richiede 12 passaggi e 23+ clic.
In Power Query selezionare le due colonne che non sono trimestri. Apri il menu a discesa Annulla pivot colonne nella scheda Trasforma e scegli Annulla pivot altre colonne, come mostrato di seguito.
Fare clic con il pulsante destro del mouse sulla colonna Attributo appena creata e rinominarla Trimestre invece di Attributo. Più di venti clic in Excel diventano cinque clic in Power Query.
Ora, per essere onesti, non tutti i passaggi di pulizia sono più brevi in Power Query che in Excel. Rimuovere una colonna significa comunque fare clic con il pulsante destro del mouse su una colonna e scegliere Rimuovi colonna. Ma ad essere onesti, la storia qui non riguarda il risparmio di tempo del primo giorno.
Ma aspetta: Power Query ricorda tutti i tuoi passaggi
Guarda sul lato destro della finestra di Power Query. C'è un elenco chiamato Passaggi applicati. È un audit trail istantaneo di tutti i tuoi passaggi. Fare clic su qualsiasi icona a forma di ingranaggio per modificare le scelte in quel passaggio e fare in modo che le modifiche si estendano nei passaggi futuri. Fare clic su qualsiasi passaggio per visualizzare l'aspetto dei dati prima di tale passaggio.
Al termine della pulizia dei dati, fare clic su Chiudi e carica come mostrato di seguito.
Mancia
Se i tuoi dati sono più di 1.048.576 righe, puoi utilizzare il menu a discesa Chiudi e carica per caricare i dati direttamente nel modello di dati PowerPivot, che può contenere 995 milioni di righe se hai abbastanza memoria installata nel computer.
In pochi secondi, i dati trasformati vengono visualizzati in Excel. Eccezionale.
Il vantaggio: dati puliti domani con un clic
Ma ancora una volta, la storia di Power Query non riguarda il risparmio di tempo il giorno 1. Quando si selezionano i dati restituiti da Power Query, viene visualizzato un pannello Query e connessioni sul lato destro di Excel e su di esso è presente un pulsante Aggiorna. (Abbiamo bisogno di un pulsante Modifica qui, ma poiché non ce n'è uno, devi fare clic con il pulsante destro del mouse sulla query originale per visualizzare o apportare modifiche alla query originale).
È divertente pulire i dati il primo giorno. Mi piace fare qualcosa di nuovo. Ma quando il mio manager vede il rapporto risultante e dice "Bello. Puoi farlo ogni giorno? " Cresco rapidamente fino a odiare la noia di pulire lo stesso set di dati ogni giorno.
Quindi, per dimostrare il Giorno 400 di pulizia dei dati, ho completamente modificato il file originale. Nuovi prodotti, nuovi clienti, numeri più piccoli, più righe, come mostrato di seguito. Salvo questa nuova versione del file nello stesso percorso e con lo stesso nome del file originale.
Se apro la cartella di lavoro della query e faccio clic su Aggiorna, in pochi secondi Power Query riporta 92 righe invece di 68 righe.
La pulizia dei dati nei giorni 2, 3, 4, … 400, … Day Infinity ora richiede due clic.
Questo esempio solo graffia la superficie di Power Query. Se trascorri due ore con il libro, M is for (Data) Monkey di Ken Puls e Miguel Escobar, imparerai altre funzionalità, come queste:
- Combinando tutti i file Excel o CSV da una cartella in una singola griglia Excel
- Conversione di una cella con Apple; Banana; Cherry; Dill; Melanzane in cinque righe in Excel
- Eseguire un CERCA.VERT in una cartella di lavoro di ricerca mentre si importano dati in Power Query
- Fare una singola query in una funzione che può essere applicata a ogni riga in Excel
Per una descrizione completa di Power Query, controlla M Is for (Data) Monkey di Ken Puls e Miguel Escobar. Entro la fine del 2019 sarà disponibile la seconda edizione ribattezzata, Master Your Data.
Grazie a Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser e Colin Michael per aver nominato Power Query.
