Power Query + Power Pivot: "Best Thing to Happen to Excel in 20 Years"

Tutto ok. Quindi c'è una citazione di questo ragazzo di nome Bill Jelen. È conosciuto come Mr. Excel è un MVP di Excel e uno dei leader del settore e quello che ha detto è che il power query e il power pivot sono la cosa migliore che possa accadere per eccellere in 20 anni. E non potrei essere più d'accordo. Quindi parliamo del perché questo è il caso e rivedi alcuni dei vantaggi chiave di questi strumenti di business intelligence. Quindi, prima di tutto, ora possiamo importare e analizzare milioni di decine di milioni anche centinaia di milioni di righe di dati direttamente in Excel da qualsiasi luogo. Questo è solo un enorme cambiamento di gioco. In secondo luogo possiamo rapidamente costruire modelli per miscelare e analizzare i dati tra fonti. Quindi non abbiamo più bisogno di mischiarle insieme con le formule di una cella come cerchiamo o indicizziamo e abbiniamo. Possiamo facilmente creare relazioni per analizzare le prestazioni olistiche su un intero modello di dati. Terzo, ora possiamo creare procedure di formatura e caricamento dei dati completamente automatizzate senza VBA e senza macro. In questo modo può connettersi ai database per conservare tali connessioni e quindi osservare il flusso di dati attraverso l'intero modello e l'intero processo con il semplice clic di un pulsante. Infine, infine, possiamo ora definire le misure calcolate utilizzando le espressioni di analisi dei dati. È un linguaggio di formula altrimenti noto come daks, quindi non utilizzeremo più queste formule ridondanti a griglia in stile unico come quelle a cui sei abituato. Useremo le espressioni daks per interfacciarci con il nostro modello di dati. Sono più flessibili, sono più potenti e sono più portabili quando si lavora con il power pivot. Quindi voglio approfondire un po 'di più questi aspetti. Uno per uno e magari mostrarvi alcune anticipazioni e alcuni demo solo per renderlo un po 'più tangibile per voi. Quindi numero uno importante analizza milioni di righe. Dai un'occhiata a queste schermate. Questa è una schermata del modello di dati con una finestra di lavoro ma di interrogazione. E questo dimostra che avevamo appena caricato appena 25 milioni di file di dati. Quindi chiediti quando è stata l'ultima volta che hai caricato 25 milioni di righe di dati in Excel. E se non hai mai usato questi strumenti di query di potenza e pivot di potenza, probabilmente la risposta non è mai. Quindi questa è la bellezza di questo. Quando ti connetti ai dati con la query di potenza e li carichi nel modello di dati delle celle, i dati vengono effettivamente compressi e archiviati in memoria. Puoi ancora guardarlo. Puoi ancora accedervi. È ancora possibile trasformarlo e analizzarlo ma non viene memorizzato nei fogli di lavoro tradizionali. Ciò significa che non più limiti di milioni di righe sono ora completamente aperti a set di dati di grandi dimensioni. E solo per dimostrartelo. Andiamo avanti e apriamo una nuova cartella di lavoro vuota di Excel. E in questa cartella di lavoro entra nei miei strumenti di dati, prendi una nuova query da un file di testo. Non ti preoccupare, ti guiderò attraverso tutti questi passaggi durante il corso. Essenzialmente quello che sto facendo qui è che sto prendendo un file di testo chiamato acquisti da supermercato e quello che voglio fare è caricarlo direttamente sul modello di dati di Excel. E non appena ho colpito caricare ciò che noterete è che questo nuovo riquadro viene visualizzato sul lato destro dello schermo. Si chiama dolore alla cartella di lavoro e, come puoi vedere, mi mostra in tempo reale la quantità di dati che vengono caricati dal supermercato. Quindi questo è tempo reale. Questo non è accelerato in alcun modo. E come puoi vedere abbiamo già oltre quattro milioni di file di dati da questo file di testo. E il motivo per cui siamo in grado di caricare così tanti dati qui è che non stiamo provando a scaricarli in un foglio di calcolo o in una cartella di lavoro tradizionali come sei abituato. Sapete che se fossimo saremmo limitati da quel milione di capacità stradali e non saremmo in grado di mettere i dati lì. Invece, questi dati entreranno direttamente nel modello di dati di Excel e il modello di dati è un motore di compressione che ci consentirà di accedere a tali dati. Ma solo un po 'dietro le quinte. Quindi, come puoi vedere qui, sta avviando il suo processo e quindi non appena questo si carica sarà immediatamente accessibile a me attraverso il modello di dati. Quindi eccoci. Sembra che si sia appena concluso e ora, proprio come una piccola anteprima, farò clic sul pulsante del modello di dati gestito e sugli strumenti di dati qui e qui posso effettivamente vedere i dati appena caricati. E quando scorrono fino in fondo al boom, vai oltre 10 milioni di righe. Roba molto interessante Ora di nuovo il secondo vantaggio chiave costruisce modelli di dati per fondere le fonti. Quello che stiamo vedendo qui è un esempio del modello di dati in quella che viene chiamata vista diagramma e vista diagramma che consente di vedere o tabelle come oggetti singoli e quindi disegnare o creare relazioni tra loro. Quindi, invece di ricucire manualmente quelle tabelle insieme alle formule di cella, lavoreremo con le relazioni per fondere i dati in base ai campi comuni. Quindi torniamo su excel e ti mostrerò come si presenta. Quindi qui in Excel ho intenzione di gestire il mio modello di dati showman nella finestra del modello di dati e sono in visualizzazione dati per impostazione predefinita. Posso semplicemente fare clic su per visualizzare il diagramma e ciò che fa è che mi mostra esattamente come appaiono i miei tavoli come sono collegati e come formano questo modello più ampio. In realtà stiamo costruendo questo modello che stai guardando qui durante tutto il corso e so che sembra un po 'complicato un po' straniero, ma credimi con un po 'di pratica che imparerai a fare questo roba in nessun tempo. Terzo vantaggio chiave. Automatizza i tuoi dati. Quindi questo è uno screenshot dal power query editor di query. E con questo editor è possibile filtrare la forma e trasformare i dati grezzi prima che vengano caricati nel modello dati. E ciò che è fantastico di questo. È proprio come una macro. Registra ogni passaggio eseguito automaticamente e lo salva con la query e con la connessione della cartella di lavoro in modo che ogni volta che è necessario caricare questi dati, basta premere Aggiorna per scorrere tutti i passaggi applicati e il boom che hai automatizzato il processo e I ' Tornerò in Excel e mostrerò esattamente come appare in Excel, quindi qui nella mia scheda dati posso fare clic sul pulsante Mostra query e questo è dove vedo tutti i file che sono stati caricati. Facciamo semplicemente clic destro e modificiamo il calendario e qui è interessante vedere tutti questi passaggi applicati che sono stati presi. In realtà, abbiamo iniziato con una tabella a colonne molto semplice e poi, nel corso dei passaggi, abbiamo aggiunto campi modificati e modificato i tipi di dati fino a raggiungere la versione finale della tabella che volevamo caricare. Quindi, ancora una volta tutti questi passaggi possono essere modificati possono essere eliminati come meglio credi. Quindi questa è la finestra di editing Qwerty di Qwerty. Ora ultimo ma non meno importante numero di benefit chiave per la creazione di potenti misure con daks. Quindi stai guardando qui. Questa è la finestra di creazione delle misure e stiamo creando un nuovo campo utilizzando la funzione di intelligenza temporale che calcola automaticamente una media mobile di 10 giorni. Ora questi tipi di funzioni sono le ragioni per le quali il daks è così potente che non si può mai fare nulla di simile usando i campi calcolati della vecchia scuola con una normale tabella pivot. E se potessi, sarebbe un po 'hackerato insieme e troppo complicato. Quindi questo rende molto chiaro molto semplice e consente un sacco di potenti e potenti analisi che non potremmo fare in precedenza. Quindi torniamo indietro per eccellere ancora una volta. E qui stiamo guardando un vero e proprio fulcro del potere. Tutti questi campi calcolati qui sono misure. E puoi fare alcune cose davvero interessanti come se fossi in grado di rispondere alle selezioni di soglia che puoi filtrare e segmentare i tuoi dati come faresti con una normale tabella pivot. È possibile utilizzare strumenti di formattazione condizionale familiari come barre dati e scale di colori. È fondamentalmente simile a quel familiare tavolo pivot ma con una tonnellata di potenza di fuoco aggiuntiva dietro le quinte. Quindi eccoti. Principali vantaggi per cui è necessario utilizzare la ricerca energetica e il pivot di alimentazione ed è per questo che è la cosa migliore che possa eccellere in 20 anni.