Power Query: the 2021 Definitive Beginners’ Guide

Ciao, benvenuto nella guida per principianti a Power Query di UpSlide.
Vi siete mai chiesti che cosa Power Query è? Come funziona? Se è così, sei nel posto giusto!
Pauline, il nostro esperto di Power Query di Finance 3.1, ci ha dato tutti i suoi migliori consigli e consigli per scrivere questo articolo.
Qui potrete imparare le basi del software e anche arrivare a praticare un po’! Sei pronto?

Se non avete tempo di leggerlo ora, scaricare la versione PDF e godere in un secondo momento!

Che cos’è Power Query? Quando dovrei usarlo?

Power Query è un componente aggiuntivo di Excel che consente agli utenti di importare, pulire e consolidare i dati. È possibile utilizzare Power Query quando:

• l’Elaborazione di grandi quantità di dati (Excel è limitata a un milione di righe)
• Importazione di dati da fonti diverse (ad esempio Excel, CSV, SharePoint, SQL, e Salesforce)
• la Manipolazione, la pulizia e la combinazione di diverse tabelle, file o cartelle!

Power Query è uno strumento facile da usare e potente anche per i principianti, in quanto non è necessaria l’esperienza di codifica per iniziare. Inoltre, non cambia l’origine dati originale. Il risultato è come un’istantanea di dati rimodellati portati in Excel. Cosa c’e ‘di cosi’ bello? Bene, dal momento che i dati di origine non vengono importati direttamente, non rende il tuo file Excel più pesante (cioè non rallenta il tuo computer!).

Per riassumere, Power Query è uno strumento di analisi dei dati che rende i processi ETL (Extract-Transform-Load) più semplici ed efficaci.

Qual è la differenza tra Power Query e Power Pivot?

Power Pivot è spesso associato a Power Query perché è un componente aggiuntivo di Microsoft Office che facilita l’analisi dei dati. È interessante notare che, tuttavia, questi due strumenti possono funzionare in tandem l’uno con l’altro! Power Pivot è semplicemente il prossimo passo della trasformazione dei dati: consente agli utenti di creare modelli di dati da tabelle precedentemente pulite da Power Query.

Mentre è simile, Power Pivot è più utilizzato per l’analisi dei dati, i calcoli relativi al tempo e la generazione di collegamenti. Semplicemente detto:

• Power Query consente di consolidare e rielaborare automaticamente i file (ad esempio la standardizzazione dei processi di reporting o il consolidamento di più file con lo stesso formato).
• Power Pivot consente di analizzare database di diversi milioni di linee estraendo informazioni aggregate in base alle regole impostate dalla propria azienda.

Qual è la differenza tra Power Query e Power BI?

La tecnologia di query sottostante di Power Query è inclusa anche in Power BI.

L’interfaccia del software Power BI Desktop consente di beneficiare delle tre tecnologie (Power Query, Power Pivot e Power View). Con Power BI, puoi eseguire tutte le fasi della catena di analisi dei dati, dall’estrazione dei dati alla creazione di immagini su un dashboard interattivo che puoi condividere e analizzare.

> Leggi il nostro articolo su come esportare Power BI in Excel

Perché Power Query è un’ottima alternativa a Excel VBA?

Power Query può facilmente sostituire VBA (Visual Basic per le applicazioni) in quanto consente di:

• Elabora le tue tabelle semplicemente facendo clic sui pulsanti. Nessuna abilità di codifica necessaria!
• Visualizza le tue operazioni passo dopo passo senza eseguire una singola macro.
• Modifica facilmente l’ordine dei passaggi dei diversi passaggi attraverso il drag-and-drop! Inoltre, è possibile aggiungere ed eliminare i passaggi non necessari con un semplice clic.

(vedere Parte 2-Sezione3: Come iniziare con Power Query?)

Tuttavia Power Query non può sostituire VBA quando si tratta di interagire con l’interfaccia di Excel, automatizzare i processi, generare nuovi file, inviare e-mail, ecc.

Microsoft Power Query è gratuita? C’è una query di potenza per Mac?

SÌ. Power Query è un’estensione gratuita di Excel. Questo è un altro vantaggio principale del componente aggiuntivo.
(Leggi la prossima domanda per scoprire come puoi ottenerla sul tuo desktop Excel)

Power Query per Mac? SÌ, grande novità: Query editor è ora compatibile con Excel per Mac! Ma l’integrazione è piuttosto limitata al momento in quanto è possibile modificare solo il percorso del file e aggiornare le query esistenti.

Parte 2: Inizia con Power Query!

Come scaricare Power Query?

Power Query è disponibile per Excel dal 2010.

Ecco come aggiungere Power Query in base alla versione di Excel*:

• Buone notizie, non è necessario scaricare Power Query per Excel 2019, 2016 o in Office 365. Puoi trovarlo nella barra multifunzione “Dati” sotto il pulsante “Ottieni & Trasforma”.
• Per Excel 2013 e 2010, è necessario scaricare il componente aggiuntivo Power Query. Apparirà in una nuova scheda dedicata denominata “Power Query” (in Excel 2010 è necessario disporre di un Professional Plus con licenza Software Assurance).

*Ecco come controllare la versione di Excel:

Vai a File > Account > Informazioni su Excel. Quindi apparirà una finestra. Leggi i primi due numeri tra parentesi. Nell’esempio seguente, la versione di Excel è 2016.

Per saperne di più, ecco un altro articolo dettagliato su come installare e attivare Power Query in Excel.

Come posso attivare Power Query in Excel?

A seconda della versione di Excel (vedi domanda precedente: Dov’è Power Query in Excel?) devi solo fare clic sul pulsante ” Ottieni dati “nella sezione” Ottieni & Trasforma “nella scheda” Dati ” per Excel 2016 e oltre. Per le versioni di Excel precedenti al 2013 incluso, basta andare alla barra multifunzione Power Query.

Quando si accede all’interfaccia Power Query, non sarà più possibile interagire con Excel. Se hai bisogno di tornare a Excel, dovrai chiudere l’editor Power Query.

Come funziona Power Query?

In Excel, si ha accesso alle seguenti funzionalità solo quando si fa clic su “Query & Connessioni”:

Data ribbon: per cercare origini dati (ribbon “Power Query” per Excel 2010 e 2013).

Query & Riquadro Connessioni: per avere un rapido sguardo a tutte le connessioni (query) effettuate.

Una volta lanciato Power Query, si atterra sull’editor Power Query che è composto da 4 elementi distinti:

Barra multifunzione principale: consente di creare query da zero grazie ai suoi pulsanti (se si ha familiarità con la lingua M, è anche possibile accedere all’editor avanzato per modificarli manualmente).

Query: per tenere traccia di tutte le connessioni a origini dati e flussi.

Anteprima tabella dati: per visualizzare il risultato dei dati dopo un passaggio specifico.

Impostazioni query (passaggi applicati) : per navigare tra tutti i passaggi effettuati. Power Query registra automaticamente tutte le trasformazioni effettuate.

• Nella sezione 4, imparerai come Power Query funziona fondamentalmente trasformando una tabella di dati.
• Nella sezione 5, diventa ancora più interessante in quanto scoprirai come procedere con i dati da varie fonti e unire le query.

Come iniziare con Power Query?

Immagina la seguente situazione:

Sei il controllore finanziario di un’azienda tecnologica che vende computer e smartphone. Attualmente, stai operando in 3 paesi: Francia, Regno Unito e Cina. Ogni mese, produci un report consolidato per il tuo manager, con KPI come ricavi totali, ricavi per paese o ricavi per prodotto. Per farlo, ogni country manager – Dennis, Douglas e Karen – ti invia un file.

Problema: ogni file elenca le vendite nella denominazione del paese. È troppo lungo convertirli in euro e poi consolidarli nella relazione finale.

E se si potesse automatizzare il processo e calcolare il report in pochi minuti utilizzando solo Power Query?

Non sarebbe fantastico? Bene, con Power Query, puoi! Iniziamo preparando la tua prima tabella

In primo luogo, hai bisogno di dati. Scarica il nostro esempio di Power Query e salvalo sul tuo server locale.

Quindi, segui questi semplici passaggi:

• Decomprimere il file e salvarlo sul server locale.
• Ora apri un nuovo foglio di lavoro in Excel e vai alla scheda Dati > Ottieni dati > Dal file > Dalla cartella di lavoro, come mostrato di seguito.

(se hai Excel versione 2013 o 2010, vai semplicemente alla scheda Power Query)

• Vai al percorso in cui hai salvato i file decompressi che hai appena scaricato e seleziona il file Excel denominato “Tassi di cambio”.

• Fare clic su Trasforma dati.

Potresti anche aver notato che alcuni passaggi applicati sono già apparsi nel riquadro Impostazioni query. Questi sono fatti automaticamente.

Ora esercitiamoci un po’, iniziando pulendo la tabella dei dati:

• Rimuovi le prime 3 righe, vai a Rimuovi righe > Rimuovi le righe superiori e digita “3”. Vedrai immediatamente la trasformazione nell’anteprima della tabella dati e nei passaggi applicati (Impostazioni query).

• Eliminare le righe vuote: rimuovere le righe > Rimuovere le righe vuote (come mostrato di seguito).

• Promuovi la tua prima riga come intestazione: fai clic su “Usa prima riga come intestazioni”. Come avrete notato, Power Query ha aggiunto automaticamente un passaggio per modificare il tipo di alcuni dati (i valori nella prima colonna sono ora formattati come Date).

• Rimuovi colonne vuote: fai clic su “Scegli colonne” e deseleziona le colonne 7, 8, 9 e 10.

Stai andando bene! Altri tre passi e sarà tutto pronto!

• Unpivot le colonne: Seleziona la colonna “Tasso di cambio”, quindi nella scheda Trasforma, vai su Unpivot columns > Unpivot Other columns (puoi anche selezionare le altre cinque colonne e selezionare”Unpivot columns”).

• Rinomina le colonne: fai clic sulle celle dell’intestazione, inserisci ” Data “nella prima,” Valuta “nella seconda e” Vota ” nella terza.

• Applicare un filtro: proprio come in Excel, fare clic sul menu a discesa della colonna “Data” e selezionare “Dec-18”.

• Nella scheda Home, fare clic su Chiudi e Carica per caricare la tabella finale in un nuovo foglio.

Ben fatto! Hai modellato la tua prima tabella di dati.

Andiamo oltre con Power Query

(Ri)immagina la seguente situazione:

Grazie alle tue abilità di Power Query, ora sei un SENIOR financial controller dell’azienda tecnologica. Ora, devi consolidare i dati di dicembre 2018 che i country manager ti hanno inviato.

Problema: ogni file elenca le vendite nella denominazione del paese. La loro conversione in euro richiede molto tempo, ancor di più per poi consolidarli nella relazione finale.

E se si potesse automatizzare il processo e calcolare il report in pochi minuti utilizzando solo Power Query?

Ora vedremo come consolidare diverse origini dati e unirle con la tabella che abbiamo preparato nella sezione precedente.

In primo luogo, importiamo l’intera cartella compresi i tre diversi file di origine:

• Se sei ancora in Power Query vai alla nuova cartella source > File >.

(Se torna su Excel, vai a Ottenere dati > Dalla cartella)

• Selezionare la cartella “Sales_201812” precedentemente scaricato e salvato. Dopo di che fare clic su OK.

Dovrebbe apparire una nuova finestra.

Quindi, è necessario unire le query:

• Seleziona “Combina & Modifica” nel menu a discesa (nella parte inferiore della nuova finestra).

• Seleziona la tabella di esempio “Dec-18” (tieni presente che tutti i fogli devono essere denominati allo stesso modo).

• È possibile verificare nella prima colonna che tutte le fonti sono incluse (fare clic sul menu a discesa), quindi fare clic su OK.

Ora sei nell’editor Power Query.

• Vai alla scheda Trasforma > Sostituisci valori.

• Ora sostituire ” € ” con “EUR”.

• Vai alla scheda Home > Unisci query.

Viene visualizzata una nuova finestra:

1. Seleziona la colonna “Valuta”.
2. Selezionare il file “Tassi di cambio” nel menu a discesa.
3. Seleziona la colonna “Valuta”.
4. Fare clic su OK.

• Ora, è necessario visualizzare i dati del tasso di cambio appena recuperati:
  1. Selezionare la colonna “Tasso di cambio”.
  2. Fare clic sul pulsante Espandi.
  3. Controlla solo “Vota”.
  4. Deseleziona “Usa originale original”.
  5. Fare clic su OK.

Congratulazioni! Hai appena caricato un’altra origine dati, unito le query e recuperato il tasso di cambio corrispondente. Non più VLOOKUP o INDICE + PARTITA!

Per finire, creeremo una colonna “Prezzo unitario EUR” per convertire tutti i prezzi in euro:

• Vai alla scheda Aggiungi colonna > Colonna personalizzata.

• Viene visualizzata una nuova finestra. Scriviamo la formula del prezzo in euro:
  1. Chiamalo “Prezzo unitario EUR”.
  2. Fare doppio clic su “Prezzo unitario Valuta locale” (o selezionarlo e quindi fare clic su Inserisci).
  3. Digitare”/”.
  4. Fare doppio clic su “Vota”.
  5. Fare clic su OK.

• Una volta visualizzata la colonna, fare clic sul simbolo del tipo (appena prima del nome) e selezionare “Numero decimale”.

Dobbiamo fare lo stesso per generare la colonna” Entrate”:

• Vai alla scheda Aggiungi colonna > Colonna personalizzata.
• Viene visualizzata una nuova finestra. Digitiamo la formula delle entrate:
  1. Chiamalo “Entrate”.
  2. Fare doppio clic su “Prezzo unitario EUR” (o selezionarlo e quindi fare clic su Inserisci).
  3. Digitare “*”.
  4. Fare doppio clic su “Quantità”.
  5. Fare clic su OK.

• Di nuovo, cambia il tipo di colonna in “Numero decimale”.

Per finire, esportiamo in una nuova tabella di dati in Excel!

• Vai alla scheda Home > Chiudi & Carica > Chiudi & Carica.

Ora puoi beneficiare appieno delle funzionalità di Excel per analizzare i dati, creare una tabella pivot, ecc.

Wow! Se ce l’hai fatta fino alla fine, congratulazioni! Ora sai come funziona Power Query.

Ora è il tuo turno!

Voglio girarlo a te: hai capito tutte le domande di cui sopra su Power Query?
Hai scaricato i nostri file tutorial e hai seguito le linee guida di cui sopra per provarlo da solo?
Fateci sapere lasciando un breve commento qui sotto in questo momento!

Per saperne di più su Power Query: