Come utilizzare Power Query in Excel?
Excel Power Query viene utilizzato per cercare origini dati, effettuare connessioni con origini dati e quindi modellare i dati in base ai nostri requisiti di analisi. Una volta che abbiamo finito di modellare i dati secondo le nostre esigenze, possiamo anche condividere i nostri risultati e creare vari rapporti utilizzando più query.
Passi
Fondamentalmente, ci sono 4 passaggi e l'ordine di questi 4 passaggi in Power Query è il seguente:
- Connetti: per prima cosa ci colleghiamo ai dati, che possono essere da qualche parte, nel cloud, in servizio o localmente.
- Trasforma: il secondo passaggio consiste nel modificare la forma dei dati secondo le esigenze dell'utente.
- Combina: in questa fase, eseguiamo alcuni passaggi di trasformazione e aggregazione e combiniamo i dati di entrambe le origini per produrre un rapporto combinato.
- Gestisci: unisce e aggiunge colonne in una query con colonne corrispondenti in altre query nella cartella di lavoro.
Esistono molte funzionalità super potenti di Excel Power Query.
Supponiamo di avere i dati sugli acquisti degli ultimi 15 anni in 180 file. Ora la gestione di un'organizzazione richiederebbe il consolidamento dei numeri prima di analizzarli. La direzione potrebbe adottare uno dei seguenti metodi:
- Avrebbero aperto tutti i file e li avrebbero copiati e incollati in un unico file.
- D'altra parte, possono usare una soluzione saggia, che è quella di applicare le formule, poiché è soggetta a errori.
Qualunque sia il metodo scelto, contiene molto lavoro manuale e, dopo pochi mesi, ci sarebbero nuovi dati di vendita per la durata aggiunta. Dovranno ripetere lo stesso esercizio.
Tuttavia, Power Query può aiutarli a non svolgere questo lavoro noioso e ripetitivo. Cerchiamo di capire questa query di potenza Excel con un esempio.
Esempio
Supponiamo di avere file di testo in una cartella con i dati di vendita e di voler ottenere quei dati nel nostro file excel.
Come possiamo vedere nell'immagine qui sotto, abbiamo due tipi di file nella cartella, ma vogliamo ottenere i dati solo dei file di testo nel file excel.
Per fare lo stesso, i passaggi sarebbero:
Passaggio 1: in primo luogo, dobbiamo ottenere i dati in Power Query in modo da poter apportare le modifiche richieste ai dati per importarli in un file excel.
Per fare lo stesso, sceglieremo l' opzione "Da cartella" dal menu "Da file" dopo aver fatto clic sul comando "Ottieni dati" dal gruppo "Ottieni e trasforma" nella scheda "Dati" .
Passaggio 2: selezionare la posizione della cartella sfogliando.
Fare clic su "OK"
Passaggio 3: si aprirà una finestra di dialogo contenente l'elenco di tutti i file nella cartella selezionata con le intestazioni delle colonne come "Contenuto", "Nome", "Estensione", "Data di accesso", "Data di modifica", "Data di creazione" "Attributi" e "Percorso cartella".
Sono disponibili 3 opzioni, ovvero Combina , Carica e Trasforma dati .
- Combina : questa opzione viene utilizzata per accedere a una schermata in cui possiamo scegliere quali dati combinare. Il passaggio di modifica viene saltato per questa opzione e non ci dà alcun controllo sui file da combinare. La funzione Combina consente di consolidare tutti i file nella cartella, il che può causare errori.
- Carica: questa opzione caricherà la tabella come mostrato sopra nell'immagine nel foglio di lavoro di Excel invece dei dati effettivi nei file.
- Trasforma dati: a differenza del comando "Combina" , se usiamo questo comando, possiamo scegliere quali file combinare, cioè possiamo combinare solo un tipo di file (stessa estensione).
Come nel nostro caso, vogliamo combinare solo file di testo (.txt); sceglieremo il comando "Trasforma dati" .
Possiamo vedere "Passaggi applicati" sul lato destro della finestra. Per ora, c'è solo un singolo passaggio che è quello di prendere i dettagli dei file dalla cartella.
Passaggio 4: c'è una colonna denominata "Estensione" in cui possiamo vedere che i valori nella colonna sono scritti in entrambi i casi, cioè, maiuscolo e minuscolo.
Tuttavia, dobbiamo convertire tutti i valori in lettere minuscole poiché il filtro differenzia tra entrambi. Per fare lo stesso, dobbiamo selezionare la colonna e quindi scegliere "Minuscolo" dal menu del comando "Formato" .
Passaggio 5: filtreremo i dati utilizzando la colonna "Estensione" per i file di testo.
Passaggio 6: dobbiamo combinare i dati per entrambi i file di testo ora utilizzando la prima colonna "Contenuto". Faremo clic sull'icona posta a destra del nome della colonna.
Passaggio 7: si aprirà una finestra di dialogo denominata "Combina file" in cui è necessario selezionare il delimitatore come "Tab" per i file di testo (file con estensione ".txt") e scegliere la base per il rilevamento del tipo di dati. E fai clic su "OK".
Dopo aver fatto clic su "OK" , otterremo i dati combinati dei file di testo nella finestra "Power Query" .
Possiamo cambiare il tipo di dati delle colonne come richiesto. Per la colonna "Entrate" , cambieremo il tipo di dati in "Valuta".
Possiamo vedere i passaggi applicati ai dati utilizzando una power query sul lato destro della finestra.
Dopo aver apportato tutte le modifiche richieste ai dati, possiamo caricare i dati in un foglio di lavoro Excel utilizzando il comando "Chiudi e carica in" nel gruppo "Chiudi" nella scheda "Home" .
Dobbiamo scegliere se caricare i dati come tabella o connessione. Quindi fare clic su "OK".
Ora possiamo vedere i dati come una tabella nel foglio di lavoro.
E 'il riquadro "Query cartella di lavoro" sul lato destro, che possiamo utilizzare per modificare, duplicare, unire, aggiungere query e per molti altri scopi.
Excel Power Query è molto utile in quanto possiamo vedere che 601.612 righe sono state caricate in pochi minuti.
Cose da ricordare
- Power Query non modifica i dati di origine originali. Invece di modificare i dati di origine originali, registra ogni passaggio eseguito dall'utente durante la connessione o la trasformazione dei dati e, una volta che l'utente ha completato la modellazione dei dati, prende il set di dati perfezionato e lo porta nella cartella di lavoro.
- Power Query fa distinzione tra maiuscole e minuscole.
- Durante il consolidamento dei file nella cartella specificata, dobbiamo assicurarci che utilizzando la colonna "Estensione" e dobbiamo escludere i file temporanei (con l'estensione ".tmp" e il nome di questi file inizia con il segno "~") come Power Query può importare anche questi file.
