Come utilizzare Power Query in Excel: la guida completa

Power Query è un potente strumento di business intelligence in Excel che consente di importare, pulire e trasformare i dati in base alle proprie esigenze. Se hai mai passato ore a ripulire i dati (abbiamo) o hai faticato a unire i dati da varie fonti (abbiamo anche), questa funzione ti semplificherà la vita.

Il modo migliore per imparare a utilizzare Power Query in Excel consiste nell'eseguire alcune attività comuni come l'importazione di dati e l'utilizzo dell'editor di Power Query per trasformare le tabelle risultanti e combinare i dati.

Questo articolo ti consente di iniziare mostrando chiari esempi di Power Query con guide dettagliate . Padroneggiando Power Query, puoi elevare il tuo lavoro al livello successivo.

Siete pronti? Facciamolo.

Sommario

• Come sapere se puoi accedere a Power Query
• Come importare dati con Power Query
• Come utilizzare l'editor di Power Query
  • Anteprima dei dati
  • Proprietà
• Come lavorare con più origini dati
• Come aggiungere dati con Power Query
• Come unire i dati con Power Query
• 5 modi per trasformare i dati con Power Query
  • 1. Espansione e condensazione dei dati
  • 2. Ordinamento dei dati
  • 3. Formattazione dei dati
  • 4. Aggiunta e rimozione di colonne
  • 5. Modifica dei tipi di dati
• Lavorare con le formule e il linguaggio M
  • Barra della formula
  • Scrittura e modifica del codice M
• Cos'è la sintassi del linguaggio M?
• Come aggiornare le connessioni dati
• Come integrare Power Query con il modello di dati
• Come automatizzare con Power Query e VBA
• Funzionalità avanzate di PowerQuery
• Collaborazione e Condivisione
• Concludiamo questo

Come sapere se puoi accedere a Power Query

Prima di iniziare a utilizzare Power Query, devi assicurarti di disporre di una versione di Excel con la funzionalità.

Ecco una breve panoramica di Power Query in base alla versione:

• Excel 2010 ed Excel 2013: disponibile come componente aggiuntivo che deve essere scaricato e installato.

• Excel 2016: integrato in Excel come “Get & Transform”.

• Excel 2019: prestazioni e funzionalità migliorate.

• Excel 2021 ed Excel per Microsoft 365: integrazione con origini dati cloud.

• Excel per Mac: disponibile solo da Microsoft 365 versione 16,69 e successive.

Per verificare che lo strumento sia integrato nella cartella di lavoro di Excel (2016 o successiva), vai alla scheda Dati e cerca la sezione "Ottieni e trasforma dati" in questa immagine:

Se hai Excel 2010 o 2013 su Windows, puoi scaricare , installare e abilitare il componente aggiuntivo Power Query.

Come importare dati con Power Query

Lo strumento Power Query consente di importare e connettersi a un'ampia gamma di origini dati:

• File, inclusi i formati CSV, XML e JSON

• URL Web o endpoint API

• Database, inclusi Access, SQL Server, Oracle e i database più moderni

• Servizi di Azure, file di SharePoint e feed OData

L'origine dati più semplice con cui iniziare è un semplice file CSV con diverse righe di dati. Crea un file di testo e aggiungi questi quattro nomi e cognomi, separati da una virgola:

• Joe, Blog

• Anna, Ryan

• Giacomo, Stewart

• Maria, Marrone

Assicurati di salvare il file con l'estensione .CSV. Il tuo file sarà simile a questo:

Per caricare i dati con Power Query, attenersi alla seguente procedura:

1. Vai alla scheda Dati.

2. Fai clic sul pulsante "Ottieni file" nella scheda Ottieni e trasforma dati.

3. Seleziona il file di esempio.

Potrebbe essere necessario attendere alcuni secondi affinché lo strumento inserisca i dati nel riquadro di anteprima. Power Query utilizza molte risorse della macchina anche per una piccola importazione, quindi sii paziente.

Suggerimento: chiudere le altre applicazioni per velocizzare le prestazioni.

Power Query visualizzerà la finestra "Importa testo/CSV" con un'anteprima dei dati.

Se i dati sembrano corretti, puoi procedere al caricamento o alla trasformazione dei dati:

• il pulsante Carica caricherà i dati direttamente in Excel.

• il pulsante Trasforma dati consente di modificare i dati prima di caricarli.

Hai notato che il file di esempio non aveva un'intestazione? Se viene caricato direttamente in Excel, le intestazioni saranno "Colonna 1" e "Colonna 2".

Invece, puoi inserire i dati nell'editor di Power Query e apportare alcune modifiche. Per fare ciò, fai clic sul pulsante "Trasforma dati".

Si apre ora l'editor di Power Query, che ci porta alla sezione successiva.

Come utilizzare l'editor di Power Query

In questa sezione verranno esaminati alcuni aspetti essenziali dell'uso dell'editor di Power Query.

Anteprima dei dati

Nell'area Anteprima dati è possibile visualizzare e interagire con i dati importati. È possibile filtrare le righe, modificare i tipi di dati delle colonne e rimodellare i dati in base alle proprie esigenze.

Al termine, Excel caricherà i dati trasformati in un foglio di lavoro.

Utilizzando il file di esempio, puoi modificare i nomi delle colonne direttamente in questo riquadro.

Fare così:

1. Fare clic con il pulsante destro del mouse sull'intestazione della colonna.

2. Fai clic su "Rinomina" nel menu a discesa.

3. Digitare i nomi preferiti nelle caselle di intestazione.

Man mano che apporti modifiche, la sezione "Passaggi applicati" a destra del riquadro di anteprima si aggiorna per riflettere ogni trasformazione, consentendoti di tenere traccia e modificare i passaggi di elaborazione dei dati secondo necessità.

Proprietà

L'impostazione delle proprietà per le tue query è essenziale per organizzare e tenere traccia del tuo lavoro.

Il riquadro Impostazioni query si trova a destra della finestra Power Query.

1. Fare clic sul collegamento "Tutte le proprietà".

2. Modifica il nome della query in modo che sia descrittivo.

3. Aggiungi una descrizione per fornire maggiori informazioni.

È facile dimenticare per cosa sono state impostate le query della scorsa settimana. In questo caso, il nome della query è stato modificato ed è stata aggiunta una descrizione che indica lo scopo della query.

Se vuoi saperne di più sulle opzioni disponibili, dai un'occhiata a questa panoramica dell'editor di Power Query .

Come lavorare con più origini dati

L'editor di Power Query offre due metodi principali per combinare i dati da più origini: l'aggiunta e l'unione.

L'aggiunta unisce le tabelle verticalmente, impilandole una sopra l'altra. L'unione consente di unire le tabelle in base a colonne comuni.

Per vedere come funziona, crea un secondo piccolo file CSV con la stessa struttura del primo. Questa volta, inserisci due nomi (nome e cognome) nel file:

• Joe, Blog

• Laura, Cane

Si noti che uno di questi nomi viene ripetuto dal file precedente.

Ora segui questi passaggi per inserire i dati nella tua cartella di lavoro di Microsoft Excel.

1. Chiudere la finestra dell'editor di Power Query e salvare le modifiche.

2. Aggiungi un nuovo foglio di lavoro.

3. Seguire i passaggi nella sezione precedente per importare il secondo file.

4. Fare clic sul pulsante Trasforma.

5. Rinominare le intestazioni di colonna come "Nome" e "Cognome".

Il riquadro di destra nel foglio di lavoro di Excel mostra le query e le connessioni correnti. Ora dovresti vedere due query nel riquadro.

Come aggiungere dati con Power Query

In questa sezione, aggiungerai il secondo file CSV di esempio al primo.

Segui questi passaggi per aggiungere i dati:

1. Fare clic con il pulsante destro del mouse sulla prima tabella (Nomi clienti) nel riquadro Query e connessioni.

2. Scegli "Modifica" per aprire la tabella nell'editor di Power Query.

3. Fare clic su Aggiungi query nella sezione Combina.

4. Scegli la seconda tabella dall'elenco a discesa delle tabelle da aggiungere.

Il risultato saranno due righe extra nella prima tabella.

Si noti che una riga viene ripetuta: è così che funziona l'operazione di accodamento. Aggiunge i dati da una tabella a un'altra, indipendentemente dal fatto che i dati esistano già.

È possibile rimuovere i dati duplicati nell'editor di Power Query con questi passaggi:

1. Fare clic sull'intestazione della colonna "Nome".

2. Tieni premuto il tasto "Ctrl" (o "Cmd" su Mac).

3. Fare clic su entrambe le intestazioni di colonna.

4. Fare clic su "Rimuovi righe" nella scheda Home della barra multifunzione dell'editor di Power Query.

5. Seleziona "Rimuovi duplicati" dal menu a discesa.

Come unire i dati con Power Query

Quando unisci i dati, devi scegliere le colonne corrispondenti tra le query.

Supponiamo di avere due file delimitati da virgole in cui uno contiene i numeri di telefono dei clienti e l'altro contiene gli indirizzi. I due file hanno questo aspetto:

Seguire le istruzioni nelle sezioni precedenti per caricare ogni file nella propria query di alimentazione. Quindi puoi esercitarti a unire le tabelle.

Nel caso dei nostri dati di esempio, entrambe le colonne (Nome e Cognome) devono essere selezionate come colonne corrispondenti. Tieni premuto il tasto Ctrl per selezionare più colonne.

Segui questi passi:

1. Fare clic con il pulsante destro del mouse sulla prima tabella nel riquadro Query e connessioni.

2. Scegli "Modifica" per aprire la tabella nell'editor di Power Query.

3. Seleziona "Unisci query" dal gruppo "Combina".

4. Scegli la seconda tabella nell'elenco a discesa delle tabelle.

5. Fare clic sulla prima colonna nella prima tabella.

6. Tieni premuto il tasto Ctrl e fai clic sulla seconda colonna nella prima tabella.

7. Tieni premuto il tasto Ctrl e fai clic su entrambe le colonne nella seconda tabella.

8. Mantieni l'operatore di join predefinito come "Left Outer".

9. Fare clic su "OK" per applicare le modifiche.

L'operatore di join "Left Outer" garantisce l'eliminazione dei dati duplicati.

Nell'editor di Power Query verrà visualizzata una nuova colonna denominata "Tabella" o "Tabella annidata" contenente i dati uniti della seconda query.

1. Fare clic sull'icona di espansione (due frecce) nell'intestazione di questa colonna.

2. Deseleziona la casella "usa la colonna originale come prefisso" (questo evita nomi di colonne lunghi).

3. Fare clic su "OK".

4. Fare clic su "Chiudi e carica".

La tabella unita aveva quattro colonne, con i dettagli del telefono e dell'indirizzo uniti. Potresti avere ancora un po' di ordine da fare.

Ad esempio, potresti voler modificare il nome della tabella (Merge1) e la colonna dell'indirizzo nell'immagine sottostante.

5 modi per trasformare i dati con Power Query

La trasformazione dei dati nel tuo file Excel è semplificata con Power Query, che ti consente di pulire, organizzare e manipolare i tuoi dati in modo efficace.

Alcuni dei modi più comuni per trasformare i dati sono:

• Espansione e condensazione dei dati

• Ordinamento dei dati

• Formattazione dei dati

• Aggiunta e rimozione di colonne

• Modifica dei tipi di dati

1. Espansione e condensazione dei dati

L'espansione e la condensazione dei dati è importante per rendere i set di dati più gestibili. Power Query consente di espandere o comprimere le tabelle per una visualizzazione più organizzata.

La sezione precedente mostra come espandere includendo i dati delle tabelle correlate.

Puoi anche condensarlo per includere solo dati riepilogati basati su un'aggregazione di tua scelta come somma, conteggio o percentuale.

2. Ordinamento dei dati

Per ordinare i dati, è possibile modificare rapidamente l'ordine per valori ascendenti o discendenti.

Fare clic sulla freccia giù accanto al nome di una colonna e scegliere "Ordinamento crescente" o "Ordinamento decrescente".

3. Formattazione dei dati

La scheda Trasforma nell'editor di Power Query offre molte opzioni.

Ad esempio, il menu a discesa Formato consente di modificare tutti i dati in una colonna o nell'intera tabella in lettere maiuscole o minuscole.

4. Aggiunta e rimozione di colonne

Power Query semplifica il processo di aggiunta, eliminazione e modifica delle colonne.

To remove a column, simply right-click on the header of the undesired column and select “Remove.” This will permanently delete the column and all of its data from the table.

To add a new column, select the existing column where you’d like the new one to appear. Then use the “Add Column From Examples” feature to create a custom column or to perform calculations between two or more columns.

5. Changing Data Types

Working with consistent and accurate data types is crucial for accurate analysis. Power Query allows you to change data types easily:

1. Right-click on the column header.

2. Choose “Change Type”.

3. Select the desired data type from the list provided.

Changing data types can be particularly useful when dealing with percentage data.

Ad esempio, potrebbe essere necessario convertire i punti decimali in percentuali o convertire le percentuali basate su testo in percentuali numeriche per semplificare il calcolo e il confronto.

Lavorare con le formule e il linguaggio M

Quando usi Power Query in Excel, incontrerai il linguaggio M, che è il cuore della creazione di formule avanzate, della manipolazione dei dati e della produzione di potenti query. In questa sezione imparerai a:

• la barra delle formule

• codice M

• gli elementi essenziali della sintassi del linguaggio M

Barra della formula

La barra della formula nell'editor di Power Query di Excel è l'area in cui inserirai le formule e il codice M.

Per usarlo, vai all'editor di Power Query selezionando una query dal riquadro Query e connessioni. La barra della formula si trova sopra il riquadro di anteprima dei dati.

Scrittura e modifica del codice M

Per creare query versatili e potenti in Excel, dovrai scrivere e modificare il linguaggio Power Query noto come codice M. Ecco alcuni suggerimenti su come iniziare:

• Utilizza il riquadro Passaggi applicati per scrivere codice passo dopo passo, semplificando il monitoraggio e la modifica della logica della query.

• Combina il codice M con una tabella Excel, tabelle pivot e origini dati esterne, come Azure e file di testo, per creare report dinamici e automatizzati.

• Regola le opzioni di query per perfezionare le tue preferenze di caricamento dei dati e migliorare le prestazioni.

Man mano che acquisisci familiarità con il codice M, scoprirai che puoi automatizzare e ottimizzare molti processi, rendendo l'analisi dei dati in Excel più efficiente e potente.

Per visualizzare il codice di una query, procedi nel seguente modo:

1. Aprire la query nell'editor di Power Query.

2. Fai clic su "Editor avanzato" nella scheda Home.

Questo è il codice alla base della nostra tabella unita di esempio (la tua potrebbe avere un aspetto diverso):

Cos'è la sintassi del linguaggio M?

Comprendere la sintassi del linguaggio M è fondamentale per creare formule di Power Query efficaci. Tieni a mente i seguenti punti:

• M La lingua fa distinzione tra maiuscole e minuscole, quindi fai attenzione alle maiuscole.

• Utilizzare le parentesi quadre [] per fare riferimento a colonne, record ed elenchi.

• Per fare riferimento a un passaggio precedente nella query, utilizzare il nome della variabile (ad esempio, #"Nome passaggio").

• Genera un passaggio della formula di query con l'istruzione "in".

Man mano che acquisisci esperienza con il flusso linguistico di Power Query , scoprirai nuovi modi per trasformare e analizzare i tuoi dati, rendendo le tue cartelle di lavoro di Excel una risorsa ancora più preziosa.

Guarda il video qui sotto, è ora di tirare fuori lo spazzolone e ripulire i tuoi dati disordinati.

Come aggiornare le connessioni dati

Una delle caratteristiche principali di Power Query è la possibilità di aggiornare facilmente la tua connessione Power Query.

Quando le origini dati sono state aggiornate e sono necessarie le informazioni più recenti nella cartella di lavoro, è possibile aggiornare le connessioni dati nel riquadro Query e connessioni.

Se non vedi il riquadro a destra del foglio di lavoro, puoi aprirlo andando alla tabella Dati e facendo clic su "Query e connessioni".

Quando il riquadro Query e connessioni è aperto, elenca tutte le query. Segui questi passi:

1. Fare clic con il pulsante destro del mouse sulla query che si desidera aggiornare.

2. Selezionare Aggiorna dal menu di scelta rapida.

Questo aggiornerà la tua cartella di lavoro con i dati più recenti dalle origini dati connesse.

Come integrare Power Query con il modello di dati

Il modello di dati di Excel è una potente funzionalità che consente di creare strumenti di business intelligence, come tabelle pivot, basati su relazioni di dati.

Il modello di dati è fondamentalmente un database nascosto che contiene i dati importati e le relazioni tra le tabelle.

Fa parte della funzionalità Power Pivot integrata nelle versioni correnti di Excel. Potrebbe essere necessario installare Power Pivot come componente aggiuntivo per accedervi nelle versioni precedenti).

Puoi integrare le connessioni dati di Power Query con il modello di dati con questi passaggi:

1. Carica i tuoi dati usando Power Query come descritto nelle sezioni precedenti.

2. Invece di caricarlo direttamente nella cartella di lavoro, scegli Carica in dal menu Carica.

3. Selezionare la casella di controllo Aggiungi questi dati al modello di dati nella finestra di dialogo Importa dati.

Come automatizzare con Power Query e VBA

Con l'integrazione VBA (Visual Basic for Applications), puoi creare connessioni dati personalizzate e automatizzare i passaggi di trasformazione dei dati in Power Query.

Puoi sfruttare le tue competenze VBA esistenti per creare modelli di dati avanzati incorporando dati da piattaforme come Microsoft Access, SharePoint e Analysis Services.

Sebbene Power Query non disponga del supporto nativo per VBA, è possibile usare il codice VBA per aprire, aggiornare e modificare indirettamente le query di Power Query.

Ciò consente di creare flussi di lavoro di dati potenti e automatizzati e di semplificare il processo di analisi dei dati.

Di seguito è riportato un codice di esempio che aggiorna una query di alimentazione nella cartella di lavoro corrente.

:

Dim wb As Workbook, pq As Object

Impostare wb = ThisWorkbook

Imposta pq = wb.Query("Nomi clienti")

 pq.Refresh

Ecco il codice all'interno di una macro VBA di Excel:

Funzionalità avanzate di PowerQuery

Power Query di Excel offre una miriade di funzionalità avanzate per semplificare e migliorare il processo di analisi dei dati. Imparando a utilizzare in modo efficace queste funzionalità, puoi portare le tue capacità di analisi dei dati a un livello completamente nuovo.

Gli strumenti di analisi dei dati integrati includono:

• Colonne condizionali per l'applicazione di una logica complessa alle trasformazioni dei dati.

• Funzioni di raggruppamento e aggregazione per riepilogare i dati in modo significativo.

• Operazioni Pivot e Unpivot per rimodellare i dati per un'analisi più semplice.

Troverai molte di queste funzionalità nella scheda Trasforma nell'editor di Power Query.

Inoltre, puoi connettere Power Query a strumenti di business intelligence come LuckyTemplates per opzioni di analisi e visualizzazione dei dati più avanzate .

Collaborazione e Condivisione

La condivisione delle connessioni dati, delle query e delle trasformazioni con i membri del team è essenziale per una collaborazione efficace.

È possibile condividere una cartella di lavoro con query salvandola in un'unità di rete, in SharePoint o in un altro percorso di archiviazione condiviso.

Puoi anche esportare il codice M dietro una singola query con questi passaggi:

1. Modificare la query nell'editor di Power Query.

2. Fai clic su "Editor avanzato" nella scheda Home.

3. Copia l'intero codice M nella finestra Editor avanzato.

4. Apri un editor di testo (ad es. Blocco note) e incolla il codice M copiato nell'editor di testo.

5. Salva il file di testo e invialo al tuo collega.

Concludiamo questo

Con una serie di funzionalità e la sua interfaccia intuitiva, Power Query è uno strumento essenziale per gli utenti di Excel che desiderano lavorare in modo efficace con dati esterni.

Questa preziosa funzionalità aiuta a semplificare il processo di creazione di report, pulizia dei dati ed esecuzione di analisi complesse sulle tabelle di dati. Usando

Power Query alla fine ti fa risparmiare tempo e aumenta l'efficienza quando lavori con i file Excel.