Query‌ tutorial di excel power (ottieni e trasforma)

Se sei una di quelle persone che lavorano molto con i dati, puoi essere chiunque ( contabile , risorse umane, analista di dati, ecc.), PowerQuery può essere il tuo potente strumento.

Lasciami andare al sodo, Power Query è una delle competenze avanzate di Excel che devi apprendere e in questo tutorial esplorerai PowerQuery in dettaglio e imparerai come trasformare i dati con esso.

Iniziamo.

Che cos’è PowerQuery di Excel

Power Query è un componente aggiuntivo di Excel che è possibile utilizzare per ETL. Ciò significa che puoi estrarre dati da diverse fonti, trasformarli e quindi caricarli nel foglio di calcolo. Puoi dire che POWER QUERY è una macchina per la pulizia dei dati perché ha tutte le opzioni per trasformare i dati. È in tempo reale e registra ogni passo che fai.

Perché dovresti utilizzare Power Query (vantaggi)?

Se hai in mente questa domanda, ecco la mia risposta per te:

• Diverse origini dati : puoi caricare dati in un potente editor di query da diverse origini dati, come CSV , TXT, JSON, ecc.
• Trasforma facilmente i dati: normalmente utilizzi formule e tabelle pivot per le trasformazioni dei dati, ma con POWER QUERY puoi fare molto con pochi clic.
• È in tempo reale: scrivi una query una volta e puoi aggiornarla ogni volta che c’è una modifica nei dati e trasformerà i nuovi dati che hai aggiornato.

Vorrei condividere un esempio:

Immagina di avere 100 file Excel contenenti dati di 100 città e ora il tuo capo vuole che tu crei un report con tutti i dati di quei 100 file. OK, se decidi di aprire ciascun file manualmente e di copiare e incollare i dati da questi file e hai bisogno di almeno un’ora per farlo.

Ma con Power Query puoi farlo in pochi minuti. Ti senti eccitato? BENE.

Più avanti in questo tutorial imparerai come utilizzare Power Query con molti esempi, ma prima devi comprenderne il concetto.

Il concetto di domanda di energia

Per apprendere le query di potenza, è necessario comprenderne il concetto che funziona in 3 passaggi:

1. Ottieni dati

Power query ti consente di ottenere dati da diverse fonti come Web, CSV, file di testo, più cartelle di lavoro da una cartella e molte altre fonti in cui possiamo archiviare i dati.

2. Trasformare i dati

Dopo aver inserito i dati nella query di alimentazione, hai tutta una serie di opzioni che puoi utilizzare per trasformarli e pulirli. Crea query per tutti i passaggi eseguiti (in sequenza, un passaggio dopo l’altro).

3. Caricare i dati

Dall’editor di query avanzate è possibile caricare i dati trasformati nel foglio di lavoro oppure creare direttamente una tabella pivot o un grafico pivot o creare una connessione di soli dati.

Dov’è Power Query (come installare)?

Di seguito puoi vedere come installare Power Query Access in diverse versioni di Microsoft Excel.

Excel 2007

Se utilizzi Excel 2007, mi dispiace che PQ non sia disponibile per questa versione, quindi devi eseguire l’aggiornamento alla latest version di Excel (Excel per Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010).

Excel 2010 ed Excel 2013

Per il 2010 e il 2013 è necessario installare separatamente un componente aggiuntivo che puoi scaricare da questo collegamento e una volta installato otterrai una nuova scheda nella barra multifunzione di Excel come di seguito:

• Prima di tutto scarica il componente aggiuntivo da qui (sito ufficiale Microsoft).
• Una volta scaricato il file, apritelo e seguite le istruzioni.
• Successivamente, otterrai automaticamente la scheda “Power Query” sulla barra multifunzione di Excel.

Se per qualche motivo questa scheda “POWER QUERY” non viene visualizzata, non è necessario preoccuparsene. Puoi aggiungerlo utilizzando l’opzione Componenti aggiuntivi COM.

• Vai alla scheda File ➜ Opzioni ➜ Componenti aggiuntivi.

• Nelle opzioni “Componenti aggiuntivi”, seleziona “Componenti aggiuntivi COM” e fai clic su VAI.
• Successivamente, seleziona la casella “Microsoft Power Query per Excel”.

• Alla fine, fare clic su OK.

Excel 2016, 2019, Office 365

Se utilizzi Excel 2016, Excel 2019 o hai un abbonamento a OFFICE 365, è già presente nella scheda Dati , come gruppo denominato “OTTIENI E TRASFORMA” (mi piace quel nome, vero?).

Excel Mac

Se utilizzi Excel su Mac, temo che non sia disponibile alcun componente aggiuntivo per Power query e puoi solo aggiornare una query esistente , ma non puoi crearne una nuova o addirittura modificare una query ( LINK ).

Editor di PowerQuery

Power Query dispone di un proprio editor in cui è possibile ottenere i dati, eseguire tutti i passaggi per creare query e quindi caricarli nel foglio di calcolo. Per aprire l’editor di Power Query, è necessario accedere alla scheda Dati e in Ottieni e trasforma ➜ Ottieni dati ➜ Avvia l’editor di Power Query.

Di seguito una prima anteprima dell’editor che otterrai quando lo aprirai.

Ora esploriamo ciascuna sezione in dettaglio:

1. Nastro

Diamo un’occhiata a tutte le schede disponibili:

• File: dalla scheda File è possibile caricare i dati, rimuovere l’editor e aprire i parametri della query.
• Home : nella scheda HOME sono disponibili opzioni per gestire i dati caricati, mettere Mi piace, eliminare e spostare colonne e righe.
• Trasforma : questa scheda contiene tutte le opzioni necessarie per trasformare e pulire i dati, come unire colonne, trasporre, ecc.
• Aggiungi colonna : qui hai la possibilità di aggiungere nuove colonne ai dati che hai nell’editor avanzato.
• Visualizza : da questa scheda è possibile apportare modifiche alla vista dell’editor di query avanzate e ai dati caricati.

2. Passaggi applicati

Sul lato destro dell’editor è presente un riquadro delle impostazioni della query che include il nome della query e tutti i passaggi applicati in una sequenza.

Quando fai clic con il pulsante destro del mouse su un passaggio, hai un elenco di opzioni che puoi eseguire, come rinominare, eliminare, modificare, spostare su o giù, ecc. e quando fai clic su un passaggio, l’editor ti porterà alla trasformazione eseguita in quel passaggio.

Guarda sotto dove hai applicato tutti e cinque i passaggi in totale e quando faccio clic sul quarto passaggio mi porta alla trasformazione nel passaggio quattro in cui i nomi delle colonne non sono cambiati.

3. Domande

Il riquadro delle query sul lato sinistro elenca tutte le query attualmente presenti nella cartella di lavoro. Fondamentalmente è un luogo in cui puoi gestire tutte le query.

Quando fai clic con il pulsante destro del mouse sul nome di una query puoi vedere tutte le opzioni che puoi utilizzare (copia, elimina, duplica, ecc.)

Puoi anche creare una nuova query semplicemente facendo clic con il pulsante destro del mouse sullo spazio vuoto nel riquadro delle query e quindi selezionando l’opzione per l’origine dati.

4. Barra della formula

Come ho detto, ogni volta che applichi un passaggio nell’editor, viene generato il codice M per quel passaggio e puoi vedere questo codice nella barra della formula. Puoi semplicemente fare clic sulla barra della formula per modificare il codice.

Una volta imparato come utilizzare il codice M, puoi anche creare un passaggio scrivendo il codice e semplicemente facendo clic sul pulsante “FX” per inserire un passaggio personalizzato.

5. Panoramica dei dati

L’area di anteprima dei dati assomiglia a un foglio di calcolo Excel, ma è leggermente diversa da un normale foglio di calcolo in cui è possibile modificare direttamente una cella o dati. Quando carichi i dati nell’editor (lo faremo tra poco), vengono visualizzate tutte le colonne con le intestazioni con i nomi delle colonne e poi le righe con i dati.

Nella parte superiore di ogni colonna puoi vedere il tipo di dati dei dati della colonna. Quando carichi i dati nell’editor, Advanced Query applica automaticamente il tipo di dati corretto (quasi ogni volta) a ciascuna colonna.

È possibile fare clic sul pulsante in alto a sinistra dell’intestazione della colonna per modificare il tipo di dati applicato alla colonna. Contiene un elenco di tutti i tipi di dati da cui puoi attingere.

E sul lato sinistro dell’intestazione della colonna, hai il pulsante di filtro che puoi utilizzare per filtrare i valori della colonna. Nota: quando si filtrano i valori di una colonna, la query avanzata la considera come un singolo passaggio e la elenca in Passaggi applicati.

Se fai clic con il pulsante destro del mouse sull’intestazione della colonna puoi vedere che è presente un menu che include un elenco di opzioni che puoi utilizzare per trasformare i dati e utilizzare una delle opzioni e PQ che memorizza come passaggio nel processo applicato. non.

Origini dati per Power Query

La parte migliore di Power Query è che hai la possibilità di ottenere dati da più origini e trasformarli e quindi caricarli nel foglio di calcolo. Quando fai clic su Ottieni dati in OTTIENI E TRASFORMA, puoi visualizzare l’elenco completo delle origini dati che puoi caricare nell’editor.

Ora diamo un’occhiata ad alcune delle fonti di dati:

• Da tabella/intervallo : con questa opzione è possibile caricare i dati nell’editor di query avanzate direttamente dal foglio di lavoro attivo.
• Dalla cartella di lavoro : da un’altra cartella di lavoro presente sul computer. Devi solo individuare questo file utilizzando una finestra di dialogo aperta e otterrà automaticamente i dati da questo file.
• Da testo/CSV : ottieni i dati da un file di testo o da un file separato da virgole, quindi puoi caricarli nel foglio di calcolo.
• Dalla cartella : prende tutti i file dalla cartella e supporta i dati in un potente editor di query. (Vedi questo: Combina file Excel da una cartella).
• Dal Web : con questa opzione ottieni dati da un indirizzo web, immagina di avere un file archiviato sul web o di avere una pagina web da cui devi ottenere i dati.

Come caricare i dati nell’editor di Power Query

Ora impariamo come caricare i dati nel potente editor di query. Qui hai un elenco dei nomi degli studenti e dei loro punteggi ( LINK ).

Caricherai i dati direttamente dal foglio di calcolo, quindi devi prima aprire il file e poi seguire i passaggi seguenti:

• Innanzitutto, applica una tabella Excel ai dati (anche se non lo fai, Excel lo farà per te prima di caricare i dati nell’editor PQ).
• Ora seleziona una cella nella tabella e fai clic su “Da tabella/intervallo” (scheda Ottieni e trasforma dati).

• Dopo aver fatto clic sul pulsante, Excel conferma l’intervallo di dati per applicarvi una tabella Excel.

• A questo punto hai i dati nell’editor di Power Query e assomiglia a quello riportato di seguito.

• Qui puoi vedere:
  • Nella barra della formula, PQ ha generato il codice M per la tabella appena caricata nell’editor.
  • Sul lato sinistro dell’editor hai il riquadro delle query in cui hai l’elenco delle query.
  • Sul lato destro, nelle impostazioni della query, hai la sezione chiamata “Passaggi applicati” in cui sono elencati tutti i passaggi. Nota: devi pensare di non aver eseguito alcun “Tipo modificato”, ma lì c’è un passaggio chiamato “Tipo modificato”. Lascia che ti spieghi l’ INTELLIGENZA di POWER QUERY quando carichi i dati nell’editor, controlla e applica automaticamente i tipi di dati corretti per tutte le colonne.

Esempi di Power Query (suggerimenti e trucchi)

Puoi imparare come eseguire alcune delle attività di base che normalmente svolgi con le formule funzionali in Excel, ma con PowerQuery puoi farlo in pochi clic:

1. Sostituisci i valori

Hai un elenco di valori e desideri sostituire uno o più valori con qualcos’altro. Bene, con l’aiuto di Power Query , puoi creare una query e sostituire questi valori in pochissimo tempo.

Nell’elenco seguente, devi sostituire il mio nome “Puneet” con “Punit”.

• Innanzitutto, modifica l’elenco nell’editor di query avanzate.
• Successivamente, nell’editor di Power Query, vai alla “scheda Trasforma” e fai clic su “Sostituisci valori”.

• Ora in “Valore da trovare” inserisci “Puneet” e in “Sostituisci con” inserisci “Punit” e successivamente fai clic su OK.

• Dopo aver fatto clic su OK, tutti i valori vengono sostituiti con i nuovi valori e ora fai clic su “Chiudi e carica” per caricare i dati nel foglio di calcolo.

2. Ordinare i dati

Proprio come l’ordinamento normale, puoi ordinare i dati utilizzando PowerQuery e sto utilizzando lo stesso elenco di nomi che hai utilizzato nell’esempio precedente.

• Innanzitutto, carica i dati nel potente editor di query.
• Nella scheda Home sono presenti due pulsanti di ordinamento (Ascendente e Discendente).
• Fare clic su uno di questi pulsanti per ordinare.

3. Elimina colonne

Diciamo che hai dei dati da qualche parte e devi rimuovere alcune colonne da essi. Il punto è che devi eliminare queste colonne ogni volta che aggiungi nuovi dati, giusto? Ma la query di potenza può occuparsi di questo.

• Seleziona la colonna o più colonne che desideri eliminare.

• Ora fai clic con il pulsante destro del mouse e seleziona “Elimina”.

Suggerimento rapido: esiste anche un’opzione per “Rimuovere altre colonne” in cui è possibile eliminare tutte le colonne non selezionate.

4. Colonne divise

Proprio come l’opzione testo in colonna, hai “Colonna divisa” nella query di potenza. Lascia che ti spieghi come funziona.

• Seleziona la colonna e vai alla scheda Home ➜ Trasforma ➜ Dividi colonna ➜ Per delimitatore.

• Seleziona la personalizzazione dall’elenco a discesa e inserisci “-” al suo interno.
• Ora qui hai tre diverse opzioni per dividere una colonna.
  • Delimitatore più a sinistra
  • Delimitatore più a destra
  • Ogni occorrenza del delimitatore

Se hai un solo delimitatore in una cella, tutti e tre funzioneranno allo stesso modo, ma se hai più di un delimitatore, dovresti scegliere di conseguenza.

5. Rinominare una colonna

Puoi semplicemente rinominare una colonna facendo clic con il pulsante destro del mouse e quindi facendo clic su “Rinomina”.

Suggerimento rapido : supponiamo che tu abbia una query per rinominare una colonna e qualcun altro la rinomina per errore. Puoi ripristinare questo nome con un solo clic.

6. Colonna duplicata

In Power Query è disponibile una semplice opzione per creare una colonna duplicata. È sufficiente fare clic con il pulsante destro del mouse sulla colonna per la quale si desidera creare una colonna duplicata, quindi fare clic su “Colonna duplicata”.

7. Trasporre colonna o riga

Nella questione del potere, la trasposizione è un gioco da ragazzi. Sì, basta un clic.

• Dopo aver caricato i dati nell’editor di query avanzato, tutto ciò che devi fare è selezionare le colonne o le righe.
• Vai alla scheda Trasforma ➜ Tabella ➜ Trasponi.

8. Sostituisci/rimuovi gli errori

Normalmente, per sostituire o rimuovere errori in Excel, è possibile utilizzare l’opzione Trova e sostituisci o il codice VBA. Ma in PowerQuery è molto più semplice. Guarda la colonna qui sotto dove sono presenti errori e puoi rimuoverli e sostituirli.

Quando fai clic con il pulsante destro del mouse sulla colonna, avrai entrambe le opzioni.

• Sostituisci gli errori
• Rimuovere gli errori

9. Modificare il tipo di dati

Sono presenti dati in una colonna ma non sono nel formato corretto. Quindi ogni volta che è necessario cambiarne il formato.

• Innanzitutto, modifica i dati nel potente editor di query.
• Successivamente, seleziona la colonna e vai alla scheda Trasforma.
• Ora, da Tipo di dati, seleziona “Data” come tipo.

10. Aggiungi colonna dagli esempi

Nella query avanzata è disponibile un’opzione per aggiungere una colonna di esempio che in realtà non è un esempio correlato alla colonna corrente.

Lasciate che vi faccia un esempio:

Immagina di aver bisogno dei nomi dei giorni da una colonna di date. Invece di utilizzare una formula o qualsiasi altra opzione possibile, puoi utilizzare “Aggiungi colonna da esempi”.

Ecco come farlo:

• Fare clic con il tasto destro su una colonna e fare clic su “Aggiungi colonna da esempi”.

• Qui otterrai una colonna vuota. Fare clic sulla prima cella nella colonna per ottenere un elenco di valori che è possibile inserire.

• Selezionare “Nome del giorno della settimana dalla data” e fare clic su OK.

Boom! la tua nuova rubrica è qui.

11. Cambia scatole

Sono disponibili le seguenti opzioni per modificare le maiuscole e minuscole del testo in PowerQuery.

• Minuscolo
• Maiuscolo
• Scrivi in maiuscolo ogni parola

Puoi farlo facendo clic con il pulsante destro del mouse su una colonna e selezionando una delle tre opzioni sopra. Oppure vai alla scheda Trasforma ➜ Colonna testo ➜ Formato.

12. Tagliare e pulire

Per cancellare i dati o rimuovere spazi indesiderati , puoi utilizzare le opzioni TRIM e CLEAN in PowerQuery. I passaggi sono semplici:

• Fai clic con il pulsante destro del mouse su una colonna o seleziona tutte le colonne se hai più colonne.
• Vai alla scheda Trasforma ➜ Colonna testo ➜ Formato.
  1. TRIM: per rimuovere gli spazi bianchi dalla fine e dall’inizio di una cella.
  2. CLEAN: per rimuovere i caratteri non stampabili da una cella.

13. Aggiungi un prefisso/suffisso

Quindi hai un elenco di valori e da questo elenco vuoi aggiungere un prefisso/suffisso in ogni cella. In Excel puoi utilizzare il metodo di concatenazione, ma in PowerQuery esiste un’opzione semplice da utilizzare per entrambi.

• Innanzitutto, seleziona la colonna in cui devi aggiungere un prefisso/suffisso.
• Quindi vai alla scheda Trasforma ➜ Colonna testo ➜ Formato ➜ Aggiungi prefisso/Aggiungi suffisso.

• Dopo aver fatto clic su una qualsiasi delle opzioni, verrà visualizzata una finestra di dialogo per inserire il testo.

• E dopo aver inserito il testo, fai clic su OK.

14. Estrai valori

Se sei un fanatico delle formule, sono sicuro che sei d’accordo con me sul fatto che per estrarre testo o numero da una cella è necessario combinare diverse funzioni. Ma PowerQuery ha risolto molti di questi problemi. Hai sette modi per estrarre valori da una cella.

15. Solo la data o l’ora

Accade spesso che tu abbia la data e l’ora, entrambe in una cella, ma ne hai bisogno una.

• Seleziona la colonna in cui hai la data e l’ora combinate.
• Se vuoi:
  • Data : fare clic con il pulsante destro del mouse su ➜ Trasforma ➜ Solo data.
  • Ora : fare clic con il pulsante destro del mouse su ➜ Trasforma ➜ Solo ora.

16. Combina data e ora

Ora sai come separare data e ora. Ma dopo, devi sapere come combinarli.

• Innanzitutto, seleziona la colonna della data e fai clic sull’opzione “Solo data”.
• Successivamente, seleziona entrambe le colonne (Data e ora) e vai alla scheda Trasforma e dal gruppo “Colonna data e ora”, vai su Data e fai clic su “Combina data e ora”.

17. Numeri arrotondati

Ecco le seguenti opzioni a disposizione per arrotondare i numeri.

• Arrotondare per difetto: arrotondare un numero per difetto.
• Arrotonda: per arrotondare un numero.
• Arrotondamento: puoi scegliere quanto arrotondare le cifre decimali.

Ecco i passaggi:

• Selezionare la colonna e fare clic con il pulsante destro del mouse su ➜ Trasforma ➜ Arrotonda .
  1. Arrotondare per difetto: arrotondare un numero per difetto.
  2. Arrotonda: per arrotondare un numero.
  3. Arrotondamento: puoi scegliere quanto arrotondare le cifre decimali.

Nota: quando si seleziona l’opzione “#3 Round”, è necessario inserire il numero di cifre decimali da arrotondare.

18. Calcoli

Ci sono opzioni che puoi usare per eseguire calcoli (molti). Puoi trovare tutte queste opzioni nella scheda Trasforma (nel gruppo Colonna numerica).

• Di base
• Statistiche
• Scienziato
• Trigonometria
• Girare
• Informazione

Per eseguire uno qualsiasi di questi calcoli, è necessario selezionare la colonna e quindi l’opzione.

19. Raggruppa per

Supponiamo che tu abbia un set di dati di grandi dimensioni e desideri creare una tabella di riepilogo. Ecco cosa devi fare:

• Nella scheda Trasforma, fai clic sul pulsante “Raggruppa per” e verrà visualizzata una finestra di dialogo.

• Ora da questa finestra di dialogo seleziona la colonna con cui vuoi raggruppare e successivamente aggiungi un nome, seleziona l’operazione e la colonna in cui hai i valori.

• Alla fine, fare clic su OK.

Nota: ci sono anche opzioni avanzate nell’opzione “Raggruppa per” che puoi utilizzare per creare una tabella di gruppo a più livelli.

20. Rimuovere i valori negativi

In uno dei post del mio blog, ho elencato sette metodi per rimuovere i segni negativi e Power query è uno di questi. Fare clic con il tasto destro su una colonna e andare all’opzione Trasforma, quindi fare clic su “Valore assoluto”.

Ciò rimuove istantaneamente tutti i segni negativi dai valori.

Come caricare i dati nel foglio di calcolo

Una volta trasformati i dati, puoi caricarli nel foglio di calcolo e utilizzarli per ulteriori analisi. Nella scheda Home c’è un pulsante chiamato “Chiudi e carica”, quando fai clic su di esso viene visualizzato un elenco a discesa che offre opzioni aggiuntive:

• Chiudi e carica
• Chiudi e carica

• Dopo aver fatto clic sul pulsante, verranno visualizzate le seguenti opzioni:

• Seleziona come desideri visualizzare questi dati nel tuo foglio di calcolo.
  • Pittura
  • Rapporto tabella pivot:
  • Perno della tabella
  • Crea una sola connessione
  • Dove vuoi inserire i dati?
    • Foglio di lavoro esistente
    • Nuovo foglio di lavoro.
  • Aggiungi questi dati al modello dati.
• Basta selezionare l’opzione della tabella e il nuovo foglio di lavoro e lasciare deselezionato il modello dati e fare clic su OK.

• Nel momento in cui fai clic su OK, viene aggiunto un nuovo foglio di lavoro con i dati.

Altri esempi da imparare

Aggiorna automaticamente una query

Di tutti gli esempi che ho citato qui, questo è il più importante. Quando crei una query, puoi aggiornarla automaticamente (puoi impostare un timer).

Ed ecco i passaggi:

• Nella scheda Dati, fai clic su “Query e connessioni” e otterrai il riquadro Query e connessioni sul lato destro della finestra.

• Ora fai clic con il pulsante destro del mouse sulla query, seleziona “Aggiorna ogni” e inserisci i minuti.

Come utilizzare una formula e una funzione in Power Query

Proprio come puoi utilizzare funzioni e formule in un foglio di calcolo Excel, Advanced Query ha il proprio elenco di funzioni che puoi utilizzare. Le basi delle funzioni e delle formule in PowerQuery sono le stesse delle funzioni del foglio di calcolo di Excel.

In PQ è necessario aggiungere una nuova colonna personalizzata per aggiungere una funzione o una formula.

Facciamo un esempio: Nei dati sottostanti (già nell’editor PQ) avete il nome e il cognome ( LINK DOWNLOAD ) .

Immagina di dover unire i due nomi e creare una colonna per il nome completo. In questo caso puoi inserire una semplice formula per concatenare i nomi delle due colonne.

• Innanzitutto, vai alla scheda Aggiungi colonna e fai clic su “Colonna personalizzata”.

• Ora nella finestra di dialogo della colonna personalizzata, inserisci il nome della nuova colonna “Nome completo” o come vuoi nominare la nuova colonna.

• • La formula della colonna personalizzata è il punto in cui devi inserire la formula. Quindi inserisci la formula qui sotto :

 [First Name]&" "&[Last Name] 

• Quando inserisci una formula nella “Formula colonna personalizzata”, PQ controlla la formula inserita e visualizza un messaggio “Nessun errore di sintassi rilevato” e se è presente un errore visualizzerà un messaggio di errore in base al tipo di errore.
• Una volta inserita la formula e senza errori, basta premere OK.
• Ora hai una nuova colonna alla fine dei dati che contiene i valori di due colonne (nome e cognome).

Come utilizzare una funzione in Power Query

Allo stesso modo, puoi anche utilizzare una funzione mentre aggiungi una colonna personalizzata e Power Query ha un vasto elenco di funzioni che puoi utilizzare.

Capiamo come utilizzare una funzione con un esempio facile e semplice. Continuo l’esempio sopra in cui abbiamo aggiunto una nuova colonna che combina nome e cognome.

Ma ora devi convertire in maiuscolo il testo completo del nome che hai in questa colonna. La funzione che puoi utilizzare è “ Text.Upper ” . Come suggerisce il nome, converte il testo in testo maiuscolo.

• Innanzitutto, vai alla scheda Aggiungi colonna e fai clic sulla colonna personalizzata.

• • Ora, nella finestra di dialogo della colonna personalizzata, inserisci il nome della colonna e la formula seguente nella casella della formula della colonna personalizzata:

 Text.Upper([Full Name]) 

• E quando fai clic su OK, crea una nuova colonna con tutti i nomi in maiuscolo.
• La prossima cosa è eliminare la vecchia colonna e rinominare la nuova colonna. Quindi fai clic con il pulsante destro del mouse sulla prima colonna e seleziona Elimina.

• Alla fine, rinomina la nuova colonna in “Nome completo”.

Esistono un totale di 700 funzioni che puoi utilizzare in Power query mentre aggiungi una nuova colonna ed ecco l’ elenco completo fornito da Microsoft per queste funzioni, controllalo.

Come modificare una query in PQ

Se desideri apportare modifiche alla query già presente nella cartella di lavoro, puoi semplicemente modificarla e quindi apportare le modifiche. Nella scheda Dati è presente un pulsante denominato Query e connessioni.

Quando fai clic su questo pulsante, si apre un riquadro sul lato destro che elenca tutte le query presenti nella cartella di lavoro corrente.

Puoi fare clic con il pulsante destro del mouse sul nome della query e selezionare Modifica e la otterrai nel potente editor di query per modificarla.

Quando modifichi una query, puoi vedere che tutti i passaggi eseguiti in precedenza sono elencati nei “Passaggi applicati” che puoi anche modificare o eseguire nuovi passaggi.

E una volta completate le modifiche, puoi semplicemente fare clic sul pulsante “Chiudi e carica”.

Esportare e importare connessioni

Se disponi di una connessione utilizzata per una query e ora desideri condividerla con qualcun altro, puoi esportare tale connessione come file odc.

Sulla tabella delle query c’è un pulsante chiamato “Esporta connessione” e quando fai clic su di esso ti consente di salvare la connessione di quella query nel tuo sistema.

E se desideri importare una connessione condivisa da qualcun altro, puoi semplicemente andare alla scheda Dati e in Ottieni e trasforma fare clic su Connessioni esistenti .

Quindi fai clic sul pulsante “Sfoglia per altro” da cui puoi individuare il file di connessione che è stato condiviso con te e importarlo nella tua cartella di lavoro.

Linguaggio PowerQuery (codice M)

Come accennato in precedenza, per ogni passaggio eseguito in PowerQuery, viene generato un codice (nel backend) chiamato M Code . Nella scheda Home è presente un pulsante chiamato ” Editor avanzato” che puoi utilizzare per visualizzare il codice.

E quando fai clic sull’editor avanzato, ti verrà mostrato l’editor del codice e questo codice avrà il seguente aspetto:

M è un linguaggio con distinzione tra maiuscole e minuscole e come tutti gli altri linguaggi utilizza variabili ed espressioni. La struttura di base del codice è simile alla seguente, dove il codice inizia con l’espressione LET.

In questo codice abbiamo due variabili e i valori impostati su di esse. Alla fine, per ottenere il valore, è stata utilizzata l’espressione IN. Ora quando fai clic su OK, verrà restituito il valore assegnato alla variabile “NomeVariabile” nel risultato.

Dai un’occhiata a questa risorsa per ulteriori informazioni sul linguaggio di Power Query.

Alla fine

Non puoi permetterti di evitare POWER QUERY. Se la pensi così, molte delle cose che facciamo con le funzioni di Excel o i codici VBA possono essere automatizzate utilizzandolo, e sono sicuro che questo tutorial ti ispirerà a usarlo sempre di più.